Invalid or Unqualified Reference

**dcompagnone** · 08-13-2010, 05:38 AM

Hi Professionals,

I'm getting the above message when I click cmdUpdateAccount event.

It's highlighting the line 'n = Application.Match(Me.ComboBox1.Value, .Range("A:A"), 0)'

Full code is below incase it's something else in it that may cause a problem.

Basically, the combo box calls the data into the userform and the user can may the relevant changes to the account detail and then 'update' it.

Code in two parts below:

Help appreciated!

Dom

Private Sub AccountCancel_Click()
Unload Me
Unload UserForm1
ActiveSheet.Protect Password:="musician"
End Sub

Private Sub AccountOK_Click()

Range("C5").Value = Me.TextHQAcc.Value
Range("E9").Value = Me.TextHQ1.Value
Range("E10").Value = Me.TextHQ2.Value
Range("E11").Value = Me.TextHQ3.Value
Range("E12").Value = Me.TextHQ4.Value
Range("E13").Value = Me.TextHQTown.Value
Range("E14").Value = Me.TextHQCounty.Value
Range("E15").Value = Me.TextHQPCode.Value
Range("E17").Value = Me.TextHQTel.Value
Range("E19").Value = Me.TextHQFax.Value
Range("E21").Value = Me.TextHQWeb.Value
Range("E23").Value = Me.TextHQEmail.Value
Range("M9").Value = Me.TextT1.Value
Range("M11").Value = Me.TextT2.Value
Range("M13").Value = Me.TextT3.Value
Range("M15").Value = Me.TextT4.Value
Range("M17").Value = Me.TextT5.Value
Range("M19").Value = Me.TextT6.Value
Range("P9").Value = Me.TextN1.Value
Range("P11").Value = Me.TextN2.Value
Range("P13").Value = Me.TextN3.Value
Range("P15").Value = Me.TextN4.Value
Range("P17").Value = Me.TextN5.Value
Range("P19").Value = Me.TextN6.Value
Range("W9").Value = Me.TextPA1.Value
Range("W11").Value = Me.TextPA2.Value
Range("W13").Value = Me.TextPA3.Value
Range("W15").Value = Me.TextPA4.Value
Range("W17").Value = Me.TextPA5.Value
Range("W19").Value = Me.TextPA6.Value
Range("P23").Value = Me.TextBeds.Value
Range("C32").Value = Me.TextNotesInfo.Value
Range("C5").Value = UCase(Range("C5").Value)

MsgBox "You have successfully updated the account information!" & vbCrLf & "", vbInformation, "Account Information"

Unload Me
Unload UserForm1
ActiveSheet.Protect Password:="musician"

End Sub


Private Sub cmdAddNew_Click()
Range("C5").Value = Me.TextHQAcc.Value
Range("E8").Value = Me.TextHQ1.Value
Range("E9").Value = Me.TextHQ2.Value
Range("E10").Value = Me.TextHQ3.Value
Range("E11").Value = Me.TextHQ4.Value
Range("E12").Value = Me.TextHQTown.Value
Range("E13").Value = Me.TextHQCounty.Value
Range("E14").Value = Me.TextHQPCode.Value
Range("J14").Value = Me.TextHQCountry.Value
Range("E16").Value = Me.TextHQTel.Value
Range("E18").Value = Me.TextHQFax.Value
Range("E20").Value = Me.TextHQWeb.Value
Range("E22").Value = Me.TextHQEmail.Value
Range("M8").Value = Me.TextT1.Value
Range("M10").Value = Me.TextT2.Value
Range("M12").Value = Me.TextT3.Value
Range("M14").Value = Me.TextT4.Value
Range("M16").Value = Me.TextT5.Value
Range("M18").Value = Me.TextT6.Value
Range("P8").Value = Me.TextN1.Value
Range("P10").Value = Me.TextN2.Value
Range("P12").Value = Me.TextN3.Value
Range("P14").Value = Me.TextN4.Value
Range("P16").Value = Me.TextN5.Value
Range("P18").Value = Me.TextN6.Value
Range("W8").Value = Me.TextPA1.Value
Range("W10").Value = Me.TextPA2.Value
Range("W12").Value = Me.TextPA3.Value
Range("W14").Value = Me.TextPA4.Value
Range("W16").Value = Me.TextPA5.Value
Range("W18").Value = Me.TextPA6.Value
Range("P22").Value = Me.TextBeds.Value
Range("W22").Value = Me.TextSHA.Value
Range("C48").Value = Me.TextNotesInfo.Value
Range("C5").Value = UCase(Range("C5").Value)

