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]

That photo cannot be the result of running my last code.

 

[MostafaNabil]

That photo cannot be the result of running my last code.

This is the result of your code.

 

[RoryA]

Yes, and that formula was not present in the photo you posted.

 

[MostafaNabil]

Yes, and that formula was not present in the photo you posted.

So, could you help me please with the following issue after updating my code.
as The following code portion should perform Vlookup with variable sheetname. However, the vlookup returns only 0's not the desired value from the lookup range, considering that the lookup range has the same structure across these multiple sheets with variable name, so any advise.

Dim SheetName As Variant: SheetName = DestinationWs.Name

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

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

Unfortunately, the following uploaded photo is the result of the code.

 

[RoryA]

Why did you take the INDIRECT part out of the formula?