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

 

[lee2smooth03]

Norie,

right now this function is called while executing a subroutine, but there are plans to use it in the worksheet.

My combText function works well; all it does is compare string values using parse methods (literally combing through the text inputs and comparing values up to a certain index number using LEFT()).

If you have any advice on how to use the .Find(,,,,,) method, I'd really appreciate it.

Thanks

 

[Norie]

I don't actually understand why you want to use find so I'm not sure I could give any pointers.

Can you post some sample data?

That really might help.

 

[lee2smooth03]

I'd rather not post data because of the nature of the content that I'm dealing with here. In our best interest, I can only allude to similar situations.

If you look at the sample code on the previous page, you'll notice two input variables: one is the range that will be searched, and the other is the item that is to be found

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

After the variable declaration and initiation, the code enters a loop that will cycle per number of rows in the named data

For rowCount = 1 To theData.CurrentRegion.Rows.Count

Once inside the loop we format two strings to my preferred specification; VBA sets dates, but they don't use leading zeroes by default, therefore we make it do this on our own.

    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

The two cells that were just formatted are not part of the input range; they only exist as validation of code since I'm still relatively new to VBA- these values are later output onto the page to see if they work as intended.

<Real Code>
The SELECT CASE function calls combText to compare the two strings

Select Case combText(theData.Cells(rowCount, 1), thisDay)   'combText is a function that compares strings

combText uses StrComp to evaluate the similarities in the strings. It's basically comparing the months or the days or the years...depending on how I adjust the code.So far all of this works well; combText returns -1, 0, or 1.

            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

When combText finds a perfect match, the next check is for the second input variable. The .Find(,,,,) statement is looking for integer values on the same row as the matching date. I think that whay you're asking for is a visualization. Imagine a table of grades (integers in this case) with the leftmost column being dates. I need the .Find(,,,,) function to find the grade on the matching row.

 

[shg]

1. If that function is invoked from the worksheet, it can't do anything except return a value to the cell in which it appears.

2. If you do this:

For rowCount = 1 To theData.CurrentRegion.Rows.Count

... Excel will not see any dependency on cells outside theData, and so will not recompute the function if they change.

 

[Norie]

Could you at least give us a clue?

 

[lee2smooth03]

Shg,

I don't mind calling the function more than one time; in my subroutine, there is another function that defines the range if the input data. The numbers in the data set are not expected to change...only the size of the matrix.

Could you elaborate on your comment about dependency?

You said that Excel will not have dependency on cells outside of theData...what exactly do you mean?

 

[shg]

Could you elaborate on your comment about dependency?

It's not relevant if the function is not being called from the worksheet.