VBA to Multiply cell calue if another cell equals certain value

[nirvehex]

Question:

In column Y i have numbers:

y2 = 8
y3 = 8
y4 = 8
y5 = 7
y6 = 6
y7 = 4
y8 = 1.5

and so on....

In column X I have text:


x2 = no
x3 = yes
x4 = no
x5 = yes
x6 = yes
x7 = yes
x8 = no

What I would like to do is where column X reads "No", in the corresponding Y cell I would like to multiply the existing value by 2.5 and have it become the new value for that cell. S

So for example, after I run the macro, the cell values would read as follows:

y2 = 20
y3 = 8
y4 = 20
y5 = 7
y6 = 6
y7 = 4
y8 = 3.75

Any idea how to do this on VBA?

Thanks!

 

[XOR LX]

Hi.

Sub Multiply()


For Each r In Range("Y2:Y" & Cells(Rows.Count, "Y").End(xlUp).Row)
      
      If r.Offset(0, -1).Value = "no" Then
            r.Value = r.Value * 2.5
      End If

Next r


End Sub

Regards

 

[nirvehex]

Thanks for the info. The code keeps breaking as soon as it goes to run it though. It breaks on the "For Each r In Range("Y2:Y" & Cells(Rows.Count, "Y").End(xlUp).Row)" line.

It says "Object required"

Any idea why?

 

[XOR LX]

Sorry - no.

Perhaps there is some fileshare site that you use?

Regards

 

[nirvehex]

Sorry - no.

Perhaps there is some fileshare site that you use?

Regards

No fileshare site.

Do I have to declare any variables?

 

[XOR LX]

No fileshare site.

Do I have to declare any variables?

I didn't and it worked fine.

Regards

 

[nirvehex]

I didn't and it worked fine.

Regards

Hey I figured out what was going on.. Needed to make it Rows not Row.

Sub CleanExport() 
    For Each r In Range("Y2:Y" & Cells(Rows.Count, "Y").End(xlUp).Row) 
        If UCase(r.Offset(0, -1).Value) = "TOT" Then 
            r.Value = r.Value * 0.00495113169 
        End If 
    Next r 
End Sub