search code tweak

[rickblunt]

Hello, I use the code below to find a row that has a specific value (as given in "DeviceId")and then pass information to that row. It works fine because there is only one occurrence of that value in the worksheet in the workbooks that I have been using it in; But now I would like to tweak the code for new workbook. The problem is that this new workbook could have that value in DeviceID multiple times. However the column that is 3 columns to the right of this value will be blank only one time, and this would be the row where I would want to pass that information. So I am thinking that I could use this blank cell as way of finding that row. In other words, the value in DeviceId And the blank cell 3 columns to the right is the target row.

I inserted the syntax (And Found.Offset(0, 3).Value = "") to the code below in an attempt to get that blank cell into the code but I am getting an object required error so I have it improperly worded, or improperly used completely.

I am still learning all of this from you all, and I know I have so much more to learn. I am guessing that I cannot apply an offset to a variable, at least not the way I did it. Can you offer any input on the way I should be doing this? As always I really value all of the help I get from all of you.

Dim Found As Range

Set Found = Sheets("Repair Log").Range("A:A").Find(What:=Me.DeviceId.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False) _
                                                      
        If Found And Found.Offset(0, 3).Value = "" Is Nothing Then
            MsgBox "No match for " & Me.DeviceId.Value, , "No Match Found"
        Else
            Found.Offset(0, 2).Value = Me.RepairComments.Value
            
       
     End If

 

[Zot]

Try change
If Found And Found.Offset(0, 3).Value = "" Is Nothing Then

to
If Found Is Nothing And Found.Offset(0, 3).Value = "" Then

 

[Marc L]

Hello !​

to
If Found Is Nothing And Found.Offset(0, 3).Value = "" Then

Can't work as if Found is nothing then can't apply the offset !​

​

It should be​

        Dim Found As Range
        Set Found = Range("'Repair Log'!A:A").Find(DeviceId.Value, , xlValues, xlWhole)
    If Found Is Nothing Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    ElseIf Found(1, 4).Text = "" Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    Else
        Found(1, 3).Value2 = RepairComments.Value
    End If

 

[Zot]

Hello !​

Can't work as if Found is nothing then can't apply the offset !​

​

It should be​

        Dim Found As Range
        Set Found = Range("'Repair Log'!A:A").Find(DeviceId.Value, , xlValues, xlWhole)
    If Found Is Nothing Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    ElseIf Found(1, 4).Text = "" Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    Else
        Found(1, 3).Value2 = RepairComments.Value
    End If

? I was looking at the syntax but not the logic

 

[Zot]

opps mistake

 

[rickblunt]

Hi Zot and Mark, thanks for the help and I am still wrapping my noggin around your code I get what you are saying, and I understand it - just need to make it intuitive. Your code works great of course, but it also uncovered that my value in the DeviceId listbox is going to null which naturally is returning the no match msgbox. I am at a loss for that as the userform which contains that listbox is still open, so I would not think that the listbox is clearing its value. I need to figure out why that is happening and how to hold the value in the listbox for the entirety of routine.

I appreciate the learning and the assistance - thanks

 

[rickblunt]

Hello !​

Can't work as if Found is nothing then can't apply the offset !​

​

It should be​

        Dim Found As Range
        Set Found = Range("'Repair Log'!A:A").Find(DeviceId.Value, , xlValues, xlWhole)
    If Found Is Nothing Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    ElseIf Found(1, 4).Text = "" Then
        MsgBox "No match for " & DeviceId.Value, , "No Match Found"
    Else
        Found(1, 3).Value2 = RepairComments.Value
    End If

Hi Marc L (and Zot) so instead of trying to "see" both cells at the same time, you just cycle from one to the next and then on. Got it - thanks for the knowledge

 

[Marc L]

​

The issue is when nothing is found so trying to apply an offset on a non existing cell has no sense​

as can see the second cell only if the first cell obviously exists (is found) …​