Refer to different sheet and defined variable in VBA vlookup

user_12489476 0
Viewed 43

This is part of the vba code (modified recorded Macro) I currently have:

Dim strSheet1 As String, strSheet2 As String, lngRow as Long

Sheets(strSheet1).Select

lngRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("J2").Select
    
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],'SFDB'!R2C15:R[47]C15,1,FALSE)),""No"","""")"

I'm trying to:

  1. change the 'SFDB'! to something like strSheet2!, but running this, excel would ask me to select another excel file (strSheet1 and strSheet2 are both from InputBox)
  2. change R[47] to R[lngRow], but this would lead to an error "Method FormulaR1C1 of Object Range Failed"
vba excel
2 Answers

You need to take the parameter strSheet1 and lngRow outside the " section.

Change:

"=IF(ISNA(VLOOKUP(RC[-1],'SFDB'!R2C15:R[47]C15,1,FALSE)),""No"","""")"

With:

"=IF(ISNA(VLOOKUP(RC[-1],'" & strSheet1 & " '!R2C15:R[" & lngRow & "]C15,1,FALSE)),""No"","""")"

Also, avoid using Select and ActiveCell, instead use fully qualified Range and Worksheet objects.

Look at slightly modified code below:

Dim strSheet1 As String, strSheet2 As String, lngRow As Long

With Sheets(strSheet1)
    lngRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    .Range("J2").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],'" & strSheet1 & " '!R2C15:R[" & lngRow & "]C15,1,FALSE)),""No"","""")"
End With
user_63443630

try :

rows.count = rows + 1
dim wint = rows
lngRow = Cells(Rows.Count, wint).End(xlUp).Row
user_153245280
Related