CODEWITHSUNDEEP
vbaexcelexcel-formula
acr
acr

Reputation: 1746

How to run a formula on each cell in a range

I am looking for a excel VBA which will go through a specific range (A1 to C5) and if B column is zero, copy C column data into D

enter image description here

Dont hav much experience with excel VBA, I have tried the code (which created with help of formula), but no luck. 

Sub Macro1()
'
' Macro1 Macro

Dim FLrange As Range
Set FLrange = Range("A1:C5")

For Each FLrange In ActiveWindow.RangeSelection

If FLrange.Value = 0 Then D$ = C$

Next FLrange

End Sub

can someone correct if it is wrong

Upvotes: 2

Views: 12158

Answers (2)

Salvatore Fanale
Salvatore Fanale

Reputation: 113

this code will do exactly what you asked for:

Sub myMacro()
Dim myRange As Range
Set myRange = Range("B2:B5")

For Each cell In myRange
    If Range(cell.Address).value = 0 Then
        Range(cell.Offset(0, 2).Address).value = Range(cell.Offset(0, 1).Address).value
    End If
Next

End Sub

However what you have requested is easily possible simply using an equation. I am assuming you are looking for a grander VBA solution and you only provided a sample of your end goal for simplicities sake. If NOT, why not just use if/than functions in the cell...

Upvotes: 0

Automate This
Automate This

Reputation: 31394

I agree with @tigeravatar about the formula option but if you really want VBA start with this:

Sub Macro1()
    Dim FLrange As Range
    Set FLrange = Range("B2:B5")

    For Each cell In FLrange
        If cell.Value = 0 Then cell.Offset(0, 2) = cell.Offset(0, 1)
    Next cell
End Sub

Upvotes: 5

Related Questions