CODEWITHSUNDEEP
excelvbaloopsrepeat
Paolo
Paolo

Reputation: 1

VBA loop, repeat formula through column

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?

Upvotes: 0

Views: 3183

Answers (3)

urdearboy
urdearboy

Reputation: 14590

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

Upvotes: 1

Kleberson Felix Silva
Kleberson Felix Silva

Reputation: 64

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.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

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" ?

Upvotes: 0

Related Questions