Vlookup to different workbook

[wafs]

I have writen the following code to do a vlookup into a different workbook. I get an compile error - syntax error, any suggestions why.

=Application.WorksheetFunction.Vlookup(myrange,workbook[Hardness conversion]!("sheet1").Range("D250"), 1, False)

 

[VicRauch]

I would suggest "()" around the [Hardness conversion] and/or maybe no "()" or quotes around sheet1.

this is only a quess.

 

[firefytr]

Your syntax is wrong for VBA ...

=Application.WorksheetFunction.Vlookup(myrange.Value,workbooks(Hardness conversion).("sheet1").Range("D250"), 1, False)

Yes, you need the () and "" around Sheet1. It's an Object.

-----------
Regards,
Zack Barresse

 

[wafs]

Thanks for the tips, I will give them a try.

 

[wafs]

When I key in the changes, I get an expected: identifier or bracketed expression. The error highlights the bracket in front of ("Sheet1"). Any suggestions on why.

=Application.WorksheetFunction.Vlookup(myrange.Value,workbooks(Hardness conversion).("sheet1").Range("A250"), 1, False)

 

[wafs]

I took out the . between (Hardness_conversion) and ("Sheets1"), now I get a syntax error. when running debug, hardness_conversion is empty. It is not pulling the data from sheet1 for the vlookup. Any suggestions on why this is happening.

 

[firefytr]

Put the period back in, surround your workbook name in quotes.

-----------
Regards,
Zack Barresse

 

[wafs]

Will try that.

 

[wafs]

I put the workbook name in quotes and the period back in and now I'm back to getting expected: identifier or bracketed expression again and it still highlights the first backet on ("sheet1").

I've tried looking it up (vlookup for workbooks) in google to try and fix this error, but I have no success.

 

[wafs]

new error. It gives me a syntex error on that line.

 

[Mizzness]

Wafs,

Have you tried coding via the recorder ?
Upon completion step into the code and adjust accordingly.

Hope this helps.

 

[firefytr]

I really do not see why this does not work ...

Application.WorksheetFunction.Vlookup(myrange.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)

?? Can you post your whole code?

-----------
Regards,
Zack Barresse

 

[wafs]

I did use the recorder and made changes to fit the program, but that did not work.

Here is the code you requested.

Sub Bolt15N()
Dim cellstart As Integer
Dim n As String
Dim testrange As String
Dim sh As String
Dim destsh As String
Dim p As Integer
Dim m As String
Dim testrange2 As Range
Dim bolt(1 To 100) As String 'holding space for Bolt 15N
Dim hardness As String
Dim BoltN As Variant
Dim BoltN2 As Variant
Dim BoltN3 As Variant
Dim BoltN4 As Variant
Dim BoltN5 As Variant
Dim Standard(1 To 100) As String
Dim myrange As Range
Dim myrange2 As Range
Dim myrange3 As Range
Dim myrange4 As Range
Dim myrange5 As Range
Dim Stand As Range
Dim Stand2 As Range
Dim Stand3 As Range
Dim Stand4 As Range
Dim Stand5 As Range

'Initialize Variables
cellstart = 8
sh = "Sheet2"
destsh = "1244 HT-DOC 12"
p = 0
n = cellstart

'Turn off Screen Updating
'Application.ScreenUpdating = False
For p = 1 To 34
n = n + 1
bolt(1) = "AA" + n
bolt(2) = Sheets(destsh).Range(bolt(1)).Value
bolt(3) = "AB" + n
bolt(4) = Sheets(destsh).Range(bolt(3)).Value
bolt(5) = "AC" + n
bolt(6) = Sheets(destsh).Range(bolt(5)).Value
bolt(7) = "AD" + n
bolt(8) = Sheets(destsh).Range(bolt(7)).Value
bolt(9) = "AE" + n
bolt(10) = Sheets(destsh).Range(bolt(9)).Value
Set myrange = Sheets(destsh).Range(bolt(1))
Set myrange2 = Sheets(destsh).Range(bolt(3))
Set myrange3 = Sheets(destsh).Range(bolt(5))
Set myrange4 = Sheets(destsh).Range(bolt(7))
Set myrange5 = Sheets(destsh).Range(bolt(9))

Standard(1) = "T" + n
Standard(2) = Sheets(destsh).Range(Standard(1)).Value
Standard(3) = "U" + n
Standard(4) = Sheets(destsh).Range(Standard(3)).Value
Standard(5) = "V" + n
Standard(6) = Sheets(destsh).Range(Standard(5)).Value
Standard(7) = "W" + n
Standard(8) = Sheets(destsh).Range(Standard(7)).Value
Standard(9) = "X" + n
Standard(10) = Sheets(destsh).Range(Standard(9)).Value
Set Stand = Sheets(destsh).Range(Standard(1))
Set Stand2 = Sheets(destsh).Range(Standard(3))
Set Stand3 = Sheets(destsh).Range(Standard(5))
Set Stand4 = Sheets(destsh).Range(Standard(7))
Set Stand5 = Sheets(destsh).Range(Standard(9))

BoltN = Application.WorksheetFunction.Vlookup(myrange.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)
If Stand >= BoltN + 2 Or Stand <= BoltN - 2 Then
myrange.Interior.ColorIndex = 3
BoltN = Application.WorksheetFunction.Vlookup(myrange2.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)
ElseIf Stand2 >= BoltN + 2 Or Stand2 <= BoltN - 2 Then
myrange2.Interior.ColorIndex = 3
BoltN = Application.WorksheetFunction.Vlookup(myrange3.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)
ElseIf Stand3 >= BoltN + 2 Or Stand3 <= BoltN - 2 Then
myrange3.Interior.ColorIndex = 3
BoltN = Application.WorksheetFunction.Vlookup(myrange4.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)
ElseIf Stand4 >= BoltN + 2 Or Stand4 <= BoltN - 2 Then
myrange4.Interior.ColorIndex = 3
BoltN = Application.WorksheetFunction.Vlookup(myrange5.Value,workbooks("Hardness conversion.xls").("sheet1").Range("A250"), 1, False)
ElseIf Stand5 >= BoltN + 2 Or Stand5 <= BoltN - 2 Then
myrange5.Interior.ColorIndex = 3
Else
n = n + 1
End If
Next
End Sub

 

[firefytr]

Oh, my, goodness. It should be ..

.Sheets("sheet1")

instead of ..

.("sheet1")

Sorry about that. Try that and see if it works. It compiles alright.

-----------
Regards,
Zack Barresse