Initialize Correct Value to Avoid Object variable or With block variable not set

Question

I have block of code where I'm using a For... Next loop to go through an Excel sheet, and tell me if values entered in the text boxes were found or not. I've modified it to work if the values match. Yet, I'm receiving the Object/With Block variable not set error, and it confusing me. I've created the following:

    Dim Value2Find_1 As String = txtMachNumber.Text                      'Serial number value.
    Dim Value2Find_2 As String = txtMachName.Text                        'Machine name value.


    Dim ReplaceWithValue1 As String = ""                                'Replaces the serial number value if found in the sheet.
    Dim ReplaceWithValue2 As String = ""                                'Replaces the machine name value if found in the sheet.
    Dim ReplaceWithValue3 As String = ""                                'Replacement for the date-time in the Date Column.

    Dim Range2Use_1 = xlWS.Range("A1:A4000")                            'Range to span the A Column.
    Dim Range2Use_2 = xlWS.Range("B1:B4000")                            'Range to span the B Column.
    Dim Range2Use_3 = xlWS.Range("F1:F4000")                            'Range to span the F Column.

    Dim xlCell_A = Range2Use_1.Find(txtMachNumber.Text)                  'Looks up the searched serial value in A Column.
    Dim xlCell_B = Range2Use_2.Find(txtMachName.Text)                    'Looks up the searched machine value in B Column.

    Dim LastRow = xlWS.Range("A4000").End(Excel.XlDirection.xlUp).Row + 1
    Dim i As Integer


    With xlWS
        For i = 1 To LastRow
            If Not (Value2Find_1 = txtMachNumber.Text And Value2Find_2 = txtMachName.Text) Then

                MessageBox.Show("No value exists here...")


            Else
                Range2Use_1.Find(What:=Value2Find_1, MatchCase:=True)
                Range2Use_2.Find(What:=Value2Find_2, MatchCase:=True)


                MsgBox("Found both values: " & Value2Find_1 & " and " & Value2Find_2 & " on row " & xlCell_A.Row)
            End If

            Exit Sub
        Next


    End With

If my textbox entries are not in the sheet, the errors returns on the following line of code:

MsgBox("Found both values: " & Value2Find_1 & " and " & Value2Find_2 & " on row " & xlCell_A.Row)

I've narrowed it down to have something to do with the variable that returns the row number of the located textbox entries - xlCell_A. This is where I'm stuck, however. What do I need to set this as in order to avoid the Object/With Block variable not set error? I am afraid I don't know what this is asking for.

M.Doerner · Accepted Answer · 2016-12-29 07:35:38Z

1

I think the problem with your code is that the Find method returns Nothing whenever no match is found, as stated in its documentation. Thus, xlCell_A.Row returns the error because the Row method cannot be called on Nothing.

Actually, I see a number of further issues with your code:

The interior of the for loop does not depend on the loop variable i. Hence, it does exactly the same thing in each interation.
The variable xlWS of the With block is never used, which makes the With block unnessesary.
The return values of the Find methods in the loop never gets assigned to anything. Because of this, they have no effect.
The condition in the if statement always returns False since you never change the values of Value2Find_1 and Value2Find_2 and you initialized them to txtMachNumber.Text and txtMachName.Text, respectively.

If you intend to evaluate whether the values txtMachNumber.Text and txtMachName.Text are present in the column A and B, respectively, you can just test whether xlCell_A and xlCell_B are Nothing.

Since you want to find both on the same row, which using Find does not guarantee, it might be easier to use a loop as in your code but replace txtMachNumber.Text and txtMachName.Text with Range2Use_1.Cells(i,1) and Range2Use_2.Cells(i,1) in the if statement. (This compares the value in the ith row with the values to search for.) Obviously, you would have to exit the loop after finding a match, e.g. using the break statement.

edited Dec 29, 2016 at 7:35

answered Dec 28, 2016 at 19:18

M.Doerner

7224 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DesignerMind Over a year ago

Interestingly I read your advice prior to the edit and setup something, but was running into the issue of substring being returned, if put in part of the cell's text value. I assume using the loop will help me achieve the same result I would get with using Match Exact Cell Contents in the Excel Find function check box? Also, how would you suggest I use xlWS for my With block? I achieve results by checking xlCell A and B were in fact Nothing and tweaking the If statement, but it is unstable in exact searching terms - I need an exact value match or else, say the value isn't present.

M.Doerner Over a year ago

@DesignerMind First of all, the comment about the With block was that it can be deleted without any consequence to what your code does and, hence, should be deleted. Second, if you use Find and check for Nothing there should be no loop. Third, I am not suggesting that you just change the condition in the if statement but that you use a loop with some logic to capture a successful match, e.g. some varable only set to a reasonable value on success, break out of the loop on success and then return the appropriate result.

DesignerMind Over a year ago

Took a swing at this and I'm getting an hresult COM error but don't understand why. I'm using the following

For i = 0 To LastRow             If Range2Use_1.Cells(i, 1).text = Value2Find_1 Then                 MsgBox("You got it...")              Else                 MsgBox("not here..")             End If         Next

I think my problem is with the .Cells portion, but I'm confused as to how to get it to search against my xlCell_A. I know I'm getting an object with .Cells() method, but trying .Value, .Text on the end keeps producing error.

DesignerMind Over a year ago

FYI, I even substituted Value2Find_1 for xlCell_A, as I realize I was searching a string vs an object return, but xlCell_A still results in an error on the Range2Find_1.Cells()

M.Doerner Over a year ago

@DesingnerMind The rows in a range are 1 based. You cannot ask for Cells(0,1). So your For loop cannot start with i=0. Moreover, using a loop to search for the values in the two columns, you do not need xlCell_A at all. Finally, you should use Value or better Value2 to access the value in a cell since the Text property of the range object is very slow.

Collectives™ on Stack Overflow

Initialize Correct Value to Avoid Object variable or With block variable not set

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related