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]

I would probably recommend nesting them inside another SELECT CASE statement or an IF THEN statement if there are only two options for column L.

 

[kapperson]

I would probably recommend nesting them inside another SELECT CASE statement or an IF THEN statement if there are only two options for column L.

Thanks, I'll see what I can come up with!

 

[kapperson]

I would probably recommend nesting them inside another SELECT CASE statement or an IF THEN statement if there are only two options for column L.

Hi Joe4,
I was hoping you could help me with this. I tried to nest another SELECT CASE statement but I'm getting an error so I must not have done it correctly.

Below is the code I have now. Could you point me in the right direction?

Sub Test()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myEntry As String
    Dim myStart As Long
    Dim myStop As Long
    Dim myWordCheck As String
    Dim myEntry2 As String
    Dim myStart2 As Long
    Dim myStop2 As Long
    Dim myWordCheck2 As String
    Dim myLastRow2 As Long
    Dim myRow2 As Long
        
'   Find last row in column A (before first blank in column A)
    myLastRow = Range("A1").End(xlDown).Row

'   Find last row in column L (before first blank in column L)
    myLastRow2 = Range("L1").End(x1Down).Row
 
    Application.ScreenUpdating = True
    
'   Loop through all cells in column A
    For myRow = 1 To myLastRow
'       Extract work from path
        myEntry = Range("A" & myRow)
        myStart = InStrRev(myEntry, "\") + 1
        myStop = InStrRev(myEntry, ".") - 1
        myWordCheck = Mid(myEntry, myStart, myStop - myStart + 1)
        
'Loop through all cells in column L
    For myRow2 = 1 To myLastRow2
        myEntry2 = Range("L" & myRow)
        myStart2 = InStrRev(myEntry2, "/") + 1
        myStop2 = InStrRev(myEntry2, ".") - 1
        myWordCheck2 = Mid(myEntry2, myStart2, myStop2 - myStart2 + 1)

'       Set scenarios to search for
        Select Case myWordCheck
            Case "ORANGE"
                Select Case myWordCheck2
                    Case "1"
                    Cells(myRow, "Q") = "Fruit"
                    Cells(myRow, "R") = "Eat"
                    Cells(myRow, "S") = "Drink"
                    End Select
                
'           What  to return if value in column A not found
            Case Else
                Cells(myRow, "Q") = "unknown"
                Cells(myRow, "R") = "unknown"
                Cells(myRow, "S") = "uknown"
                
        End Select
    Next myRow
    
    Application.ScreenUpdating = True
    
   ' Application.Run "Post_Merge"
    
End Sub

Thank you so much for your help!

 

[Joe4]

What is your exact error message?
Which line of code does it highlight when you click "Debug"?

 

[kapperson]

What is your exact error message?
Which line of code does it highlight when you click "Debug"?

The error says

Compile Error: Invalid Next Control Variable Reference

It highlights "Next myRow", line 56.

Thanks,
Kelsey

 

[Joe4]

You have two FOR NEXT loops nested inside of each other, and you are missing your "Next myRow2" statement.

 

[kapperson]

You have two FOR NEXT loops nested inside of each other, and you are missing your "Next myRow2" statement.

Am I not able to next two FOR NEXT loops inside each other?
(Sorry, I'm a novice!)

Thanks,
Kelsey

 

[Joe4]

You can, but every FOR statement needs a matching NEXT statement. You have two FOR statements, but only one NEXT statement.
The structure should be something like

For myRow
    ...
    For myRow2
        ....
    Next myRow2
    ...
Next myRow

You are missing the Next myRow2 statement.

 

[kapperson]

Okay, I see what you're saying.
I'll fix that.

Thanks,
Kelsey

 

[kapperson]

I'm sorry, I'm still having trouble.
Is the use of nested FOR NEXT loops the best way to do this, or is there a better approach I'm just not knowledgeable enough to think of?

I know you mentioned nested SELECT CASE statements, which is what I was trying to do when I created the nested FOR NEXT loops. Is there a way to do that without creating the additional loop?

Thanks,
Kelsey