Excel VBA IF statement to perfrom calculation

simongilder

Board Regular
Joined
Nov 3, 2011
Messages
68
Hi There,

Iam trying to write a simple macro using an IF statement which does the following

Looks in values in Column A, if the value is X, then multiply the corresponding value in Column Y by -,, otherwise, do nothing.

Can anybody help?

I began with

For i = 1 To 65536

If Cells(i, 1).Value = "B" Then .....


End If


Not very far at all really!

Can anybody help?

many thanks

Simon
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
why not do it with a formula instead of a vba macro?
if you have headers in row 1 and data starting in row 2- put a formula like this somewhere in row 2-
=if(a2="B",y2*2,y2)
 

OFFICE360

New Member
Joined
Sep 26, 2014
Messages
9
try something like this. I have made the font bold for important areas.
edit it to suit your need.

Visni.
Code:
'[COLOR=#333333]Looks in values in Column A, if the value is X, then multiply the corresponding value in Column Y by -,, otherwise, do nothing.[/COLOR]

Sub editVal()
dim lastrow as long
dim i as long


lastrow = Range("A" & Rows.Count).End(xlUp).Row' get last row based on column A


For i = 1 To lastrow 


If Cells(i, "A").Value = "[B]x Value[/B]" Then'column A, x value can be replaced by your critiria.
        [U]Cells(i, [B]"Y"[/B]).Value = Cells(i, "Y").Value * Cells(i, "X").Value[/U]'edit value in column Y = x column value * column x value.
End If


next i


End Sub
 
Last edited:

simongilder

Board Regular
Joined
Nov 3, 2011
Messages
68
try something like this. I have made the font bold for important areas.
edit it to suit your need.

Visni.
Code:
'[COLOR=#333333]Looks in values in Column A, if the value is X, then multiply the corresponding value in Column Y by -,, otherwise, do nothing.[/COLOR]

Sub editVal()
dim lastrow as long
dim i as long


lastrow = Range("A" & Rows.Count).End(xlUp).Row' get last row based on column A


For i = 1 To lastrow 


If Cells(i, "A").Value = "[B]x Value[/B]" Then'column A, x value can be replaced by your critiria.
        [U]Cells(i, [B]"Y"[/B]).Value = Cells(i, "Y").Value * Cells(i, "X").Value[/U]'edit value in column Y = x column value * column x value.
End If


next i


End Sub

Many thanks for your responses...I am still working on this....

Kind regards

Simon
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,586
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top