Conditional Testing Lingo (VBA)

[lee2smooth03]

Am I using the right test?

I'm using Range.Rows.Find() to search for a value in the row while looping through a named range. When the code arrives at a row, I want to check to see if an input value is present in the row...the caveat here is that the "Find" function returns a range (the cell) that contains the value...if it's there.

For the purpose of this function, I don't care what the return cell is, I just want to know if the value is present in the row.The IsNull function was used to measure this condition, but I don't think it's working properly because the two variables that were supposed to count the condition keep returning the same value...and they shouldn't

Below is a snippet of my code:

            If IsNull(theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)) Then
                counter = counter + 1
            Else
                itsHere = itsHere + 1
                counter = counter + 1
            End If

In the conditional statement, how can we check to if the value is in the range/or not in the range?

Thanks for any help

 

[VoG]

That would be something like

Dim Found As Range
Set Found = theData.Find(Item, after:=theData.Cells(RowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
If Found Is Nothing Then
    counter = counter + 1
Else
    itsHere = itsHere + 1
    counter = counter + 1
    Set Found = Nothing
End If

 

[lee2smooth03]

Thanks for the speedy reply.

After running this code, the original "counter" variable seems to be working properly, but "itsHere" does not. Maybe I should define the Else statement in case something is there.

            Set foundIt = theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
            'If IsNull(theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)) Then
            If foundIt Is Nothing Then
                counter = counter + 1
            Else if Not Is Nothing Then
                itsHere = itsHere + 1
                counter = counter + 1
            End If

 

[VoG]

That shouldn't be necessary but the syntax would be

Set foundit = theData.Rows.Find(Item, after:=theData.Cells(RowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
            'If IsNull(theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)) Then
            If foundit Is Nothing Then
                counter = counter + 1
            ElseIf Not foundit Is Nothing Then
                itsHere = itsHere + 1
                counter = counter + 1
            End If

 

[lee2smooth03]

I know it shouldn't be, but I'm still baffled. Below I'll post the entire function. In the variable initialization, "counter" and "itsHere" both start off with values of 0

Inside of the loop, formats will be set on two string variables, then both of the formatted variables are sent to be compared in a custom function that searches for identical numbers. The returning value is then used in a Case Select statement.

This IF statement is only called if the custom function returns a value of 0. On a separate post I asked for help on a Range.Rows.Find() method to look for a value.

Something must be wrong with the way that I declared the .Find method because counting integers is too easy. The output that I keep getting at the bottom of the function returns values that are equivalent at every step of the loop and that should not be.

Here's the function:

Function countByDay(theData As Range, item As Integer) As Integer

Dim rowCount As Integer
Dim colCount As Integer

Dim counter As Integer
Dim itsHere As Integer

Dim thisDay As String

Dim subData As Range
Dim foundIt As Range

counter = 0
itsHere = 0
thisDay = Format(Now, "mm/dd/yyyy")

For rowCount = 1 To theData.CurrentRegion.Rows.Count
    'Cells(rowCount, 1).Value = rowCount + item              'test procedure that validates the loop
    'Cells(rowCount, 2).Value = theData.Cells(rowCount, 1)   'test procedure that validates the loop
    'Cells(rowCount, 2).Value = Application.WorksheetFunction.Text(theData.Cells(rowCount, 1), "mm/dd/yyyy")
    
    With Cells(rowCount, 2)
        .Value = theData.Cells(rowCount, 1)
        .NumberFormat = "mm/dd/yyyy"
    End With
    
    With Cells(rowCount, 3)
        .Value = Now
        .NumberFormat = "mm/dd/yyyy"
    End With
          
    Select Case combText(theData.Cells(rowCount, 1), thisDay)   'combText is a function that compares strings
        Case -1, 1                                              'these two values indicate "No Match"
            counter = counter + 0                               'counting the mismatches
        Case 0
            'subData was declared as an array at the beginning of the funciton
            'every time the loop is run, subset is supposed to become the row that had matching string values
            'the program doesn't seem to have a problem with subData, but it seems redundant
            
            'Set subData = theData.Range(theData.Cells(rowCount, 1), theData.Cells(rowCount, 1).End(xlRight))
            
            theData.Cells(rowCount, 1).Interior.Color = RGB(100, 200, 150)  'verifies the function test
            'counter = theData.Columns.Count                                'more verification
            
            'at this point I'd like to assign some variable to be the number of columns in theData
            'there is supposed to be a loop that traverses this row to search for the item
            Set foundIt = theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
            
            If foundIt Is Nothing Then
                counter = counter + 1
            ElseIf Not foundIt Is Nothing Then
                counter = counter + 1
                itsHere = itsHere + 1
            End If
                 
    End Select
            
     Cells(rowCount, 4).Value = counter
     Cells(rowCount, 5).Value = itsHere
    
Next rowCount

End Function

...almost there...

 

[lee2smooth03]

I know it shouldn't be, but I'm still baffled. Below I'll post the entire function. In the variable initialization, "counter" and "itsHere" both start off with values of 0

Inside of the loop, formats will be set on two string variables, then both of the formatted variables are sent to be compared in a custom function that searches for identical numbers. The returning value is then used in a Case Select statement.

This IF statement is only called if the custom function returns a value of 0. On a separate post I asked for help on a Range.Rows.Find() method to look for a value.

Something must be wrong with the way that I declared the .Find method because counting integers is too easy. The output that I keep getting at the bottom of the function returns values that are equivalent at every step of the loop and that should not be.

Here's the function:

Function countByDay(theData As Range, item As Integer) As Integer

Dim rowCount As Integer
Dim colCount As Integer

Dim counter As Integer
Dim itsHere As Integer

Dim thisDay As String

Dim subData As Range
Dim foundIt As Range

counter = 0
itsHere = 0
thisDay = Format(Now, "mm/dd/yyyy")

For rowCount = 1 To theData.CurrentRegion.Rows.Count
    'Cells(rowCount, 1).Value = rowCount + item              'test procedure that validates the loop
    'Cells(rowCount, 2).Value = theData.Cells(rowCount, 1)   'test procedure that validates the loop
    'Cells(rowCount, 2).Value = Application.WorksheetFunction.Text(theData.Cells(rowCount, 1), "mm/dd/yyyy")
    
    With Cells(rowCount, 2)
        .Value = theData.Cells(rowCount, 1)
        .NumberFormat = "mm/dd/yyyy"
    End With
    
    With Cells(rowCount, 3)
        .Value = Now
        .NumberFormat = "mm/dd/yyyy"
    End With
          
    Select Case combText(theData.Cells(rowCount, 1), thisDay)   'combText is a function that compares strings
        Case -1, 1                                              'these two values indicate "No Match"
            counter = counter + 0                               'counting the mismatches
        Case 0
            'subData was declared as an array at the beginning of the funciton
            'every time the loop is run, subset is supposed to become the row that had matching string values
            'the program doesn't seem to have a problem with subData, but it seems redundant
            
            'Set subData = theData.Range(theData.Cells(rowCount, 1), theData.Cells(rowCount, 1).End(xlRight))
            
            theData.Cells(rowCount, 1).Interior.Color = RGB(100, 200, 150)  'verifies the function test
            'counter = theData.Columns.Count                                'more verification
            
            'at this point I'd like to assign some variable to be the number of columns in theData
            'there is supposed to be a loop that traverses this row to search for the item
            Set foundIt = theData.Rows.Find(item, after:=theData.Cells(rowCount, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
            
            If foundIt Is Nothing Then
                counter = counter + 1
            ElseIf Not foundIt Is Nothing Then
                counter = counter + 1
                itsHere = itsHere + 1
            End If
                 
    End Select
            
     Cells(rowCount, 4).Value = counter
     Cells(rowCount, 5).Value = itsHere
    
Next rowCount

End Function

...almost there...

By the way, that post is here and not the one that's up in the text...sorry for the confusion!

post = 596355

 

[lee2smooth03]

okay, wrong again...lets try thread!
t=596355
t=596355
Back Over Here

 

[Norie]

Why do you have ElseIf?

It's a not needed, you either find what you are looking for or you don't find it.

So you only need to check one condition.

Also, what's on the purpose of this?

counter = counter + 0

What is the purpose of the code anyway?

What are you trying to count?

 

[lee2smooth03]

Norie,

the "count + 0" line is a dummy line. The code is not fully developed and I wanted to use a placeholder instead of using an "Else" statement in case select.

The "counter" variable is counting the number of times that the function combText is true; its value should be increased under each condition (when the item is found or not found).

The next variable "itsHere" should only count the number of times the item is found. The ElseIf statement is redundant, but I cannot figure out why "itsHere" always equals "counter" when they cycle under different conditions.

Any suggestions?

 

[Norie]

Where are you using this code?

Is it on a worksheet or just being called from other code?

What does the function combText do exactly?

Is it checking for combinations?

Perhaps if you posted some sampel data it migh clarify things.