Vlookup and Indirect through VBA

[dmorgan20]

Okay so I thought I was on a roll today (not needed to post all day, ha)

Im transferring an Excel formula from Access-Excel, however VBA thinks I am using the ' to comment out part of the code.

The Code:

                        ActiveCell.FormulaR1C1 = _
                            "=VLOOKUP($A2,INDIRECT("'"&Index!B2&"'!"&"A2:D1000"),2,FALSE)"

As soon as I go on the to next line of code VBA changes the above slightly to this:

                        ActiveCell.FormulaR1C1 = _
                            "=VLOOKUP($A2,INDIRECT(" ' "&Index!B2&"'!"&"A2:D1000"),2,FALSE)"

Which causes half the formula to comment itself out, there has to be a way around this?

 

[pbaldy]

What would the finished formula look like if you typed it in a cell?

 

[Minty]

Try putting triple quotes around the whole thing ?

ActiveCell.FormulaR1C1 = """=VLOOKUP($A2,INDIRECT("'"&Index!B2&"'!"&"A2:D1000"),2,FALSE)"""

 

[dmorgan20]

@pbaldy: Typed in a cell it looks like this, and works:

=VLOOKUP($A2,INDIRECT("'"&Index!$B$2&"'!"&"$A$2:$D$1000"),2,FALSE)

@minty: Thank you for this, I tried it but the same thing happens

 

[dmorgan20]

I think this is the correct one, trying it now:

                            "=VLOOKUP(RC1,INDIRECT(""'""&Index!R2C2&""'!""&""$A$2:$D$1000""),2,FALSE)"

 

[The_Doc_Man]

No sure your intent since I don't do very much VBA in Excel.

If you wanted a formula like:

=VLOOKUP($A2,INDIRECT('<index>'!B2'!A2:D1000'),2,FALSE)

then your problem is too many quotes. I'm going to take a shot but I'm guessing a bit here. I'll space it out to be clearer.

ActiveCell.FormulaR1C1 = 
"=VLOOKUP( $A2, INDIRECT( '" & Index!B2 & "'!A2:D1000" ), 2, FALSE )"

 

[Gasman]

You would not have quotes in the formula though?

=VLOOKUP($D13,Data!$D:$N,11,FALSE)

 

[Mark_]

Try

"=VLOOKUP($A2,INDIRECT(" &_    'should give =VLOOKUP($A2,INDIRECT(
chr(34) &_ ' For the first DOUBLE quote
chr(39) &_ ' For the first SINGLE quote
chr(34) &_ ' For the second DOUBLE quote
"&Index!$B$2&" &_
chr(34) &_ ' For the first DOUBLE quote
chr(39) &_ ' For the first SINGLE quote
"!" &_
chr(34) &_
"&" &_
chr(34) &_
"$A$2:$D$1000" &_
chr(34) &_
"),2,FALSE)"

This way you build up the string by telling it EXACTLY what characters are being used and avoid having VBA assume single and double quotes are being used for something else.

 

[dmorgan20]

Thank you Mark

I got it working with the following:

             ActiveCell.FormulaR1C1 = _
                            "=VLOOKUP(RC1,INDIRECT(""'""&Index!R2C2&""'!""&""$A$2:$D$1000""),2,FALSE)"

Another issue I have noticed is that the code works every 2 times, the first time it will work, bit the second will throw out an error of:

Object Variable or With variable not set

My entire block of code is:

With xlSh
'Create a list of FITs ID from the CFR table, then add formulas
.Name = "ID"
.Select
.Range("A1").Value = "FullFITID"
.Range("B2").Select
xlSh.EnableCalculation = True
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,INDIRECT(""'""&Index!R2C2&""'!""&""$A$2:$D$1000""),2,FALSE)"
' Selection.AutoFill Destination:=Range("B2:B100000")
' Selection.AutoFill Destination:=Range("B2:D2"), Type:=xlFillDefault
' .Range("C2:D2").Select
' Selection.AutoFill Destination:=Range("C2:D89394")
.Range("A2").CopyFromRecordset rstA

