0

I have code for a command button click event that keeps throwing error 91. When I step through the code it throws the error right after the 'Set findvalue' code executes. See code snippet below. I can't figure it out upon inspection of the code itself.

'findvalue' is dimensioned as a range object. 'DataSH' is the worksheet where the master data table exists beginning with the column for Record ID (column B). The user form elements are set to their respective columns in the data table on DataSH. 

    Private Sub cmdEdit_Click()
    'declare the variables
    Dim findvalue As Range
    Dim cNum As Integer
    Dim DataSH As Worksheet
    'error handling
    On Error GoTo errHandler:
    'hold in memory and stop screen flicker
    Application.ScreenUpdating = False
    Set DataSH = Sheet1

The click event sub routine that this snippet is taken from is setup to update the appropriate column data on the row for the selected Record ID (row number identifier) in DataSH based on the value in those UF elements (i.e. changed or deleted values). The point is to update the master data table on DataSH with any changes made in the UF elements (text boxes and combo boxes).

    Set findvalue = DataSH.Range("B:B"). _
    Find(What:=Me.tbRecID.Value, LookIn:=xlValues, LookAt:=xlWhole)
    'update the values
    findvalue = tbRecID.Value
    findvalue.Offset(0, 1) = tbRecDate.Value
    findvalue.Offset(0, 2) = cmbRecLoc.Value
    findvalue.Offset(0, 3) = cmbRecCust.Value
    findvalue.Offset(0, 4) = tbRecAmt.Value
    findvalue.Offset(0, 5) = cmbRecComm.Value
    findvalue.Offset(0, 6) = cmbRecPrin.Value
    findvalue.Offset(0, 7) = tbRecTerr.Value
    findvalue.Offset(0, 8) = tbRecRep.Value
Improve this question
4
  • 1
    You set findvalue using the Set statement, and then tried to just assign it another value. Commented Nov 30, 2016 at 15:37
  • 1
    @Rdster, but that shouldn't raise any error since it's just confirming current "findvalue" value Commented Nov 30, 2016 at 15:51
  • @Rdster The text box and combo box elements are populated once a record is double clicked (a separate subroutine) in a list box (pulled from the data table on DataSH) on the same UF. 'findvalue' is set to the Record ID that is populated in the tbRecID text box element upon that event. The rest of the code is supposed to return the value from the UF elements (namely those changed) to the appropriated field in the DataSH table (and by extension the list box) using the 'findvalue' object as a reference point. At least I think I explained that correctly. Commented Nov 30, 2016 at 16:04
  • Then why bother with the Find? If you are just going to assign the value of the textbox...The Find does nothing. But one of the answers posted is most likely what you need. Commented Nov 30, 2016 at 16:07

3 Answers 3

Reset to default
1

I think DataSH is not setted. Is "Sheet1" a name of a sheet or is it a worksheet variable? if it is a name then you must set DataSH like this :

Set DataSH = thisworkbook.worksheets("Sheet1")

and not Set DataSH = Sheet1

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

4 Comments

That would raise an error right from "Set DataSH = Sheet1" statement
No, it works as he has it. As long as the Data sheet is Sheet1. One is the Name of the sheet in the properties window...the other is the (Name).
@user3598756 That's the ticket! I was setting it to the Excel default Sheet1 name. I have been doing that without any previous problems just in case the sheet name got changed. However, when I set it to the actual sheet name like in your solution, works like a charm. Problem solved. Many thanks1!
@Rdster I was thinking the same thing, but after changing it, the subroutine now runs as expected. Scratching my head on this one.
0

Error 91 pops up (in this situation), because you are trying to assign value to some range that have not been set, so your Me.tbRecID.Value doesn't exist in DataSH.Range("B:B"). To avoid this problem, you can add some error exception, for example:

    Set findvalue = DataSH.Range("B:B"). _
    Find(What:=Me.tbRecID.Value, LookIn:=xlValues, LookAt:=xlWhole)
    'update the values
On Error GoTo ErrHand:
    findvalue = tbRecID.Value
On Error GoTo 0
    findvalue.Offset(0, 1) = tbRecDate.Value
    findvalue.Offset(0, 2) = cmbRecLoc.Value
    findvalue.Offset(0, 3) = cmbRecCust.Value
    findvalue.Offset(0, 4) = tbRecAmt.Value
    findvalue.Offset(0, 5) = cmbRecComm.Value
    findvalue.Offset(0, 6) = cmbRecPrin.Value
    findvalue.Offset(0, 7) = tbRecTerr.Value
    findvalue.Offset(0, 8) = tbRecRep.Value
ErrHand:
[rest of your code]

This will force program to bypass lines which uses findvalue if there is no range assigned to this variable.

Improve this answer

Comments

0

Your code is simply failing at finding "Me.tbRecID.Value" in "DataSH.Range("B:B")"

So wrap your code in a "If Then End If" to be executed if findvalue has been actually set to a valid Range

Set findvalue = DataSH.Range("B:B"). _
Find(What:=Me.tbRecID.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not findvalue Is Nothing Then
    findvalue.Offset(0, 1) = tbRecDate.Value
    findvalue.Offset(0, 2) = cmbRecLoc.Value
    findvalue.Offset(0, 3) = cmbRecCust.Value
    findvalue.Offset(0, 4) = tbRecAmt.Value
    findvalue.Offset(0, 5) = cmbRecComm.Value
    findvalue.Offset(0, 6) = cmbRecPrin.Value
    findvalue.Offset(0, 7) = tbRecTerr.Value
    findvalue.Offset(0, 8) = tbRecRep.Value
End If
Improve this answer

1 Comment

If my answer solved your question then you may want to mark it as accepet. Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.