Add One to YES or NO

[NutriMiah]

Hello All,

I am trying to write a simple code to look at a value then find that value in a row of values and if another cell says YES then add one to the YES column and if not then add to the no column. The problem keeps giving me an error and I can't figure it out.

Attached I have put an example of the data and here is my code.

Sub Confidence()
With Sheets("Confidence")

If .Range("k90") = "" Then Exit Sub
If .Range("k91") = "" Then Exit Sub
Set Rng = Sheets("Confidence").Rows(1).Find(Range("k90"), , xlValues, xlWhole).Offset(2)

If .Range("k91") = "Yes" Then Rng.Value = Rng.Value + 1 Else Rng.Offset(, 1) = Rng.Offset(, 1) + 1

End With
End Sub


Sheet Confidence:
Row 1 looks like:
[FONT=&quot]A1: 5.30 b1: blank c1: 5.25 d1: blank e1: 5.2 (and so on down to 0)
Row 2 looks like:
A2: YES B2: NO C2: YES D2: NO and so on
Row 3 looks like:
A3: 4 B3: 1 C3: 7 (they all have numbers under each yes or no)

I can't attach a file.[/FONT]

 

[Joe4]

I tried setting up a sheet like you described, and I get that error when it is looking for a value in K90 that is not found in your list.
What SHOULD happen in that scenario (what do you want to happen)?

 

[NutriMiah]

It should error and say not found on list.

 

[Joe4]

Then just add some error handling code, like this:

Sub Confidence()

    Dim Rng As Range
    
    On Error GoTo err_chk
    With Sheets("Confidence")
        If .Range("k90") = "" Then Exit Sub
        If .Range("k91") = "" Then Exit Sub
        Set Rng = Sheets("Confidence").Rows(1).Find(Range("k90"), , xlValues, xlWhole).Offset(2)
        If .Range("k91") = "Yes" Then Rng.Value = Rng.Value + 1 Else Rng.Offset(, 1) = Rng.Offset(, 1) + 1
    End With
    On Error GoTo 0
    
    Exit Sub
    

err_chk:
    If Err.Number = 91 Then
        MsgBox "Item in cell K90 not found in list", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[NutriMiah]

The two issues I have having is that when K91 says YES it is adding the number to the wrong cell.
Also it isn't finding the number in the row when I know it is there (probably just formatting).

 

[Joe4]

The two issues I have having is that when K91 says YES it is adding the number to the wrong cell.

Please describe an actual detailed example for us, so we can try to recreate it.

Also it isn't finding the number in the row when I know it is there (probably just formatting).

You say that there are calculations going on. Note that changing the format of a cell does NOT change the value.
So, if you had a cell equal to 3.333333333 and formatted it so Excel only shows 3.33, for calculations Excel will still use 3.333333333 (unless you change the "Precision as Displayed" setting).
Also, Excel has a quirk, known as the "floating arithmetic error" (see here: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel)
In either event, rounding all the results you want to compare to the same number of decimal places should resolve that issue.

 

[edmitchell]

Hi - within the VBA code window, can you press 'F8' and execute the code, one line at a time, and confirm which line generates the error message?