Solved: Setting wb.Name to string for VLOOKUP formula

[CatDaddy]

I am trying to search the open workbooks for the name of the other workbook (because the full name will include a changing date, but will always contain certain characteristics:

[vba]For Each wb In Application.Workbooks
If InStr(1, wb.Name, "somecriteria", vbTextCompare) Then
vRngVDNR = "'[" & wb.Name & "]OPEN'!$A:$M"

ElseIf InStr(1, wb.Name, "someothercriteria", vbTextCompare) Then
vRngAsset = "'[" & wb.Name & "]MFD'!$A:$CN"
End If

Next[/vba]

but when i send this string to the next subroutine to fill cells with formulas i recieve a 1004 error on the highlighted line:

[vba]Private Sub InsertFormulasVDNR(i As Long, vRngVDNR As String)
With ActiveWorkbook.Sheets("MFD")
'Assigns cell value to reference formula
.Cells(i, 15).Formula = "=VLOOKUP(M" & i & ", " & vRngVDNR & ", 5, False)"
.Cells(i, 16).Formula = "=IF(N" & i & "=O" & i & ", ""True"", ""False"")"
.Cells(i, 20).Formula = "=VLOOKUP(M" & i & ", " & vRngVDNR & ", 6, False)"
.Cells(i, 21).Formula = "=IF(S" & i & "=T" & i & ", ""True"", ""False"")"
.Cells(i, 25).Formula = "=VLOOKUP(M" & i & ", " & vRngVDNR & ", 7, False)"
.Cells(i, 26).Formula = "=IF(X" & i & "=Y" & i & ", ""True"", ""False"")"

'Reassigns cells to value of formula
.Cells(i, 15).Value = .Cells(i, 15).Value
.Cells(i, 20).Value = .Cells(i, 20).Value
.Cells(i, 25).Value = .Cells(i, 25).Value
End With
End Sub[/vba]

This program was working before when vRngVDNR = "VDNR!A:M" as a tab in the same workbook, but when i try to vlookup in a different workbook it fails.
When i debug it appears to give me the right string "'[VDNR]OPEN'!A:M"

[p45cal]

remove the space, (shown below replaced by a # character):
.Cells(i, 15).Formula = "=VLOOKUP(M" & i & ",#" & vRngVDNR & ", 5, False)"

edit post posting: although doing that removed the error, when I replaced the space it still worked! so I don't know what's going on.

When you go into debug, try
?i
in the immediate pane; is it a valid row no.?

[CatDaddy]

that was not the only problem...same error

[p45cal]

see altered message #2 re i beng valid row no.

[CatDaddy]

no good:
i = 2 (to 57) integers (valid row numbers)
vRngVDNR = "'[Book1.xlsx]OPEN'!A:M"

so the formula should read:
=VLOOKUP(M2,'[Book1.xlsx]OPEN'!A:M,5,False) --> which i believe is the correct syntax

[p45cal]

You have saved the file Book1 at least once, haven't you?

[CatDaddy]

yessssssssssssssssssssssssss

is what i have what the vlookup should look like if it were to ever make it to the cell?

[Aflatoon]

That code works for me as long as the inputs are valid. If the sheet name were incorrect for example (say you had a trailing space after OPEN) then you would get a 1004 error.

[CatDaddy]

That code works for me as long as the inputs are valid. If the sheet name were incorrect for example (say you had a trailing space after OPEN) then you would get a 1004 error.

thank you, seems like sound advice (let you know in a few minutes)

[CatDaddy]

ok, i dont like it...there were no extra spaces on the names of the sheets, but when i renamed them the program worked fine...(I RENAMED THEM EXACTLY THE SAME?!?!?!?!?!?!?!)

thanks for your help!