Urgent - Help VBA Macro

[Jeff]

Hello,

I have the following VBA Macro. But I'm getting an error msg on the Set
Lookup_Table_VBAK = ("A1:EZ65000").range
Can any one help?

Sub Macro1()
Dim VBAK As Variant
Dim Lookup_Table_VBAK As Range
VBAK = Application.GetOpenFilename
If VBAK = False Then Exit Sub 'User cancelled
Workbooks.Open Filename:=VBAK
Set Lookup_Table_VBAK = ("A1:EZ65000").range
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C:C", Lookup_Table_VBAK, 2, 0)

 

[JE McGimpsey]

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

 

[Jeff]

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?

 

[Dave Peterson]

It can't hurt...

Set Lookup_Table_VBAK = worksheets("sheet1").Range("A1:EZ65000")
or
Set Lookup_Table_VBAK = activeworkbook.worksheets("sheet2").Range("A1:EZ65000")

or something like this.

 

[Jeff]

This is my macro. I get a #n/a. Is there something wrong with my macro?

Sub Macro1()
Dim VBAK As Variant
Dim Lookup_Table_VBAK As Range
VBAK = Application.GetOpenFilename
If VBAK = False Then Exit Sub 'User cancelled
Workbooks.Open Filename:=VBAK
Set Lookup_Table_VBAK =
ActiveWorkbook.Worksheets("sheet1").Range("A1:EZ65000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

 

[Dave Peterson]

I do something like:

Option Explicit
Sub Macro1A()

Dim VBAK As Variant
dim VBAKWkbk as workbook
dim HeaderWks as worksheet
Dim Lookup_Table_VBAK As Range
dim res as variant

VBAK = Application.GetOpenFilename
If VBAK = False Then Exit Sub 'User cancelled
set vbakwkbk = Workbooks.Open(Filename:=VBAK)
Set Lookup_Table_VBAK = vbakwkbk.Worksheets("sheet1").Range("A1:EZ65000")

set headerwks = workbooks("Billable Jobs Tierney Total Validation.xls") _
.worksheets("Header-Sales")

res = application.vlookup(headerwks.range("C3").value, lookup_Table_VBAK, 2, 0)

if iserror(res) then
res = "Missing"
end if

headerwks.range("d3").value = res

end sub

 

[Jeff]

Thank you Dave.
What would I need to add to the macro to have the macro start at D3 to the
next empty row.

 

[Dave Peterson]

Is the next empty row defined by a different column--or do you just overwrite
anything that was in column D?

I used column C to find that last used cell:

Option Explicit
Sub Macro1A()

Dim VBAK As Variant
Dim VBAKWkbk As Workbook
Dim HeaderWks As Worksheet
Dim Lookup_Table_VBAK As Range
Dim res As Variant
Dim myRng As Range
Dim myCell As Range

VBAK = Application.GetOpenFilename
If VBAK = False Then Exit Sub 'User cancelled
Set VBAKWkbk = Workbooks.Open(Filename:=VBAK)
Set Lookup_Table_VBAK = VBAKWkbk.Worksheets("sheet1").Range("A1:EZ65000")

Set HeaderWks = Workbooks("Billable Jobs Tierney Total Validation.xls") _
.Worksheets("Header-Sales")

With HeaderWks
Set myRng = .Range("C3", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
res = Application.VLookup(myCell.Value, Lookup_Table_VBAK, 2, 0)
If IsError(res) Then
res = "Missing"
End If

myCell.Offset(0, 1).Value = res
Next myCell
End Sub

Thank you Dave.
What would I need to add to the macro to have the macro start at D3 to the
next empty row.

 

[Jeff]

Thank you
--
Regards,
Jeff

Is the next empty row defined by a different column--or do you just overwrite
anything that was in column D?

I used column C to find that last used cell:

Option Explicit
Sub Macro1A()

Dim VBAK As Variant
Dim VBAKWkbk As Workbook
Dim HeaderWks As Worksheet
Dim Lookup_Table_VBAK As Range
Dim res As Variant
Dim myRng As Range
Dim myCell As Range

VBAK = Application.GetOpenFilename
If VBAK = False Then Exit Sub 'User cancelled
Set VBAKWkbk = Workbooks.Open(Filename:=VBAK)
Set Lookup_Table_VBAK = VBAKWkbk.Worksheets("sheet1").Range("A1:EZ65000")

Set HeaderWks = Workbooks("Billable Jobs Tierney Total Validation.xls") _
.Worksheets("Header-Sales")

With HeaderWks
Set myRng = .Range("C3", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
res = Application.VLookup(myCell.Value, Lookup_Table_VBAK, 2, 0)
If IsError(res) Then
res = "Missing"
End If

myCell.Offset(0, 1).Value = res
Next myCell
End Sub