CatDaddy
07-12-2011, 11:37 AM
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:
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
but when i send this string to the next subroutine to fill cells with formulas i recieve a 1004 error on the highlighted line:
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
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"
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
but when i send this string to the next subroutine to fill cells with formulas i recieve a 1004 error on the highlighted line:
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
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"