OptionUnknown = True
TextNotesInfo.SetFocus

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Accounts") 'find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.TextHQAcc.Value
ws.Cells(iRow, 2).Value = Me.TextHQ1.Value
ws.Cells(iRow, 3).Value = Me.TextHQ2.Value
ws.Cells(iRow, 4).Value = Me.TextHQ4.Value
ws.Cells(iRow, 5).Value = Me.TextHQTown.Value
ws.Cells(iRow, 6).Value = Me.TextHQCounty.Value
ws.Cells(iRow, 7).Value = Me.TextHQPCode.Value
ws.Cells(iRow, 8).Value = Me.TextHQCountry.Value
ws.Cells(iRow, 9).Value = Me.TextHQTel.Value
ws.Cells(iRow, 10).Value = Me.TextHQFax.Value
ws.Cells(iRow, 11).Value = Me.TextHQWeb.Value
ws.Cells(iRow, 12).Value = Me.TextHQEmail.Value
ws.Cells(iRow, 13).Value = Me.TextT1.Value
ws.Cells(iRow, 14).Value = Me.TextN1.Value
ws.Cells(iRow, 15).Value = Me.TextPA1.Value
ws.Cells(iRow, 16).Value = Me.TextT2.Value
ws.Cells(iRow, 17).Value = Me.TextN2.Value
ws.Cells(iRow, 18).Value = Me.TextPA2.Value
ws.Cells(iRow, 19).Value = Me.TextT3.Value
ws.Cells(iRow, 20).Value = Me.TextN3.Value
ws.Cells(iRow, 21).Value = Me.TextPA3.Value
ws.Cells(iRow, 22).Value = Me.TextT4.Value
ws.Cells(iRow, 23).Value = Me.TextN4.Value
ws.Cells(iRow, 24).Value = Me.TextPA4.Value
ws.Cells(iRow, 25).Value = Me.TextT5.Value
ws.Cells(iRow, 26).Value = Me.TextN5.Value
ws.Cells(iRow, 27).Value = Me.TextPA5.Value
ws.Cells(iRow, 28).Value = Me.TextT6.Value
ws.Cells(iRow, 29).Value = Me.TextN6.Value
ws.Cells(iRow, 30).Value = Me.TextPA6.Value
ws.Cells(iRow, 31).Value = Me.TextBeds.Value
ws.Cells(iRow, 32).Value = Me.TextSHA.Value
ws.Cells(iRow, 33).Value = Me.TextNotesInfo.Value


MsgBox "Are you sure you want to add this Account?" & vbYesNo & vbCrLf & "", vbQuestion, "Are you sure?"

If vbYes Then

MsgBox "The new account was saved!!" & vbCrLf & "", vbInformation, "Account Saved"

End If

Unload Me
Unload UserForm1

Sheets("A1").Protect Password:="musician"
Sheets("Accounts").Protect Password:="musician"
End Sub

Private Sub cmdUpdateAccount_Click()

Dim ws As Worksheet
Set ws = Worksheets("Accounts")
        
        n = Application.Match(Me.ComboBox1.Value, .Range("Account"), 0)

        
        ws.Cells(iRow, 1).Value = Me.TextHQAcc.Value
        ws.Cells(iRow, 2).Value = Me.TextHQ1.Value
        ws.Cells(iRow, 3).Value = Me.TextHQ2.Value
        ws.Cells(iRow, 4).Value = Me.TextHQ4.Value
        ws.Cells(iRow, 5).Value = Me.TextHQTown.Value
        ws.Cells(iRow, 6).Value = Me.TextHQCounty.Value
        ws.Cells(iRow, 7).Value = Me.TextHQPCode.Value
        ws.Cells(iRow, 8).Value = Me.TextHQCountry.Value
        ws.Cells(iRow, 9).Value = Me.TextHQTel.Value
        ws.Cells(iRow, 10).Value = Me.TextHQFax.Value
        ws.Cells(iRow, 11).Value = Me.TextHQWeb.Value
        ws.Cells(iRow, 12).Value = Me.TextHQEmail.Value
        ws.Cells(iRow, 13).Value = Me.TextT1.Value
        ws.Cells(iRow, 14).Value = Me.TextN1.Value
        ws.Cells(iRow, 15).Value = Me.TextPA1.Value
        ws.Cells(iRow, 16).Value = Me.TextT2.Value
        ws.Cells(iRow, 17).Value = Me.TextN2.Value
        ws.Cells(iRow, 18).Value = Me.TextPA2.Value
        ws.Cells(iRow, 19).Value = Me.TextT3.Value
        ws.Cells(iRow, 20).Value = Me.TextN3.Value
        ws.Cells(iRow, 21).Value = Me.TextPA3.Value
        ws.Cells(iRow, 22).Value = Me.TextT4.Value
        ws.Cells(iRow, 23).Value = Me.TextN4.Value
        ws.Cells(iRow, 24).Value = Me.TextPA4.Value
        ws.Cells(iRow, 25).Value = Me.TextT5.Value
        ws.Cells(iRow, 26).Value = Me.TextN5.Value
        ws.Cells(iRow, 27).Value = Me.TextPA5.Value
        ws.Cells(iRow, 28).Value = Me.TextT6.Value
        ws.Cells(iRow, 29).Value = Me.TextN6.Value
        ws.Cells(iRow, 30).Value = Me.TextPA6.Value
        ws.Cells(iRow, 31).Value = Me.TextBeds.Value
        ws.Cells(iRow, 32).Value = Me.TextSHA.Value
        ws.Cells(iRow, 33).Value = Me.TextNotesInfo.Value
        
    End With
    
    MsgBox "Your record was updated with the new information"
