CODEWITHSUNDEEP
vbadynamicexcel-formula
Juastle
Juastle

Reputation: 1

Linking cells to different worksheet by using vba to insert dynamic formulas

I am creating a new worksheet called varRef and am trying to link cells of an existing worksheet to this new worksheet by using VBA. I have been trying to solve it for a couple of hours but cannot get it right and cannot find a similar case on the web either.

The code is:

Dim varRef as Variant 'name of the new sheet
varRef = Inputbox("xyz") 'this is how I define the name
Dim intRow As Integer 'row that corresponds to the appropriate postion in the existing sheet
intRow = ActiveCell.Row 'see above
Cells(intRow, 22).Formula = "=IF(varRef & ""!I148""="""","""",varRef & _
""!I148"")" 'trying to link the contents within the same workbook

The result in the cell I get is

=IF(varRef & "!I148"="","",varRef & "!I148"

which is obviously not working.

Issue 1) is that VBA does not recognize my variable in the formula. It is working for naming the sheet however.

Issue 2) is the quotation marks, that are not working as intended. One is supposed to use double quotation marks to not end the string. However the second marks will not disappear in the final code of the cell.

Any help is very much appreciated and hopefully valuable for other users as well!

Upvotes: 0

Views: 131

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You had some count issues with hoe many " you have before and after the varRef variable.

Also, I prefer to use Chr(34) to have " inside the Formula string, this way I don't get confused with how many " I need to use.

Try the code below:

Cells(intRow, 22).Formula = "=IF(" & varRef & "!I148=" & Chr(34) & Chr(34) & "," _
                            & Chr(34) & Chr(34) & "," & varRef & "!I148)"

Upvotes: 1

Related Questions