(vba) if then

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
968
Office Version
  1. 2010
Platform
  1. Windows
Hello everyone.
I am trying to add two columns, A2:A34 result in C2, B2:B34 result in D2 and C2/D2 in F2. my problem now is that SOMETIMES I don't need to input values on A2:A34 and I just want the results of column B, so I write this code, but I don't figure It out the condition for empty column, here is my code.
HTML:
Private Sub CommandButton1_Click()

    Range("C2").Value = WorksheetFunction.Sum(Range("A2:A34"))
    Range("D2").Value = WorksheetFunction.Sum(Range("B2:B34"))
    Range("F2").Value = Range("D2").Value / Range("C2").Value
    If Range("A2:A34") = 0 Then
    Range("C2") = 0
    End If
    
    If Range("F2") > 4.8 Then
    MsgBox "your rick is to hight, be carefull if there are more than 25 rolls"
    End If
    End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
If your C2 is 0, then your F2 will have a #DIV/0 error. So would you want to say:

Code:
If (Range("C2").Value = 0) Then
  Range("F2").Value = Range("D2").Value
Else
  Range("F2").Value = Range("D2").Value / Range("C2").Value
End If
 
Upvote 0

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
968
Office Version
  1. 2010
Platform
  1. Windows
thanks Iliace, sometimes I don't use A2:A34 and I just want the sum of B, so I am looking for how to make this condition possible, if A2:A34 is empty or = 0 then just sum B2:B34 instead to get error messages.
 
Upvote 0

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
That's what the code was intended to do. Di it work?
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,831
Office Version
  1. 365
Platform
  1. Windows
To clarify, I think iliace's code is intended to replace more than just your first If...Then block. So
Rich (BB code):
Range("C2").Value = WorksheetFunction.Sum(Range("A2:A34"))
Range("D2").Value = WorksheetFunction.Sum(Range("B2:B34"))
If (Range("C2").Value = 0) Then
  Range("F2").Value = Range("D2").Value
Else
  Range("F2").Value = Range("D2").Value / Range("C2").Value
End If

If Range("F2") > 4.8 Then
  MsgBox "your rick is to hight, be carefull if there are more than 25 rolls"
End If
.. or possibly, instead of the blue line you might want
Rich (BB code):
Range("F2").ClearContents
 
Upvote 0

Forum statistics

Threads
1,195,672
Messages
6,011,084
Members
441,581
Latest member
rp4717

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