For-Loop to assign index value Excel VBA

Question

Apologies for the naive nature of this question but I am very new to VBA.

I have a column of data with the number of pageviews a particular page has had.

I then have a seperate sheet that assigns an index value of between 1 and 30 depending whether the number of page views exceeds a specific number.

For example if a page has 10,000 page views then that is an index value of 4 as index 4 is any number over and including 8,640 and upto 10,368 where it would become index 5.

As I have many rows of data to complete this indexing on I would like to create a loop that will check what index each page should be assigned and then print the index in a new column in the same row.

I have been looking at tutorials but can't find anything specific enough to help me out. If anyone has any advice or a quick example to get me started that would be much appreciated :)

You don't need VBA for this. You can use a VLOOKUP formula in the new column. See this Contextures page grade range example. — Doug Glancy
– Doug Glancy, Commented Jul 29, 2013 at 16:56
Why don't you just use a vlookup? with range_lookup set to TRUE. — Declan_K
– Declan_K, Commented Jul 29, 2013 at 16:58
Thanks for these comments and the link. If however I wanted to use VBA for this is it possible? — Tom Pinchen
– Tom Pinchen, Commented Jul 29, 2013 at 21:34

Reafidy · Accepted Answer · 2013-07-29 22:10:10Z

2

Yes you can do it with VBA, although as others have mentioned its not required. Forget about looping, its slow and unnecessary.

Sub HTH()

    With Sheet1.Range("A1", Sheet1.Cells(Rows.Count, "A").End(xlUp)).Offset(, 1)
        .Formula = "=VLOOKUP(A1,Sheet2!A$1:B$5,2)"
        .Value = .Value
    End With

End Sub

Assumes you have the index layed out like this:

And your pageviews in column A on sheet2.

answered Jul 29, 2013 at 22:10

Reafidy

8,4796 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

For-Loop to assign index value Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related