Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Urgent - Help VBA Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeff
 
Posts: n/a
Default Urgent - Help VBA Macro
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)
--
Regards,
Jeff

Reply With QuoteReply With Quote
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default
Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)
Reply With QuoteReply With Quote
  #3   Report Post  
Jeff
 
Posts: n/a
Default
Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff



"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)

Reply With QuoteReply With Quote
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default
It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Jeff
 
Posts: n/a
Default
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:EZ65 000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

--
Regards,
Jeff



"Dave Peterson" wrote:

It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default
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 wrote:

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:EZ65 000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

--
Regards,
Jeff

"Dave Peterson" wrote:

It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #7   Report Post  
Jeff
 
Posts: n/a
Default
Thank you Dave.
What would I need to add to the macro to have the macro start at D3 to the
next empty row.

--
Regards,
Jeff



"Dave Peterson" wrote:

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 wrote:

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:EZ65 000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

--
Regards,
Jeff

"Dave Peterson" wrote:

It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson


--

Dave Peterson

Reply With QuoteReply With Quote
  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default
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

Jeff wrote:

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

--
Regards,
Jeff

"Dave Peterson" wrote:

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 wrote:

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:EZ65 000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

--
Regards,
Jeff

"Dave Peterson" wrote:

It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #9   Report Post  
Jeff
 
Posts: n/a
Default
Thank you
--
Regards,
Jeff



"Dave Peterson" wrote:

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

Jeff wrote:

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

--
Regards,
Jeff

"Dave Peterson" wrote:

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 wrote:

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:EZ65 000")
Windows("Billable Jobs Tierney Total Validation.xls").Activate
Sheets("Header-Sales").Select
Range("D3").Value = Application.VLookup("C3", Lookup_Table_VBAK, 2, 0)

--
Regards,
Jeff

"Dave Peterson" wrote:

It can't hurt...

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

or something like this.





Jeff wrote:

Hi,

Do I need to indicate a worksheet for my range Set Lookup_Table_VBAK ?
--
Regards,
Jeff

"JE McGimpsey" wrote:

Try:

Set Lookup_Table_VBAK = Range("A1:EZ65000")

In article ,
"Jeff" wrote:

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)


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent Help Required on Excel Macro Problem Sachin Shah Excel Discussion (Misc queries) 1 August 17th 05 06:26 AM
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 10:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 12:40 AM
URGENT Mac/PC macro compatibility problem marika1981 Excel Discussion (Misc queries) 7 January 8th 05 12:43 AM


All times are GMT +1. The time now is 04:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement