Best way to use a countif function or best approach to write a logical argument needed - VBA

[kelly mort]

I am stacked with this script and I need help to make it happen.

I have 3 sheets that I want to look at a given column for each of them say column B and count for values greater than zero (0) in the said column.

The code I have currently looks like this:

If Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") >0 And Countif  (sheet 3 col B, ">0") >0 Then
   MyVar = Sum ( sheet 3 col C)
ElseIf Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") >0 And Countif  (sheet 3 col B, ">0") =0 Then
   MyVar = Sum (sheet 2 col C)
ElseIf Countif  (sheet 1 col B, ">0") >0 And Countif  (sheet 2 col B, ">0") = 0 And Countif  (sheet 3 col B, ">0") = 0 Then
   MyVar = Sum (sheet 1 col C)
Else 
   MyVar = Sum (sheet 1 col C)
End If

Then when there was no data on sheet 1 it failed to output the sum from the sheet 2.
I could try and add more conditions to the if function but I want to find out if there are better ways of achieving this before I try making the if conditions complicated again.

Thanks in advance.
(Kelly mort)

 

[Alex Blakenburg]

I don'tknow that it makes sense to have the last ElseIf and then the Else both returning the same value, it means you don't need to the last ElseIf.
I have left it the way it is, see if this works for you.
I am also not sure of what the point is in checking for positive values in Column B but not using a SumIf of positive values in the sum function.

See if this is what you want.

Sub CheckForPositives()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim sh1PosCnt As Long
    Dim sh2PosCnt As Long
    Dim sh3PosCnt As Long
    Dim MyVar As Double
    
    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    Set sh3 = Worksheets("Sheet3")
    
    sh1PosCnt = WorksheetFunction.CountIf(sh1.Range("B:B"), ">0")
    sh2PosCnt = WorksheetFunction.CountIf(sh2.Range("B:B"), ">0")
    sh3PosCnt = WorksheetFunction.CountIf(sh3.Range("B:B"), ">0")
    
    MyVar = 0
    
    If sh1PosCnt > 0 And sh2PosCnt > 0 And sh3PosCnt > 0 Then
        MyVar = WorksheetFunction.Sum(sh3.Range("C:C"))
    ElseIf sh1PosCnt > 0 And sh2PosCnt > 0 And sh3PosCnt = 0 Then
        MyVar = WorksheetFunction.Sum(sh2.Range("C:C"))
    ElseIf sh1PosCnt > 0 And sh2PosCnt = 0 And sh3PosCnt = 0 Then
        MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
    Else
        MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
    End If
    
End Sub

 

[AlphaFrog]

Then when there was no data on sheet 1 it failed to output the sum from the sheet 2.

I didn't understand the statement above.
What are all your conditions? The pattern is not clear from your code.

 

[kelly mort]

@Alex Blakenburg ,
You are very right about the last if condition. I don't actually need it I only placed it there in case some extremes come my way. Lol.

And with the case of using a sumif function against the column C, I am glad you pointed that out. It would help me a lot. Thanks for that tip.

The code you wrote, I have a similar syntax, which is not giving me the desired output.

When I planned the logic, I never anticipated a situation where there would be no data on sheet 1 but have data on sheet 2 and or 3.


During the planning process, I thought there would always be data on sheet 1 before sheet 2 and sheet 3. Those forms of irregularities didn't cross my mind at the time.

When I used data I mean column B. That's when that column is blank, I describe it as the sheet has no data since that's where I am using to verify my data or no data.

And the case I am facing currently is that sheet 1 is blank but sheet 2 has data.

Therefore, none of the conditions were met - the code has no option than to point to the last condition (else) pointing me to the blank sheet (sheet 1).

That is what I need a way around.

In the above scenario (where sheet 1 is blank) I want to check if sheet 2 and 3 are both not blank. If both are not blank, then I want to use sheet 3 else use any of sheets 2 and 3 which is not blank.

 

[kelly mort]

I didn't understand the statement above.
What are all your conditions? The pattern is not clear from your code.

What I want to do is to be able to point to a particular sheet from 3 sheets.

If all 3 sheets have data (value greater than 0 in column B), then I use sheet 3.

If only sheet 1 has data (value greater than 0) in column B, then I use sheet 1.

If both sheets 1 and 3 have data as described before, then I use sheet 3.

Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.

 

[Alex Blakenburg]

Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.

That is a much clearer requirement statement and allows the code to be much simpler.
It still needs a decision on what to do if none of them have positive values in Column B.

Sub UseMostRecentWithPositives()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    Dim sh1PosCnt As Long
    Dim sh2PosCnt As Long
    Dim sh3PosCnt As Long
    Dim MyVar As Double
    
    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    Set sh3 = Worksheets("Sheet3")
    
    sh1PosCnt = WorksheetFunction.CountIf(sh1.Range("B:B"), ">0")
    sh2PosCnt = WorksheetFunction.CountIf(sh2.Range("B:B"), ">0")
    sh3PosCnt = WorksheetFunction.CountIf(sh3.Range("B:B"), ">0")
    
    MyVar = 0
    
    If sh3PosCnt > 0 Then
        MyVar = WorksheetFunction.Sum(sh3.Range("C:C"))
    ElseIf sh2PosCnt > 0 Then
        MyVar = WorksheetFunction.Sum(sh2.Range("C:C"))
    ElseIf sh1PosCnt > 0 Then
        MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
    Else
        MyVar = WorksheetFunction.Sum(sh1.Range("C:C"))
    End If
    
End Sub

 

[kelly mort]

@Alex Blakenburg,

Thanks. It's fixed now.

 

[AlphaFrog]

Using the numbers 1 2 3, one being the oldest and 3 being the newest or latest, my goal is to use the most recent sheet with data (value greater than 0) in column B.

Check the newest first and then the older ones.

    If Application.CountIf(Sheets(3).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(3).Columns("C"))
    ElseIf Application.CountIf(Sheets(2).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(2).Columns("C"))
    ElseIf Application.CountIf(Sheets(1).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(1).Columns("C"))
    Else
        'no data
    End If

 

[kelly mort]

Check the newest first and then the older ones.

    If Application.CountIf(Sheets(3).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(3).Columns("C"))
    ElseIf Application.CountIf(Sheets(2).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(3).Columns("C"))
    ElseIf Application.CountIf(Sheets(1).Columns("B"), ">0") > 0 Then
        MyVar = Application.Sum(Sheets(1).Columns("C"))
    Else
        'no data
    End If

You guys are just amazing.

I appreciate your time.

Have a wonderful time

 

[Alex Blakenburg]

Glad we could help.

In case you need it:-
(sh3 appears twice and you would need to be changed for 2 & 1)

MyVar = WorksheetFunction.SumIfs(sh3.Range("C:C"), sh3.Range("B:B"), ">0")