Range too small must be dynamic

I believe this works to use headers as a reference for vlookup.
However, the initial range must be dynamic - right now     Set Filtered_Data = Range("A1:N1000")
There may be many more rows/columns that the user has.
Later on, this is causing an issue with user data. (their "Zone" is in column W, far outside

Option Explicit
Sub GroundRating()
    Application.ScreenUpdating = False
    Dim NextCol As Long, LastRow As Long
    Dim Filtered_Data As Range
    Set Filtered_Data = Range("A1:N1000")
    
    Dim HRow As Range
    Dim cl As Object
    Dim RWt, Zn As String
    Set HRow = Range("A1", Range("A1").End(xlToRight))
    For Each cl In HRow
        Select Case cl.Value
            Case "Rated Weight"
                RWt = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
            Case "Zone"
                Zn = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
        End Select
    Next cl
    
    With Filtered_Data
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(1, NextCol) = "Ground Rates"
        .Range(.Cells(2, NextCol), .Cells(LastRow, NextCol)).Formula = _
            "=IF(" & RWt & "2>=150,(VLOOKUP(" & RWt & "2,GR," & Zn & "2,TRUE)/150)*" & RWt & _
            "2,VLOOKUP(" & RWt & "2,GR," & Zn & "2,FALSE))"
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

Euro5Asked:
Who is Participating?
 
Glenn RayExcel VBA DeveloperCommented:
Hi Euro5,

The reason that range was defined for only 1000 rows was because in your previous question that range name was not defined in the VBA code (Filtered_Data).  I had to give it some definition in order to test the code on a new dataset.

Now that I've been able to open your binary file and see the construction properly, I can give you the revised code for this procedure:
Option Explicit
Sub GroundRating()
    Application.ScreenUpdating = False
    Dim NextCol As Long, LastRow As Long
    
    Dim HRow As Range
    Dim cl As Object
    Dim RWt, Zn As String
    Set HRow = Range("A1", Range("A1").End(xlToRight))
    For Each cl In HRow
        Select Case cl.Value
            Case "Rated Weight"
                RWt = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
            Case "Zone"
                Zn = Left(cl.EntireColumn.Address, InStr(1, cl.EntireColumn.Address, ":") - 1)
        End Select
    Next cl
    
    With Sheets("Filtered_Data")
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(1, NextCol) = "Ground Rates"
        .Range(.Cells(2, NextCol), .Cells(LastRow, NextCol)).Formula = _
            "=IF(" & RWt & "2>=150,(VLOOKUP(" & RWt & "2,GR," & Zn & "2,TRUE)/150)*" & RWt & _
            "2,VLOOKUP(" & RWt & "2,GR," & Zn & "2,FALSE))"
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window


This removes lines 5-6 and changes line 21 (now line 19).


Regards,
-Glenn
0
 
[ fanpages ]IT Services ConsultantCommented:
PS. I have just posted another variation for a solution to your previous question:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28672209.html#a40773159 ]
0
 
Saurabh Singh TeotiaCommented:
Change this line...

Set Filtered_Data = Range("A1:N1000")

Open in new window


To these lines...

Dim myrw As Long
myrw = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set Filtered_Data = Range("A1:N" * myrw)

Open in new window


This will take care of the fact that in case if you have more then 1000 rows of data...

Saurabh...
0
 
Euro5Author Commented:
Excellent as usual! Thanks for helping with that - the data is not limited to number of columns or rows.
0
 
[ fanpages ]IT Services ConsultantCommented:
"Excellent as usual! Thanks for helping with that - the data is not limited to number of columns or rows."

You're welcome.

Oh wait... never mind.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.