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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.