VBA: Multiple If Conditions

Abdulhaq

New Member
Joined
Feb 1, 2019
Messages
9
Hello Everyone,

I have a query in VBA multiple if conditions.

if you are writing vba code for sum and if any of the column have 0 then it will throw an error.

So, What i am tring to do is writing a vba code where any of the columns have "0" value then it should give "0" in the sum column. and if one column have value and another column have "0" then it should give the output as value in the sum column. and if both columns have value then it should give a sum in sum column.

below is the code it tried. but not sucessful :(

Sub Count_sum()


Dim x As Integer
Dim header1 As Long
Dim header2 As Long


header1 = Sheets("sheet2").range("K" & Rows.Count).End(xlUp).Row
header2 = Sheets("sheet2").range("L" & Rows.Count).End(xlUp).Row
Lastrow2 = Sheets("sheet2").range("J" & Rows.Count).End(xlUp).Row


With ThisWorkbook.Sheets("Sheet2")
For x = 2 To Lastrow2


If header1 = 0 And header2 = 0 Then
.range.Cells(x, "M").Value = 0
ElseIf header1 >= 1 And header2 = 0 Then
.range.Cells(x, "M").Value = .range.Cells(x, "K").Value
ElseIf header1 = 0 And header <= 1 Then
.range.Cells(x, "M").Value = .range.Cells(x, "L").Value
ElseIf header1 >= 1 And header2 >= 1 Then
.range.Cells(x, "M").Value = .range.Cells(x, "K").Value + .range.Cells(x, "L").Value
End If
Next

End With
End Sub



and same is needed to do division also :)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
if you are writing vba code for sum and if any of the column have 0 then it will throw an error.
A 0 should not cause an error for the SUM function (zero is a valid function). And the SUM function will automatically ignore text entries.

What exactly does your data look like, and what exactly are the functions that are returning errors?
 

Abdulhaq

New Member
Joined
Feb 1, 2019
Messages
9
Sum Issue : IF any of the column have "0" value. then sum is giving me "0" in the sum column. It should give me the value which is the problem with sum

i'm unable to provide screenshot.

My data set goes like this :

Column J : Some Text
Column K : header 1 (sum 1)category
Column L : header 2 (sum 2) Category
Column M : sum of Column K + Column L
Column N : Column M / Column K
Column O : Column M / Column L

Cell Format for Column N & O should be in Percentage
This is how calculation is required.


Thanks
Abdul Haq
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
Column M : sum of Column K + Column L
What exactly does this formula look like?
Note that SUM and + do the same thing, so you seldom would use both in the some equation (its usually an "either or" proposition).
Also, where "+" does not like text entries, SUM will just ignore them with no error, so if you change your formula to use SUM and not "+", it may solve your issue.
If you are unsure how to do that, post your current formula here.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,751
Members
417,108
Latest member
Thein Than

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