Macro that references another column and adds text based on what it says

[kapperson]

Hello,
I'm just getting started with trying to write macros for Excel. I have minimal JavaScript knowledge, so I understand parts of it but I'm not totally sure of the correct language. I'm trying to create a macro that will reference another cell and add a value in a different cell based on what that cell says.

For example, the logic would be something similar to this:

If cell A:1 = orange then cell P:1 = fruit.

The complex part is that I need it to run through several checks before continuing onto the next cell in the column, so it would have to go through the following:

If cell A:1 = orange then cell P:1 = fruit
If cell A:1 = desk then cell P:1 = furniture
If cell A:1 = Texas then cell P:1 = state
If cell A:1 = red then cell P:1 = color
If cell A:1 = hot then cell P:1 = temperature
If cell A:1 = shirt then cell P:1 = clothing
If cell A:1 = ring then cell P:1 = jewelry
If cell A:1 = male then cell P:1 = gender
If cell A:1 = Thursday then cell P:1 = day
If cell A:1 = Christmas then cell P:1 = holiday
If cell A:1 = dog then cell P:1 = mammal
If cell A:1 = sad then cell P:1 = emotion

The macro would have to run through all of those checks for each cell clear down until there is a blank cell in column A. Once it reaches that point, it would need to go back to the top of column A and start doing the same thing to populate the next column (Q) with values:

If cell A:1 = orange then cell Q:1 = eat
If cell A:1 = desk then cell Q:1 = write
If cell A:1 = Texas then cell Q:1 = live
If cell A:1 = red then cell Q:1 = see
If cell A:1 = hot then cell Q:1 = feel
If cell A:1 = shirt then cell Q:1 = wear
If cell A:1 = ring then cell Q:1 = wear
If cell A:1 = male then cell Q:1 = am
If cell A:1 = Thursday then cell Q:1 = is
If cell A:1 = Christmas then cell Q:1 = celebrate
If cell A:1 = dog then cell Q:1 = play
If cell A:1 = sad then cell Q:1 = cry

I need it to do this for P - AG (18 columns) and anywhere from 5,000 to 15,000 rows.
Again, I'm not sure how to take what I want to do and translate it into the code.

If you could show me a way to do this with the first two columns as I've detailed or point me in the direction of something or someone that would help me figure this out, I'd really appreciate it.

 

[Joe4]

Can you explain in English exactly what you are hoping to accomplish now with this iteration of code?
You wouldn't normally bother using a SELECT CASE statement if you are just checking for a single value (usually when you are checking for a bunch of different options, like in your original).

 

[kapperson]

Can you explain in English exactly what you are hoping to accomplish now with this iteration of code?
You wouldn't normally bother using a SELECT CASE statement if you are just checking for a single value (usually when you are checking for a bunch of different options, like in your original).

Okay, I want it to work basically like the original macro does, where it reads the information from column A and adds information based on that. The only addition is that I want it to read column A AND column L and add information dependent upon both of those values.

So, for example, column A could equal orange and column L would equal 1, then it would add "fruit" in column Q.
Secondly, column A could equal orange and column L would equal 2, then it would add "color" in column Q.
The numbers in column L will be more than 2, so the previous solution you offered with 2 conditions wouldn't work.

Does that make sense? Is there anything I can answer to help clarify?
I'm sorry this is becoming such a hassle!!

Thanks,
Kelsey

 

[Joe4]

Is "Orange" the only value you are looking for in column A?

 

[kapperson]

Is "Orange" the only value you are looking for in column A?

No, there will be multiple values in column A as well.

Thanks,
Kelsey

 

[Joe4]

OK. You don't need to loop throw the rows again. Just loop through the rows once, checking columns A and L on each pass. We can do that by nesting another SELECT CASE statement in each option of the original SELECT CASE statement.

So the first one would look like this, and the rest would follow the same pattern:

Sub MyMacro()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myEntry As String
    Dim myStart As Long
    Dim myStop As Long
    Dim myWordCheck As String
        
'   Find last row in column A (before first blank in column A)
    myLastRow = Range("A1").End(xlDown).Row
 
    Application.ScreenUpdating = True
    
'   Loop through all rows
    For myRow = 1 To myLastRow
'       Extract work from path (column A)
        myEntry = Range("A" & myRow)
        myStart = InStrRev(myEntry, "\") + 1
        myStop = InStrRev(myEntry, ".") - 1
        myWordCheck = Mid(myEntry, myStart, myStop - myStart + 1)

'       Set scenarios to search for
        Select Case myWordCheck
            Case "orange"
'               Check values in column L
                Select Case Cells(myRow, "L")
                    Case 1
                        Cells(myRow, "Q") = "fruit"
                    Case 2
                        Cells(myRow, "Q") = "color"
                End Select
''                ...
'           What  to return if value in column A not found
            Case Else
                Cells(myRow, "P") = "unknown"
                Cells(myRow, "Q") = "unknown"
''                ...
        End Select
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[kapperson]

OK. You don't need to loop throw the rows again. Just loop through the rows once, checking columns A and L on each pass. We can do that by nesting another SELECT CASE statement in each option of the original SELECT CASE statement.

So the first one would look like this, and the rest would follow the same pattern:

Sub MyMacro()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myEntry As String
    Dim myStart As Long
    Dim myStop As Long
    Dim myWordCheck As String
        
'   Find last row in column A (before first blank in column A)
    myLastRow = Range("A1").End(xlDown).Row
 
    Application.ScreenUpdating = True
    
'   Loop through all rows
    For myRow = 1 To myLastRow
'       Extract work from path (column A)
        myEntry = Range("A" & myRow)
        myStart = InStrRev(myEntry, "\") + 1
        myStop = InStrRev(myEntry, ".") - 1
        myWordCheck = Mid(myEntry, myStart, myStop - myStart + 1)

'       Set scenarios to search for
        Select Case myWordCheck
            Case "orange"
'               Check values in column L
                Select Case Cells(myRow, "L")
                    Case 1
                        Cells(myRow, "Q") = "fruit"
                    Case 2
                        Cells(myRow, "Q") = "color"
                End Select
''                ...
'           What  to return if value in column A not found
            Case Else
                Cells(myRow, "P") = "unknown"
                Cells(myRow, "Q") = "unknown"
''                ...
        End Select
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

Thank you so much! This is what I was trying to do but I didn't have the correct syntax for it. I really appreciate your help!!

Thanks again,
Kelsey