CODEWITHSUNDEEP
excelvba
Andy Dufresne
Andy Dufresne

Reputation: 25

Looping a formula in a column while data exists in another column

I'm stuck and I'm sure it's simple. I need to run a LOOKUP formula in Column E while data exists in Column A. Column A will have varying amounts of data/rows, it will never be a set number. The problem is that my code wants to loop through ALL of Column E and it takes the macro 20 minutes to run.

Here is what I have: 

Sub Run_Lookup()
      Range("A1").Select
      Do Until IsEmpty(ActiveCell)
         Range("E:E").Formula = "=lookup(A1, G:H)"
         ActiveCell.Offset(1, 0).Select
      Loop
   End Sub

I appreciate any advice.

EDIT: A Visual of my issue...

EDIT: A visual of my issue...

Upvotes: 0

Views: 59

Answers (1)

Selkie
Selkie

Reputation: 1255

There are a few ways you can go about this - I tend to dislike DoUntil:

1) Could you simply turn your data into a table? It'll auto-expand column E as needed, no need to be in VBA.

2) Write your loop to first find the end of your data (Hint: Google for LastRow Excel VBA), then loop

For I = 1 to LastRow
    'Code here
Next I

Upvotes: 2

Related Questions