VLookUp in Macro not compiling

'=IF($AA3<> " ",VLOOKUP(ABS($AA3),'C:\Reference\[TranMaster.xls]Translator'!$A$11:$AC$54,2,TRUE)," ")

The above VLookUp statement runs great when its in a cell.

In a macro it won't compile. At the ' in front of 'C:\Reference\... the compiler says "Expected Expression."

Below is what is actually crashing

===============
If (sBreakDown <> " ") Then
sDistrict = Application.VLOOKUP(ActiveSheet.Cells(i,1),'C:\Reference\[TranMaster.xls]Translator'!$A$11:$AC$54,2,TRUE)," ")
End If
===============

Thanks for helping..Too many hours, not enough sleep! Craigm

You can't do an Application.VLOOKUP on a closed workbook. Worksheet
function, yes, VBA, no.

--
HTH

Bob Phillips

"Craigm" <Craigm.1rhjqf_1120212326.4986@excelforum-nospam.com> wrote in
message news:Craigm.1rhjqf_1120212326.4986@excelforum-nospam.com...
>
> '=IF($AA3<> "
>
",VLOOKUP(ABS($AA3),'C:\Reference\[TranMaster.xls]Translator'!$A$11:$AC$54,2
,TRUE),"
> ")
>
> The above VLookUp statement runs great when its in a cell.
>
> In a macro it won't compile. At the ' in front of 'C:\Reference\...
> the compiler says "Expected Expression."
>
> Below is what is actually crashing
>
> ===============
> If (sBreakDown <> " ") Then
> sDistrict =
>
Application.VLOOKUP(ActiveSheet.Cells(i,1),'C:\Reference\[TranMaster.xls]Tra
nslator'!$A$11:$AC$54,2,TRUE),"
> ")
> End If
> ===============
>
> Thanks for helping..Too many hours, not enough sleep! Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=383822
>

The worksheet is open. I open it in Excel manually.

sDistrict = Application.VLookup("1701-31-0000-301", Workbooks("C:\Reference\TranMaster.xls").Sheets("Translator").Range("A11:C55"), 2, True)

I am still having trouble getting to the WorkBook "TranMaster" and WorkSheet "Translator"

I tried taking out the "'C:\Reference\TranMaster.xls") and changed .Sheets to WorkSheets. I can access the WorkSheet as long as it is in the same Workbook.

It's looking into other WorkBooks that I falldown!

I am lost on this one!

Thanks for your help!

Craig

Try this form in VB:
Application.WorksheetFunction.VLookup(ActiveSheet.Cells(i,1),'C:\Reference\[TranMaster.xls]
_
Translator'!$A$11:$AC$54,2,TRUE)

Add .WorksheetFunction & remove orphaned If condition ," ") from the
end.

Mike F



"Craigm" <Craigm.1rhjqf_1120212326.4986@excelforum-nospam.com> wrote in
message news:Craigm.1rhjqf_1120212326.4986@excelforum-nospam.com...
>
> '=IF($AA3<> "
> ",VLOOKUP(ABS($AA3),'C:\Reference\[TranMaster.xls]Translator'!$A$11:$AC$54,2,TRUE),"
> ")
>
> The above VLookUp statement runs great when its in a cell.
>
> In a macro it won't compile. At the ' in front of 'C:\Reference\...
> the compiler says "Expected Expression."
>
> Below is what is actually crashing
>
> ===============
> If (sBreakDown <> " ") Then
> sDistrict =
> Application.VLOOKUP(ActiveSheet.Cells(i,1),'C:\Reference\[TranMaster.xls]Translator'!$A$11:$AC$54,2,TRUE),"
> ")
> End If
> ===============
>
> Thanks for helping..Too many hours, not enough sleep! Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
> http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=383822
>

Don't know where you are reading the responses but as I said elesewhere

sDistrict = Application.VLookup("1701-31-0000-301",
Workbooks("TranMaster.xls").Sheets("Translator").Range("A11:C55"), 2, True)

--
HTH

Bob Phillips

"Craigm" <Craigm.1rhpax_1120219586.0168@excelforum-nospam.com> wrote in
message news:Craigm.1rhpax_1120219586.0168@excelforum-nospam.com...
>
> The worksheet is open. I open it in Excel manually.
>
> sDistrict = Application.VLookup("1701-31-0000-301",
>
Workbooks("C:\Reference\TranMaster.xls").Sheets("Translator").Range("A11:C55
"),
> 2, True)
>
> I am still having trouble getting to the WorkBook "TranMaster" and
> WorkSheet "Translator"
>
> I tried taking out the "'C:\Reference\TranMaster.xls") and changed
> Sheets to WorkSheets. I can access the WorkSheet as long as it is in
> the same Workbook.
>
> It's looking into other WorkBooks that I falldown!
>
> I am lost on this one!
>
> Thanks for your help!
>
> Craig
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=383822
>

With your help I've got it working!