vlook from closed workbook that changes each week

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
Here is the macro I run now.

Sub Deal_Report_Formula()
'
Range("E5:H5").Select
Selection.FormulaArray = _
"=VLOOKUP(RC2,'F:\DEAL REPORT\[CURRENT DEAL REPORT.xls]Sheet1'!R2C1:R1792C7,{4,5,6,7},0)"
Range("A1").Select
End Sub

The problem I have is the report changes each week in size. sometimes its only 1000 rows, sometimes its 2500, or anywhere in between. How do I get it to work when the source size is not always the same?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Use a "Dynamic" named range. The range referred to by the name changes depending on how manyrows are there.

Insert/Name/Define and use a formula like this instead of the usual reference :-

= OFFSET(Mysheet!$A$1,0,0,COUNTA(Mysheet!$A:$A),10)

This refers to a 10 column range and counts *nonblank* cells in column A.
so hoose a column that does nothave blanks.

Then use something like :- =VLOOKUP(A1,'Book1.xls'!MyRange,2,FALSE)
 

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
I dont understand how this will work when both files are new files. doesnt the formula, to remember the range, have to exist in the spreadsheet?
Let me explain a little further what Im doing.

I run a query in access and have a macro that exports it to a file in a folder called dealreport.xls. I then need to run a vlookup out of the dealreport.xls into another book. The dealreport.xls file always has the same number of columns, but never has the same amount of rows. This is what im having trouble with, defining the size of dealreport.xls.
 

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
Im still trying to find an answer on how to do this. I hope bumping it to the top is ok.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127

ADVERTISEMENT

I feel I have answered your question in principle.
I suggest you try it and come back with any further query.
 

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
I was told at one time that using if you use a name range, you have to be working in the same book. I guess thats not true?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi RolanDoobies:

Working with EXCEL 97, I can work with a range name in a closed book but not a dynamic range.

As a work around could you use a range that is large enough to cover the largest number of records that you might encounter -- then you can use that single range for all cases.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Rolan,

You could programmatically set the range at run-time in dealreport.xls.

The macro assume that the range is located in Sheet1 and is called “MyRange”, and refers to A4:Cxxx, where xxx is the unknown number of rows (change the range to suit).
Code:
Sub myNameRng()
   
Dim wb As Workbook
Dim sFile As String
Dim sPath As String
sFile = "dealreport.xls"
sPath = "C:\Documents and Settings\AAA\"       ‘Change to suit

Application.ScreenUpdating = False
On Error Resume Next
'If the workbook is open, this will get a reference to it.
Set wb = Workbooks(sFile)

'If the workbook is not open, open it.
If wb Is Nothing Then
   'Confirm file can be found.
   If Len(Dir(sPath & sFile)) > 0 Then
      Set wb = Workbooks.Open(sPath & sFile)
    Else
     MsgBox "file not found " & sPath & sFile
     Exit Sub
    End If
End If

If wb Is Nothing Then
    MsgBox "Error getting data workbook"
      Exit Sub
 End If
On Error GoTo 0
  
With Worksheets("Sheet1")
   .Range("A4:C" & Cells(Rows.Count, 1).End(xlUp).Row).Name = "MyRange"
End With

Workbooks(sFile).Save
Workbooks(sFile).Close   ‘ Optional

End Sub

HTH

Mike
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
<I was told at one time .....>

Beware of "cannot be done". All that usually means is that the person who says it does not know how to do it. It is amazing how these get converted to "can do" via groups like this. Be flexible. Try alternatives.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,891
Messages
5,766,966
Members
425,391
Latest member
Alono23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top