Using data from a defined sheet for a formula in another sheet (VBA)

[Dying]

Hi all,

Dying here (First thread yay), I am dealing with a lot of functions which uses different sheets for the same purpose so I had to define some sheet with a variable (not sure if I said it correctly I'm bad with the terms).
tldr: Need to vlookup data from a defined sheet (and not by using the sheet's name) for a formula in active sheet with vba.

For example:

Public calc As Worksheet 'define sheet

Sub setcalc()

Set calc As Sheets("DataSet3") 'set sheet
Sheets("DataSet6").Activate
Range("C2").Formula = "=IFERROR(IF(VLOOKUP($A2&C$1,calc!$C:$C,1,FALSE)=DataSet6!$A2&C$1,""x""),"""")" 'you can ignore what the formula is for
'before you say "why not just put "DataSet3!" instead of "calc!" well like I said it's for lots of function I'm putting this as an example

Set calc As Sheets("DataSet4") 'set sheet
Sheets("DataSet7").Activate
Range("C2").Formula = "=IFERROR(IF(VLOOKUP($A2&C$1,calc!$C:$C,1,FALSE)=DataSet7!$A2&C$1,""x""),"""")"
End Sub


calc! doesn't seem to work as "xxx!" can only be used when referencing to a sheet name (correct me if I'm wrong). How would I be able to use the defined calc Worksheet's value in the formula?

Not sure if anyone had post about this before because I don't know how to search for this problem. Any help is well appreciated, thanks!!

 

[yky]

calc is an object. What you need in the formula is a string, namely, calc.name.

See if the following works for you. Basically, you escape the variable by breaking down the long string into three parts (before-variable, variable, after-variable) and joint them with &. Since the variable in this case is a sheet name, it needs to be enclosed in apostrophes.

Range("C2").Formula = "=IFERROR(IF(VLOOKUP($A2&C$1,'" & calc.name & "!$C:$C,1,FALSE)=DataSet6!$A2&C$1,""x""),"""")"

 

[Dying]

Hi yky!

Thanks for breaking down the steps for me, it helped me to understand- though I don't understand why do we need to break it into three parts.

The code you gave is forgetting an extra apostrophe, but with that it worked great! Thank you so much, you're awesome!! Didn't think it was possible

working code:
Range("C2").Formula = "=IFERROR(IF(VLOOKUP($A2&C$1,'" & calc.name & "'!$C:$C,1,FALSE)=DataSet6!$A2&C$1,""x""),"""")"

 

[yky]

Good that you figured out I missed an apostrophe.

If variable is enclosed in double quotation marks, Excel treats it as string, not variable. So, variables need to be alone, staying outside of double-quotation marks. Because your variable is in the middle of a string, you need to break the string into three parts, before-variable, variable, and after-variable. If it is in the beginning or end, you only need two parts, before-variable and variable, or variable and after-variable.

 

[Dying]

Woah! Thank you so much for explaining that to me, kinda understand it better now. This will definitely be useful in my future assignments.

vba is so interesting..