CODEWITHSUNDEEP
vbaexcelloopsrangeformula
Adam
Adam

Reputation: 1

Loop through a range populating with a dynamic formula

Does anybody have a suggestion on how to speed up this loop? Our file has 9,000+ rows and the macro is taking too long. In the range is it going back to G1 every time it loops and do you have a suggestion of how to write the range more efficiently while still keeping the formula dynamic?

Dim lrow As Long
lrow = 1
For lrow = 1 To lastrow3
   Range("g1:g" & lrow).Formula = "=IF(A1=""SC"",(IF(LEN(E1)=3,""SC"",IF(LEN(E1)=4,LEFT(E1,1),LEFT(E1,2)))),""LB"")"
   Range("h1:h" & lrow).Formula = "=IF(D1="" "",RIGHT(E1,3),CONCATENATE(RIGHT(E1,3),F1))"

Thank you

Upvotes: 0

Views: 163

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

get rid of the loop all together:

Dim lrow as long
lrow = 100
Range("g1:g" & lrow).Formula = "=IF(A1=""SC"",(IF(LEN(E1)=3,""SC"",IF(LEN(E1)=4,LEFT(E1,1),LEFT(E1,2)))),""LB"")"
Range("h1:h" & lrow).Formula = "=IF(D1="" "",RIGHT(E1,3),CONCATENATE(RIGHT(E1,3),F1))"

This will fill all the cels from row 1 to row 100 in one step no loop needed.

By using the loop you are filling an ever increasing range with the formula. The first loop does row 1, the second doew row 1 to row 2 and so forth.

Row 1 get populated lrow times. That is what is slowing down your code.

Any relative references (no $ before the row number), will automatically be updated to the relative row.

Upvotes: 2

Related Questions