VBA loop, repeat formula through column

Question

I am trying to replicate in VBA the simple function in excel which allows you to repeat a function through an entire column, and stops when the columns on the side are empty. Specifically, I want to repeat an if - else if function for the entire relevant part of the column

Here's an attempt which does not really work

Sub RepeatIfElseif
    Range("A1").Select
    If selection > 0 Then
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "X"
        Range("A1").Select
    ElseIf selection <= 0 Then
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Y"
    End If
    Range("B1").Select
    selection.AutoFill Destination:=Range("B1:B134")

Is there any way I can do it with a loop?

urdearboy · Accepted Answer · 2019-03-13 23:07:22Z

1

You do not need to loop to drop formulas in. You just need to know where the last row is!

Pick a column that is most likely to represent your last row (I am using Column A in my example) and then you can dynamically drop-down your equation in one line without the loop.

The below will fill in the equation A2 + 1 in Column B starting from 2nd row (assuming you have a header row) down to the last used row in Column A

Option Explicit

Sub Formula_Spill()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update sheet!
Dim LR As Long

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row '<-- Update column!

ws.Range("B2:B" & LR).Formula = "=A2+1" '<-- Update formula!

End Sub

edited Mar 13, 2019 at 23:07

answered Mar 13, 2019 at 23:02

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

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

Comments

Shai Rado · Accepted Answer · 2019-03-13 20:42:52Z

0

If you want to use a loop, you can use something like the code below:

For i = 1 To 134
    If Range("A" & i).Value > 0 Then
        Range("B" & i).FormulaR1C1 = "X"
    Else
        Range("B" & i").FormulaR1C1 = "Y"
    End If
Next I

It can be done without a loop, something like:

Range("B1:B134").Formula = "=IF(A1>0," & Chr(34) & "X" & Chr(34) & "," & Chr(34) & "Y" & Chr(34) & ")"

Not sure what formula you are trying to achieve with .FormulaR1C1 = "Y" ?

answered Mar 13, 2019 at 20:42

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Comments

Kleberson Felix Silva · Accepted Answer · 2019-03-13 22:27:46Z

0

I'm trying to improve my English, I swear...

I would do something like this:

dim row as long
dim last_row as Long

last_row = ActiveSheet.Range("A1048576").End(xlUp).Row

For row = 1 to last_row
    If Range("A" & row).Value > 0 Then
        ActiveSheet.Range("B" & row).Value = "X"
    Else
        ActiveSheet.Range("B" & row).Value = "Y"
    End If
Next row

Hope this helps.

answered Mar 13, 2019 at 22:27

Kleberson Felix Silva

641 bronze badge

2 Comments

Paolo Over a year ago

Thanks, that's exactly what I was looking for

Kleberson Felix Silva Over a year ago

Great! I am glad to hear that.

Collectives™ on Stack Overflow

VBA loop, repeat formula through column

3 Answers 3

You do not need to loop to drop formulas in. You just need to know where the last row is!

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

You do not need to loop to drop formulas in. You just need to know where the last row is!

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related