Vlookup with variable sheet Name as a variant

[MostafaNabil]

I'm working on a code and everything is working fine but I need to set vlookup value from a variable sheet Name with the same structure

I tried many syntax with no use any advise.

This a portion of the code which is related to my issue.

Dim SheetName As Variant
SheetName= DestinationWs.Name

Dim StringVal As String
StringVal = CStr(SheetName)

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'"" & StringVal & ""'!"" & " R[" & OldRemainingLastRowFirst & "]C[12] : R[" & OldRemainingLastRowFirst & "]C[14]),2,0)"

 

[RoryA]

It should be:

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R[" & OldRemainingLastRowFirst & "]C[12]:R[" & OldRemainingLastRowFirst & "]C[14]"",0),2,0)"

 

[MostafaNabil]

Unfortunately, I got Run-Time error '1004': Application-denfined or object-defined error message

 

[MostafaNabil]

The Whole Portion of function like below which may guide you to help me

'Selecting Previous Month Sheet (From Feb to Dec)

srIndex = Application.Match(CLng(InputValue) - 1, IndexRange, 0)

sMonth = MonthResultRg.Cells(srIndex)

For Each DestinationWs In wb.Worksheets

If InStr(1, DestinationWs.Name, sMonth, vbTextCompare) > 0 Then Exit For

Next DestinationWs

Dim SheetName As Variant: SheetName = DestinationWs.Name

Dim StringVal As String: StringVal = CStr(SheetName)

Dim OldRemainingHoursLastRowFirst As Variant: OldRemainingHoursLastRowFirst = DestinationWs.Range("W3").End(xlDown).Row

'Selecting the Desired Sheet Again which Selected First To Paste The Selection

srIndex = Application.Match(CLng(InputValue), IndexRange, 0)

sMonth = MonthResultRg.Cells(srIndex)

For Each DestinationWs In wb.Worksheets

If InStr(1, DestinationWs.Name, sMonth, vbTextCompare) > 0 Then Exit For

Next DestinationWs


DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R[" & OldRemainingHoursLastRowFirst & "]C[12]:R[" & OldRemainingHoursLastRowFirst & "]C[14]"",0),2,0)"

Simply this part of the whole code takes the input value and selecting the Month reference based on this value.


Then if the input value is 2 which refers to Feb Month , then it will vlookup the needed data from Sheet Name (Jan)

Then paste the vlookup data into Feb Month which is totally dynamic

 

[RoryA]

Ah, I missed that you were missing the last parts of the IFERROR formula. I guess you wanted:

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R[" & OldRemainingLastRowFirst & "]C[12]:R[" & OldRemainingLastRowFirst & "]C[14]"",0),2,0),0)"

though you are only looking at one row.

 

[MostafaNabil]

Ah, I missed that you were missing the last parts of the IFERROR formula. I guess you wanted:

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R[" & OldRemainingLastRowFirst & "]C[12]:R[" & OldRemainingLastRowFirst & "]C[14]"",0),2,0),0)"

though you are only looking at one row.

The command is ran now but I got only 0 values however the range should be correct
I made a slight modification for the command after your help to lookup from X4 to Z(the last row)

As attached photo, so where could the problem be

 

[RoryA]

The row numbers in your formula are relative to the formula cell. I suspect you should not be using the square brackets.

 

[MostafaNabil]

The row numbers in your formula are relative to the formula cell. I suspect you should not be using the square brackets.

You could explain more please as I did not get it or how to solve it .. First my problem where about using indirect inside vlookup to retrieve the variable sheet name which is solved after your help .. now the vlookup is not working and always gives 0's

 

[RoryA]

I meant that I think you want:

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R" & OldRemainingLastRowFirst & "C12:R" & OldRemainingLastRowFirst & "C14"",0),2,0),0)"

 

[MostafaNabil]

I meant that I think you want:

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R" & OldRemainingLastRowFirst & "C12:R" & OldRemainingLastRowFirst & "C14"",0),2,0),0)"

Yes exactly, and I made a slight modification for the command after your help to lookup from X4 to Z(the last row)

which should be
DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT(""'" & StringVal & "'!R4C12:R" & OldRemainingLastRHoursowFirst & "C14"",0),2,0),0)"


and the indirect function finally worked after your help and retrieved variable sheet name .. but the vlookup now gives only 0's as the attached photo. So any help