DavidSCowan
Board Regular
- Joined
- Jun 7, 2009
- Messages
- 78
I am a VBA novice and I would greatly appreciate some help please as this is driving me mad<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o> </o>
I have a macro (below FIRSTSUB) which works okay. What it does is identify if a cell in Col 1 contains specific strings (e.g. “ Aerosol”, “ Non_Aerosol “ etc.). If the cell does contain the string the macro uses the Left function to pull out the words that occur before the given strings, in this case the brand names of deodorants.<o></o>
<o> </o>
What I want to do is to have a more convenient macro (SECONDSUB) that cycles through a list of strings in Col 2 so that instead of having to type in the words Aerosol, Non_Aerosol etc. I want the macro to cycle through a list of strings.
<o> </o>
The problem is that SECONDSUB doesn’t work. The macro jumps to the last proper line (Else: Cells(i, 5).Value = "Error")<o></o>
<o> </o>
I have checked and the first line (If Application.WorksheetFunction.CountIf(Cells(i, 1), "*" & Cells(j, 2) & "*") = 1) works<o></o>
(I have to thank Fowmy for that he helped me on this type of line yesterday) <o></o>
<o> </o>
But the second line doesn’t work and the macro jumps to Else:<o></o>
<o> </o>
Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search("" & Cells(j, 2) & "", Cells(i, 1)) - 1))<o></o>
<o> </o>
Can someone please tell me what I am doing wrong.<o></o>
<o> </o>
<o> </o>
Sub FIRSTSUB()<o></o>
<o> </o>
finalrow = Cells(Rows.Count, 1).End(xlUp).Row<o></o>
<o> </o>
For i = 10 To finalrow<o></o>
If Application.WorksheetFunction.CountIf(Cells(i, 1), "* Aerosol*") = 1 Then<o></o>
Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Aerosol", Cells(i, 1)) - 1))<o></o>
ElseIf Application.WorksheetFunction.CountIf(Cells(i, 1), "* Non_Aerosol*") = 1 Then<o></o>
Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Non_Aerosol", Cells(i, 1)) - 1))<o></o>
ElseIf Application.WorksheetFunction.CountIf(Cells(i, 1), "* Nonaeros*") = 1 Then<o></o>
Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Nonaeros", Cells(i, 1)) - 1))<o></o>
Else: Cells(i, 5).Value = "Error"<o></o>
End If<o></o>
Next i<o></o>
End Sub<o></o>
<o> </o>
Sub SECONDSUB()<o></o>
<o> </o>
finalrow = Cells(Rows.Count, 1).End(xlUp).Row<o></o>
Lastrow = Cells(Rows.Count, 2).End(xlUp).Row<o></o>
<o> </o>
For i = 10 To finalrow<o></o>
For j = 1 To Lastrow<o></o>
<o></o>
If Application.WorksheetFunction.CountIf(Cells(i, 1), "*" & Cells(j, 2) & "*") = 1 Then<o></o>
<o></o>
Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search("" & Cells(j, 2) & "", Cells(i, 1)) - 1))<o></o>
<o></o>
Else: Cells(i, 5).Value = "Error"<o></o>
<o></o>
End If<o></o>
Next j<o></o>
Next i<o></o>
End Sub<o></o>
fficeffice" /><o></o><o
> </o>I have a macro (below FIRSTSUB) which works okay. What it does is identify if a cell in Col 1 contains specific strings (e.g. “ Aerosol”, “ Non_Aerosol “ etc.). If the cell does contain the string the macro uses the Left function to pull out the words that occur before the given strings, in this case the brand names of deodorants.<o
></o><o
> </o>What I want to do is to have a more convenient macro (SECONDSUB) that cycles through a list of strings in Col 2 so that instead of having to type in the words Aerosol, Non_Aerosol etc. I want the macro to cycle through a list of strings.
<o
> </o>The problem is that SECONDSUB doesn’t work. The macro jumps to the last proper line (Else: Cells(i, 5).Value = "Error")<o
></o><o
> </o>I have checked and the first line (If Application.WorksheetFunction.CountIf(Cells(i, 1), "*" & Cells(j, 2) & "*") = 1) works<o
></o>(I have to thank Fowmy for that he helped me on this type of line yesterday) <o
></o><o
> </o>But the second line doesn’t work and the macro jumps to Else:<o
></o><o
> </o>Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search("" & Cells(j, 2) & "", Cells(i, 1)) - 1))<o
></o><o
> </o>Can someone please tell me what I am doing wrong.<o
></o><o
> </o><o
> </o>Sub FIRSTSUB()<o
></o><o
> </o>finalrow = Cells(Rows.Count, 1).End(xlUp).Row<o
></o><o
> </o>For i = 10 To finalrow<o
></o>If Application.WorksheetFunction.CountIf(Cells(i, 1), "* Aerosol*") = 1 Then<o
></o>Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Aerosol", Cells(i, 1)) - 1))<o
></o>ElseIf Application.WorksheetFunction.CountIf(Cells(i, 1), "* Non_Aerosol*") = 1 Then<o
></o>Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Non_Aerosol", Cells(i, 1)) - 1))<o
></o>ElseIf Application.WorksheetFunction.CountIf(Cells(i, 1), "* Nonaeros*") = 1 Then<o
></o>Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search(" Nonaeros", Cells(i, 1)) - 1))<o
></o>Else: Cells(i, 5).Value = "Error"<o
></o>End If<o
></o>Next i<o
></o>End Sub<o
></o><o
> </o>Sub SECONDSUB()<o
></o><o
> </o>finalrow = Cells(Rows.Count, 1).End(xlUp).Row<o
></o>Lastrow = Cells(Rows.Count, 2).End(xlUp).Row<o
></o><o
> </o>For i = 10 To finalrow<o
></o>For j = 1 To Lastrow<o
></o><o
></o>If Application.WorksheetFunction.CountIf(Cells(i, 1), "*" & Cells(j, 2) & "*") = 1 Then<o
></o><o
></o>Cells(i, 5).Value = Left(Cells(i, 1), (Application.WorksheetFunction.Search("" & Cells(j, 2) & "", Cells(i, 1)) - 1))<o
></o><o
></o>Else: Cells(i, 5).Value = "Error"<o
></o><o
></o>End If<o
></o>Next j<o
></o>Next i<o
></o>End Sub<o
></o>