'Add new worksheet ready for the first data dump
xlWB.Worksheets.Add After:=xlSh
.Select
End With

 

[JHB]

..Another issue I have noticed is that the code works every 2 times, the first time it will work, bit the second will throw out an error of:

Because you're missing the reference to the Excel object.
Try the below:

[SIZE=4] [B][COLOR=Red].[/COLOR][/B][/SIZE]ActiveCell.FormulaR1C1 =...

 

[dmorgan20]

Thank you, doing that highlights this section:

.ActiveCell

Followed by this error:

Method or data member not found

 

[JHB]

Have you that?

[B][COLOR=Red].[/COLOR][/B]ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,INDIRECT(""'""&Index!R2C2&""'!""&""$A$2:$D$1000""),2,FALSE)"[B][COLOR=Red]
[/COLOR][/B]

 

[dmorgan20]

Yes, when running the code its highlighting the top line:

Public Sub ExportQuery(ByVal Query As String, ByVal ShowToUser As Boolean, ByVal TabName As String, ByVal TabCount As Integer, ByRef xlApp As Excel.Application, ByRef xlWB As Object)

 

[JHB]

Okay, then try the reference to the Excel instead.

[B][COLOR=Red]xlApp[/COLOR][COLOR=Red].[/COLOR][/B]ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(RC1,INDIRECT(""'""&Index!R2C2&""'!""&""$A$2:$D$1000""),2,FALSE)"

Else post the whole code in that Sub.

 

[dmorgan20]

Thank you JHB - That appears to have worked, as simple as that then lol

Quick follow on question though, why do I not need to use xlAPP on the below:

.Name = "ID"
                        .Select
                        .Range("A1").Value = "FullFITID"
                        .Range("B2").Select

 

[JHB]

..
Quick follow on question though, why do I not need to use xlAPP on the below:

.Name = "ID"
                        .Select
                        .Range("A1").Value = "FullFITID"
                        .Range("B2").Select

You use the xlSh, (because you don't show the entire code, I can't tell you where you Set the xlSh to the Excel reference but I'm 100% sure you do it).

With[B][COLOR=Red] xlSh[/COLOR][/B] 'Create a list of FITs ID from the CFR table, then add formulas 
  .Name = "ID"
  ...

 

[dmorgan20]

I see, thank you

Final question if you don't mind.

the formula now successfully goes in to Excel, however the cell the formula is in is showing a #REF!

If I double click the cell then press ENTER, the formula updates and works. Know a way how this can be done automatically?

 

[JHB]

I see, thank you

Final question if you don't mind.

the formula now successfully goes in to Excel, however the cell the formula is in is showing a #REF!

If I double click the cell then press ENTER, the formula updates and works. Know a way how this can be done automatically?

Without exactly knowing it - seems a reference can't be found.
Helping you further I need the database and the Excel file.

 

[The_Doc_Man]

You don't need to qualify the four items you named because they relate to the sheet you identified in your WITH clause. You can leave off xlSH inside the WITH context because it will supply that.

On the other hand, .ActiveSheet is not a property or member of xlSH; i.e. it belongs to the app, not the sheet.

As to getting that #REF fixed, it appears that you are forcing it to recalculate. If so, you might try selecting the cell that gives you that problem and use the .Calculate method on it. The article I'm quoting has three or four options to do varying degrees of the same thing.

https://stackoverflow.com/questions/154434/getting-excel-to-refresh-data-on-sheet-from-within-vba

 

[Gasman]

Try

[XLObject]Application.Volatile

at the top of your code

I see, thank you

Final question if you don't mind.

the formula now successfully goes in to Excel, however the cell the formula is in is showing a #REF!

If I double click the cell then press ENTER, the formula updates and works. Know a way how this can be done automatically?