fireguy1125
asked on
VBA Macro Not Completing in Excel 2007
I have the following macro that is supposed to generate specific data in column K if a cell in Column A contains a specific value. This does not complete successfully, and only seems to get to the point of creating the heading Custom Attribute 3, and saving the file as "Active-Transform.xls". The rest does not seem to occur. I did combine 2 separate macros, so perhaps the formatting or the way I did it is incorrect. I'm a novice. Below is the code. Thanks you in advance!
Private Sub Workbook_Open()
If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
'nothing to do as already done
ActiveWorkbook.Save
Else
'fill extra columns
FillColumnKLM
'now save and close
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "Active-transform.xls"
Application.DisplayAlerts = True
End If
Application.Quit
End Sub
Sub FillColumnKLM()
Dim WS As Worksheet, I As Long, RwCnt As Long
Set WS = ActiveSheet
RwCnt = WS.Cells(Rows.Count, 1).End(xlUp).Row
WS.Cells(1, 11) = "Custom Attribute 3"
WS.Cells(1, 11).Font.Bold = True
For I = 2 To RwCnt
On Error Resume Next
If InStr(1, LCase(WS.Cells(I, 1)), "Fruit") Then
WS.Cells(I, 11) = "Apple"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Vegetable") Then
WS.Cells(I, 11) = "Carrot"
Else
End If
Next
Columns.AutoFit
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your code was also missing the End Sub at the end of the second subroutine but that may just be a typo.
ASKER
Thanks, pony10us, actually that is a typo.
ASKER
Also, it still needs to be kept as an "if contains", not "if is", as that cell contains other data I don't want included as part of the source info.
Wow, that is not going to have an answer. There is no way to predict what will be there.
Hmmm... I guess you could create an array of all of the unique values in the column and then youwould have the exact data, but I do not know of a simple way to do what you want without a lot of trial and error.
Hmmm... I guess you could create an array of all of the unique values in the column and then youwould have the exact data, but I do not know of a simple way to do what you want without a lot of trial and error.
No problem. I figured if you had it working at all then it must be a typo. :)
If you write Option Compare Text at the top of your module, all the VBA comparisons with in that module will use Text comparison instead of Binary comparison. Thus, “yes” will be equal to “YES”.
If you write Option Compare Text at the top of your module, all the VBA comparisons with in that module will use Text comparison instead of Binary comparison. Thus, “yes” will be equal to “YES”.
ASKER
I don't have a module, can I do it at the VBAProject level?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thanks so much!
ASKER