Simple VBA

[brooklyn2007]

Hello, I'm all new with this VBA and I'm trying to learn by myself. I have a very simple problem for a VBA expert. I need to use an IF, Else Statement but still is very hard for me. In my problem I need to put all my data in the cells in the code that I'm displaying below. If A2is equal to zero, display all the data in B1 and B2, otherwise do't display anything in B1 and B2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		Difference
2		483					Total	46
3
4
5
6			John	Kenny
7			23	23
8								Multiplication	529
9
10
11
Sheet1

Sub Jim()
Range("c6").Value = "John"
Range("d6").Value = "Kenny"
Range("g2").Value = "Total"
Range("H8").Value = "Multiplication"
Range("i8").Value = "=c7*d7"
If Range("a2").Value = "" Then Range("b2").Value = "=i8-h2"
Range("b1").Value = "Difference"
End Sub

How do I use otherwise or Else in my code? Thx

 

[repairman615]

Hello,

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Jim()<br>    <br>    Range("c6").Value = "John"<br>    Range("d6").Value = "Kenny"<br>    Range("g2").Value = "Total"<br>    Range("H8").Value = "Multiplication"<br>    Range("i8").Value = "=c7*d7"<br>        <br>        <SPAN style="color:#00007F">If</SPAN> Range("a2").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            Range("b2").Value = "=i8-h2"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Range("B2").Value = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    Range("b1").Value = "Difference"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[brooklyn2007]

Hello,

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Jim()<br>****<br>****Range("c6").Value = "John"<br>****Range("d6").Value = "Kenny"<br>****Range("g2").Value = "Total"<br>****Range("H8").Value = "Multiplication"<br>****Range("i8").Value = "=c7*d7"<br>********<br>********<SPAN style="color:#00007F">If</SPAN> Range("a2").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>************Range("b2").Value = "=i8-h2"<br>********<SPAN style="color:#00007F">Else</SPAN><br>************Range("B2").Value = ""<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<br>****Range("b1").Value = "Difference"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Thx, but I don't want to display the value "difference" in b1 if a2 has a value bigger than 0. If A2 has a value bigger than zero then b1 and b2 should be empty.

 

[brooklyn2007]

Again, I hope I explain properly. If A2 is bigger than zero, cell b1 and cell b2 should be empty. I think I need an And statement as well but I don't know were to put it. Thanks

 

[repairman615]

How about:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Jim()<br>    <br>    Range("c6").Value = "John"<br>    Range("d6").Value = "Kenny"<br>    Range("g2").Value = "Total"<br>    Range("H8").Value = "Multiplication"<br>    Range("i8").Value = "=c7*d7"<br>        <br>        <SPAN style="color:#00007F">If</SPAN> Range("a2").Value > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("B2").Value = ""<br>            Range("B1").Value = ""<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Range("b2").Value = "=i8-h2"<br>            Range("b1").Value = "Difference"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[brooklyn2007]

How about:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Jim()<br>****<br>****Range("c6").Value = "John"<br>****Range("d6").Value = "Kenny"<br>****Range("g2").Value = "Total"<br>****Range("H8").Value = "Multiplication"<br>****Range("i8").Value = "=c7*d7"<br>********<br>********<SPAN style="color:#00007F">If</SPAN> Range("a2").Value > 0 <SPAN style="color:#00007F">Then</SPAN><br>************Range("B2").Value = ""<br>************Range("B1").Value = ""<br>********<SPAN style="color:#00007F">Else</SPAN><br>************Range("b2").Value = "=i8-h2"<br>************Range("b1").Value = "Difference"<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<br>****<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Yeap it works. Thx. One last favour please, can you show me an example how to use And in a macro in my problem?. For example if A4 and b4 is bigger than zero, then C4 equals 1000

 

[repairman615]

Sure,

Here is AND, OR is along the same lines. Also this uses greater than or equal to.

<font face=Courier New>        <SPAN style="color:#00007F">If</SPAN> Range("a2").Value > 0 And Range("B4").Value >= 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("B2").Value = ""<br>            Range("B1").Value = ""<br>        <SPAN style="color:#00007F">Else</SPAN><br>            Range("b2").Value = "=i8-h2"<br>            Range("b1").Value = "Difference"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[repairman615]

I posted too soon.

Like this:

If Range("a4").Value > 0 And Range("B4").Value >= 0 Then
Range("C4").Value = 1000
Else
Range("C4").Value = ""
End If

 

[brooklyn2007]

I posted too soon.

Like this:

If Range("a4").Value > 0 And Range("B4").Value >= 0 Then
Range("C4").Value = 1000
Else
Range("C4").Value = ""
End If

Thx a lot for your time