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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,464
Messages
5,528,957
Members
409,848
Latest member
Blomsten
Top