Loops/Arrays/ Help!

[Jenna_B]

I am failing at trying to create a code. What I want my code to do is to:

I have 35 conditions that needs to go in to 7 different categories...
so if the cell.Column1 is like "Veol" then cell.Column3 should output to say Veolia
if cell.column1 is like "Clean" then cell.Column3 will say "Clean"

I need to do the above 35 times and I have tried everything and nothing seems to work. I'll find a partial answer and the loop is missing so I try and add a loop and the vba crashes, or just does not function. I have THOUSANDS of lines to go through.

 

[Joe4]

Welcome to the Board!

Form the little information you have provided here, it sounds like a VLOOKUP formula may work. You would just set up a lookup table of your different options/conversions, and then use the formula.
See: https://www.techonthenet.com/excel/formulas/vlookup.php

If you don't think that will do what you want, please provide more information.

 

[Jenna_B]

Vlookup would be fine except... I have a ton of things I am looking for.
I likely didn't explain myself completely.

Here is my long way around it my issue...

Sub Enviro()[INDENT]Dim i As Long
For i = 2 To 999999

If Cells(i, 3).Value Like "*ATLA*" Then Cells(i, 11).Value = "Enviro Systems"
Next i[/INDENT]

End Sub

Sub Enviro1()[INDENT]Dim i As Long
For i = 2 To 999999

If Cells(i, 3).Value Like "*ENVIR*" Then Cells(i, 11).Value = "Enviro Systems"
Next i[/INDENT]

End Sub


Sub Enviro2()[INDENT]Dim i As Long
For i = 2 To 999999

If Cells(i, 3).Value Like "*CROSB*" Then Cells(i, 11).Value = "Enviro Systems"
Next i[/INDENT]

End Sub


Sub Enviro3()[INDENT]Dim i As Long
For i = 2 To 999999

If Cells(i, 3).Value Like "*DYNAM*" Then Cells(i, 11).Value = "Enviro Systems"
Next i[/INDENT]

End Sub

What I want to be able to do is put the above in to ONE code, as I need to do this for many different variables, I have tried using OR statements that just have not been working for me.

Sub Chnge[]
for i = 2 to 999999

if cells(i, 3).value like "*DYNAM*" OR "*CROSB*" OR "*ENVIR*" then cells(i,11).value = "Enviro Systems"

 

[Norie]

You should create 2 identically sized arrays, one for the terms you are looking for, eg ATLA, ENVIR, CROSB etc, and another for the corresponding return values.

Something like this.

Dim arrTerms()
Dim arrReturn()
Dim I As Long
Dim J As Long

    arrTerms = Array("ATLA", "ENVIR", "CROSB")
    arrReturns = Array("Enviro Systems", "Enviro Systems", "Enviro Systems")

    For I = 2 To 9999
        For J = LBound(arrTerms) To UBound(arrTerms)
            If Cells(i, 3).Value Like "*" & arrTerms(J)  & "*" Then 
                Cells(i, 11).Value = arrReturn(J)
                Exit For
            End If
        Next J
    Next I

Note, instead of hard-coding the values for the arrays you could store them in a table on a sheet and read them in at the start of the code.

 

[Jenna_B]

Norie,

That was brilliant!! EXACTLY what I needed it to do. Thank you!

I have little knowledge of VBA, so this was lost on me for sure.

 

[Joe4]

Glad you were able to get a solution (and Norie's solution is pretty slick).

Just another piece of information to pass along for future use. When using OR, each clause needs to be a complete statement, i.e.

if (cells(i, 3).value like "*DYNAM*") OR [FONT=Verdana](cells(i, 3).value like [/FONT][FONT=Verdana]"*CROSB*") OR "[/FONT][FONT=Verdana](cells(i, 3).value like [/FONT][FONT=Verdana]*ENVIR*") then cells(i,11).value = "Enviro Systems"[/FONT]