search code tweak

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,211
Office Version
  1. 2016
Platform
  1. Windows
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

Banned User
Joined
Apr 5, 2021
Messages
2,030
Office Version
  1. 2010
Platform
  1. Windows
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​
VBA Code:
        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

Well-known Member
Joined
Nov 26, 2020
Messages
2,211
Office Version
  1. 2016
Platform
  1. Windows
Hello !​

Can't work as if Found is nothing then can't apply the offset !​
It should be​
VBA Code:
        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

Well-known Member
Joined
Nov 26, 2020
Messages
2,211
Office Version
  1. 2016
Platform
  1. Windows
opps mistake
 

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
Hello !​

Can't work as if Found is nothing then can't apply the offset !​
It should be​
VBA Code:
        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

Banned User
Joined
Apr 5, 2021
Messages
2,030
Office Version
  1. 2010
Platform
  1. Windows
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) …​
 

Forum statistics

Threads
1,176,122
Messages
5,901,507
Members
434,898
Latest member
dez091

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top