0

Before I get started, I have researched for solutions and seem unable to produce similar results. Notably, I attempted the solutions in How can I insert variable into formula in VBA And Using string variables within a formula in VBA

So, I have set a variable to refer to a named range in another workbook, for use in a vlookup. Even after trying double quotes or none at all it refuses to work, I also tried making it based off RC cell format instead of my Range("N2"). I know there are other ways to process the vlookup but I would like to solve this.

    Dim cwb as workbook ' (this is a different workbook than the current one)
    Dim crng as range
    Dim frmcount as long
    Dim checkcsht as string, cwbname as string

        Set crng = csht.Range("D:P")
        checkcsht = "crng"
        cwb.Names.Add Name:=checkcsht, RefersTo:=crng
        
        cwbname = cwb.Name
        cwbname = "'[" & Left(cwb.Name, Len(cwb.Name) - 5) & "]'!crng"
        Debug.Print cwbname
        
    'gather raildata from cont-report
        frmcount = Application.CountA(frmsht.Range("A:A"))
        With frmsht.Range("N2")
            .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-11],""" & cwbname & """, 12, False)), """", (VLOOKUP(RC[-11],""" & cwbname & """, 12, False)))"
            .AutoFill Destination:=.Parent.Range("N2:N" & frmcount), Type:=xlFillDefault
            Calculate
            .EntireColumn.Copy
            .EntireColumn.PasteSpecial xlPasteValues
        End With

Everything was DIM'd and set at some point to the proper value. Any ideas on what I am missing?

braX
  • 11,506
  • 5
  • 20
  • 33
Arktik
  • 17
  • 6
  • 2
    "it refuses to work" - what exactly does that look like? Do you get an error or ? – Tim Williams Nov 23 '22 at 22:16
  • You add a name to `cwb` but then in the formula you're only using part of the name of `cwb` ? Why take off the last 5 characters? – Tim Williams Nov 23 '22 at 22:25
  • Use the macro recorder to perform your tasks in with Excel, then look at the code it generates. – nicomp Nov 23 '22 at 22:34
  • I am receiving error 1004, application or object defined error. I remove the last 5 characters to remove '.xlsx', which was what I found different when using macro recorder to reference the workbook. – Arktik Nov 28 '22 at 14:13
  • I have tried keeping the entire workbook name, with .xlsx attached to no avail – Arktik Nov 28 '22 at 14:26
  • Found this on excel forums and solved issue [How to write VBA vlookup formula with variable workbook and sheet names](https://www.mrexcel.com/board/threads/how-to-write-vba-vlookup-formula-with-variable-workbook-and-sheet-names.1139722/) – Arktik Nov 28 '22 at 15:33

1 Answers1

0

You don't need the double quotes around table address

Change this erasing the single quotes:

cwbname = "[" & Left(cwb.Name, Len(cwb.Name) - 5) & "]!crng"

and this erasing the double quotes around table range.

 .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-11]," & cwbname & ", 12, False)),"""" , (VLOOKUP(RC[-11]," & cwbname & ", 12, False)))"
wrbp
  • 870
  • 1
  • 3
  • 9