Vlookup in Macro - unable to select range - issue with setting R[]C[]

[SuemyMontanheiro]

Hi guys,

I’ve been insanely looking for this on the web, but couldn’t find anything that would make me get why this is not working. I’m new to VBA and my knowledge is very limited, for most things I use the “record macro” option to get the code and just paste it where I need it to be on my macro – but for this one it just isn’t working and I can´t seem to fix it or understand what is going wrong.


I need the macro to type this 3 formulas below, on the left cells. My issue is that when it goes on the code (on those RC language for the rows and columns), it turns into what’s in the right cell. Could you please help me understand what is going on and how to get the macro to write the right code? If it helps, the sheet “SQ01” has around 10k-15k lines and I’m looking from line 2 and down. So if it’s not possible to search on range B:G, using $S$2:$G$30000 would be fine.

Thank you in advance for your help.

[TABLE="class: x_MsoNormalTable, width: 1757"]
<tbody>[TR]
[TD="width: 536"]What I need
[/TD]
[TD="width: 687"]How it looks in the macro
[/TD]
[TD="width: 535"]What I get
[/TD]
[/TR]
[TR]
[TD="width: 536"]=VLOOKUP(C2,'SQ01'!B:G,6,0)
[/TD]
[TD="width: 687"]=VLOOKUP(RC[-9],'SQ01'!R2C1:R30000C7,6,0)
[/TD]
[TD="width: 535"]=VLOOKUP(C3,'SQ01'!$C$2:$C$30000,6,0)
[/TD]
[/TR]
[TR]
[TD="width: 536"]=IF(L2>42736,TEXT(L2,"[$-409]mmm"),"Created before 2017")
[/TD]
[TD="width: 687"]=IF(RC[-1]>42736,TEXT(RC[-1],""[$-409]mmm""),""Created before 2017"")
[/TD]
[TD="width: 535"]=IF(L3>42736,TEXT(L3,"[$-409]mmm"),"Created before 2017")
[/TD]
[/TR]
[TR]
[TD="width: 536"]=IF(A2="Inactive",TEXT(VLOOKUP(C2,'SQ01'!B:G,6,0),"[$-409]mmm"),"MISC Active")
[/TD]
[TD="width: 687"]=IF(RC[-13]=""Inactive"",TEXT(VLOOKUP(RC[-11],'SQ01'!C[-12]:C[-7],6,0),""[$-409]mmm""),""MISC Active"")
[/TD]
[TD="width: 535"]=IF(A3="Inactive",TEXT(VLOOKUP(C3,'SQ01'!C:C,6,0),"[$-409]mmm"),"MISC Active")
[/TD]
[/TR]
</tbody>[/TABLE]


---

 

[L. Howard]

Try something like this to enter formulas in a range of cells.

Enters the formula(s) in cell K2 and as far down the K column as there are used/occupied cells in column B of sheet SQ01.
The VLOOKUP formula has the lookup value ($C$2) set to an ABSOLUTE cell reference so all returns will be identical.

You will notice the " are now "" when they are part of the formula. Does not apply to the start and end "=the-formula" quotes.

The other formulas are commented out with an ' as as is the ".Value = .Value" line.
Un comment a formula line to see its use in this test situation.

The .Value = .Value is used if you want ONLY the result of the formula to be pasted in the destination cell, there will be NO formula in the destinations cells.
The data would not update if values change on the sheet, you would re-run the macro to get the updated formula results.

Copy to a standard module. I understand you may have some trouble blending this stuff into your recorded macro, if so, then post back and include the code you have recorded and describe the problem/s you are having.

Howard

Option Explicit


Sub SueMy_Formulas()


  Dim lRowCount&
  lRowCount = Sheets("SQ01").Cells(Rows.Count, "B").End(xlUp).Row
 
  With Range("K2").Resize(lRowCount)


    .Formula = "=VLOOKUP($C$2,'SQ01'!B:G,6,0)"
    
    '.Formula = "=IF(L2>42736,TEXT(L2,""[$-409]mmm""),""Created before 2017"")"
    
    '.Formula = "=IF(A2=""Inactive"",TEXT(VLOOKUP(C2,'SQ01'!B:G,6,0),""[$-409]mmm""),""MISC Active"")"
    
    '.Value = .Value
  End With
  
End Sub

Here is a single cell example. 


Sub Single_Cell()


Range("P2").Formula = "=VLOOKUP(C2,'SQ01'!B:G,6,0)"
Range("P3").Formula = "=IF(L2>42736,TEXT(L2,""[$-409]mmm""),""Created before 2017"")"
Range("P4").Formula = "=IF(A2=""Inactive"",TEXT(VLOOKUP(C2,'SQ01'!B:G,6,0),""[$-409]mmm""),""MISC Active"")"


End Sub