End Sub

**dcompagnone** · 08-13-2010, 05:39 AM

  Private Sub ComboBox1_Change()
Dim vreg As String, drow As Integer, c As Range

    Let vreg = ComboBox1.Value

    With Sheets("Accounts").Range("A2:A65336")
        Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
             drow = c.Row
        End If
    End With
    
    With Me
        .TextHQAcc.Value = Sheets("Accounts").Cells(drow, 1).Value
        .TextHQ1.Value = Sheets("Accounts").Cells(drow, 2).Value
        .TextHQ2.Value = Sheets("Accounts").Cells(drow, 3).Value
        .TextHQ3.Value = Sheets("Accounts").Cells(drow, 4).Value
        .TextHQ4.Value = Sheets("Accounts").Cells(drow, 5).Value
        .TextHQTown.Value = Sheets("Accounts").Cells(drow, 6).Value
        .TextHQCounty.Value = Sheets("Accounts").Cells(drow, 7).Value
        .TextHQPCode.Value = Sheets("Accounts").Cells(drow, 8).Value
        .TextHQCountry.Value = Sheets("Accounts").Cells(drow, 9).Value
        .TextHQTel.Value = Sheets("Accounts").Cells(drow, 10).Value
        .TextHQFax.Value = Sheets("Accounts").Cells(drow, 11).Value
        .TextHQWeb.Value = Sheets("Accounts").Cells(drow, 12).Value
        .TextHQEmail.Value = Sheets("Accounts").Cells(drow, 13).Value
        .TextT1.Value = Sheets("Accounts").Cells(drow, 14).Value
        .TextN1.Value = Sheets("Accounts").Cells(drow, 15).Value
        .TextPA1.Value = Sheets("Accounts").Cells(drow, 16).Value
        .TextT2.Value = Sheets("Accounts").Cells(drow, 17).Value
        .TextN2.Value = Sheets("Accounts").Cells(drow, 18).Value
        .TextPA2.Value = Sheets("Accounts").Cells(drow, 19).Value
        .TextT3.Value = Sheets("Accounts").Cells(drow, 20).Value
        .TextN3.Value = Sheets("Accounts").Cells(drow, 21).Value
        .TextPA3.Value = Sheets("Accounts").Cells(drow, 22).Value
        .TextT4.Value = Sheets("Accounts").Cells(drow, 23).Value
        .TextN4.Value = Sheets("Accounts").Cells(drow, 24).Value
        .TextPA4.Value = Sheets("Accounts").Cells(drow, 25).Value
        .TextT5.Value = Sheets("Accounts").Cells(drow, 26).Value
        .TextN5.Value = Sheets("Accounts").Cells(drow, 27).Value
        .TextPA5.Value = Sheets("Accounts").Cells(drow, 28).Value
        .TextT6.Value = Sheets("Accounts").Cells(drow, 29).Value
        .TextN6.Value = Sheets("Accounts").Cells(drow, 30).Value
        .TextPA6.Value = Sheets("Accounts").Cells(drow, 31).Value
        .TextBeds.Value = Sheets("Accounts").Cells(drow, 32).Value
        .TextSHA.Value = Sheets("Accounts").Cells(drow, 33).Value
        .TextNotesInfo.Value = Sheets("Accounts").Cells(drow, 34).Value
        End With
End Sub

Private Sub UserForm_Initialize()
'Account_Information.ComboBox1.RowSource = "Account" & Cells(Rows.Count, "A").End(xlUp).Row
    Account_Information.ComboBox1.RowSource = "Account"

End Sub

**royUK** · 08-13-2010, 06:30 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**romperstomper** · 08-13-2010, 06:42 AM

Sorry Roy, didn't see your post before I edited the original.

**romperstomper** · 08-13-2010, 06:43 AM

You haven't used a With... End With statement, so it should be:

n = Application.Match(Me.ComboBox1.Value, ws.Range("Account"), 0)

**dcompagnone** · 08-13-2010, 08:45 AM

oh man!

See it's the small things that you miss when you do it late at night.

thank you.

And thanks for editing my code tags in. I thought I hade done those.

Appreciated

Dom