VBcode for adding a LOOKUP formula

[stct80]

Hello all,

I am relatively new to the VB code for Excel macro and wanted to get your advise on the following macro..

'=== CODE START ===
Sub c_SLCodeThisWk()
Dim t As Long
For t = 2 To 100
Range("AR" & t).Formula = "=LOOKUP(2^15,SEARCH({""Overdue"",""Red"",""PastY"",""Yellow"",""Green"",""Complete"",""No_BL""},RC44),{6,3,5,2,1,0,4})"
Next t
End Sub
'=== CODE COMPLETE ===

With this code, I am trying to populate a formula in the 2 nd row of Column AU, with the pseudo code logic:

• Check the value in Column AR of the same row
• Populate a number (from 0 / 1 / 2 / 3 /4 /5 / 6), based on the string in Column AR of the same row.
• Move to the next row to refer the same logic till there is no more row left.

Problem is, I am getting an #NA value in place of the correct number in the destination cell for all the rows.
I think, there is some small change needed in the formula. Any advise?

 

[Jonmo1]

Try

Sub c_SLCodeThisWk()
Range("AU2:AU100").Formula = "=LOOKUP(2^15,SEARCH({""Overdue"",""Red"",""PastY"",""Yellow"",""Green"",""Complete"",""No_BL""},AR2),{6,3,5,2,1,0,4})"
End Sub

 

[JackDanIce]

I think it's a problem with RC44, try changing that line to:

Range("AR" & t).FormulaR1C1 = "=LOOKUP(2^15,SEARCH({""Overdue"",""Red"",""PastY"",""Yellow"",""Green"",""Complete"",""No_BL""},RC44),{6,3,5,2,1,0,4})"

 

[stct80]

Hi Jonmo1 & JackDanIce,

Thank you for quick responses. I think, both the solutions worked perfectly fine. I need one more advise, if you can.

Is there anyway to restrict the population to a row, when the sheet is complete say after certain number of rows. If we can leverage on value in one of the column (Say A). Once the Column A is <BLANK>for a particular row, macro can stop execution to this row and beyond..

 

[Jonmo1]

Try

Sub c_SLCodeThisWk()
Dim lr As Long
lr = Cells(Rows.Count,"A").End(xlup).Row
Range("AU2:AU" & lr).Formula = "=LOOKUP(2^15,SEARCH({""Overdue"",""Red"",""PastY"",""Yellow"",""Green"",""Complete"",""No_BL""},AR2),{6,3,5,2,1,0,4})"
End Sub

 

[stct80]

Hi Jonmo1,

Greetings and Thank you for the reply. This worked perfectly fine..

Just one small issue on number format. Unfortunately, the number populates with a '0' prefix. For example "05", "04" etc. Ths issue is, if I use this column for a pivot report, pivot table does not recognize the value as a number format - and can not display the Conditional formating due to number format mismatch.

When I punch in the same number in the cell manually and autofill all the row in this column, pivot conditional formatting works fine.

Any advise on tweaking the Lookup Search formula?

Thank you and stay cool at Bryan in summer heat!

Best,

 

[stct80]

Actually, I just figured out a small error in my formula.. This worked fine thro the VB Script.

Please ignore my last note.

Thank you!

Hi Jonmo1,

Greetings and Thank you for the reply. This worked perfectly fine..

Just one small issue on number format. Unfortunately, the number populates with a '0' prefix. For example "05", "04" etc. Ths issue is, if I use this column for a pivot report, pivot table does not recognize the value as a number format - and can not display the Conditional formating due to number format mismatch.

When I punch in the same number in the cell manually and autofill all the row in this column, pivot conditional formatting works fine.

Any advise on tweaking the Lookup Search formula?

Thank you and stay cool at Bryan in summer heat!

Best,