CODEWITHSUNDEEP
excelvba
Hayk
Hayk

Reputation: 47

Multiplying a column based on another column value

I am trying to multiply a column of values by 100 based on another column value. I get an error.

Sub macro1()
Dim f As Range

Set f = Range("B2:B5").Value

For i = 2 To 100000
    With Sheets("Sheet1")
        If .Range("A" & i).Value = "Pavel" Then
            .Range("B" & i).Value = f * 100
Next i
        End If
End Sub

Upvotes: 2

Views: 236

Answers (2)

Vityata
Vityata

Reputation: 43575

So... Whenever you have For you need Next. For If there is a need of End If, unless it is not on the same line. For With there is an End With needed.

Thus, in general, just to get you started somewhere, this works somehow:

Option Explicit

Sub Macro()
    Dim f As Range
    Dim i As Long

    Set f = Range("B2:B5")

    For i = 2 To 100000
        With Worksheets("Sheet1")
            If .Range("A" & i).Value = "Pavel" Then
                .Range("B" & i).Value = .Range("B" & i) * 100
            End If
        End With
    Next i    
End Sub

Take a look at the Option Explicit at the top of the code, it is really important and brings your code one level up - MSDN Option Explicit.

Upvotes: 0

chillin
chillin

Reputation: 4486

If you start a new line after the then in your IF statement, then you must end the block with End if (let's ignore the line continuation character for now) -- else the syntax is not valid.

So either do:

If 1 > 0 then
     Msgbox("True!")
End if

Or:

If 1 > 0 then Msgbox("True!")

But don't start a new line after then, and not include the End if.

Also, it's better to put Option Explicit before your code and your f variable seems unused -- but that wasn't your question.

Upvotes: 2

Related Questions