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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)
 
Upvote 0
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.
 
Upvote 0
I feel I have answered your question in principle.
I suggest you try it and come back with any further query.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
<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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top