Single Quotes in VBA

[eoinymc]

Hi,

I have the following formula, which works when I type it into a cell in excel:

=OFFSET(INDIRECT("'" & $AM6 & "'!$A$" & MATCH($A6,INDIRECT("'" & $AM6 & "'!$A:$A"),0)),31,1)

However, when I try to put this in VBA, I keep getting errors...I believe it's because of the double quotes and single quotes:

Tfc.Cells(6, 26)="=OFFSET(INDIRECT("'" & $AM6 & "'!$A$" & MATCH($A6,INDIRECT("'" & $AM6 & "'!$A:$A"),0)),31,1)"

I have tried to put in Chr(39) for single quotes and Chr(34) for double quotes, but it doesn't seem to work...

Anyone got any ideas as to why this won't work? Could it be because of Indirect? I think I can remember having issues with Indirect in VBA previously.

Thanks,

Eoin

 

[par60056]

try this:

Tfc.Cells(6, 26)="=OFFSET(INDIRECT(""'"" & $AM6 & ""'!$A$"" & MATCH($A6,INDIRECT(""'"" & $AM6 & ""'!$A:$A""),0)),31,1)"

 

[Jonmo1]

Do you really want the formula entered in the cell via VBA, or is the goal to get the actual result of the formula into the cell??
If you're doing this in VBA, it's just creating double work to use Indirect. You can specify the different sheets within VBA itself.

You might try something like

Dim ws As Worksheet, MyRow As Long

Set ws = SheetS(Tfc.Range("AM6").Value)
MyRow = Application.Match(Tfc.Range("A6"),ws.Range("A:A"),0)
Tfc.Cells(6,26).Value = ws.Cells(MyRow,1).Offset(31,1).Value