Invalid qualifier

J

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
786
Office Version
  1. 365
Hi,

have a form trying adapt this code suggested in this site to my userform to be able to edit text boxes to update spreadsheet but when I click update get the compile error "invalid or unqualified reference"

here is the code:

VBA Code: 
Private Sub CommandButton1_Click()
Dim cll As Range

    ' Find the selected list item value in the data range
    Set cll = rng.Columns(1).Find(COGSform.Value, rng.Cells(1, 1), xlValues)
    ' First column is the lookup range, so we don't update it but the others
    
    COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
                COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
                COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
                COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
                COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
                COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
                COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
                COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
                COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
                COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
                COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
                COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
                COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
                COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
                COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).Value
    
    'cll.Cells(, 2).Value = TextBox2.Value
    'cll.Cells(, 3).Value = TextBox3.Value
End Sub

Thank you,
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Its where you are using .Cells and .Range. They need to be within a With statement.
 
Upvote 0
thank you.

I am not that good in VBA changed it to: but now getting error sub or function no defined (run-time error 424 object required) and highlight this line:

VBA Code: 
Set rng = rng.Columns(1).Find(COGSform.Value, rng.Cells(1, 1), xlValues)

Complete code (with the changes)

VBA Code: 
Private Sub CommandButton1_Click()
Dim cll As Range

    ' Find the selected list item value in the data range
    Set rng = rng.Columns(1).Find(COGSform.Value, rng.Cells(1, 1), xlValues)
    ' First column is the lookup range, so we don't update it but the others
    
    
    
  cll.Cells(, 2).Value = TextBox_Results1.Value
  cll.Cells(, 3).Value = TextBox_Results2.Value
  cll.Cells(, 13).Value = TextBox_Results3.Value
  cll.Cells(, 10).Value = TextBox_Results4.Value
  cll.Cells(, 14).Value = TextBox_Results5.Value
  cll.Cells(, 4).Value = TextBox_Results6.Value
  cll.Cells(, 5).Value = TextBox_Results8.Value
  cll.Cells(, 6).Value = TextBox_Results9.Value
  cll.Cells(, 11).Value = TextBox_Results10.Value
  cll.Cells(, 12).Value = TextBox_Results11.Value
  cll.Cells(, 8).Value = TextBox_Results12.Value
  cll.Cells(, 15).Value = TextBox_Results13.Value
  cll.Cells(, 16).Value = TextBox_Results14.Value
  cll.Cells(, 9).Value = TextBox_Results15.Value
  cll.Cells(, 7).Value = TextBox_Results16.Value
  cll.Cells(, 1).Value = TextBox_Results17.Value
  
  
   'COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
   ' COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
   ' COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
   ' COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
   ' COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
   ' COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
   ' COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
   ' COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
   ' COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
   ' COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
   ' COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
   ' COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
   ' COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
   ' COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
   ' COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
   ' COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).V
End Sub

could you please help no sure what to change now.
 
Upvote 0
I assume you assigned rng somewhere else and you probably meant:

Code: 
Set cll = rng.Columns(1).Find(COGSform.Value, rng.Cells(1, 1), xlValues)
 
Last edited:
Upvote 0
Where did you assign a range to the rng variable?
 
Upvote 0
To be honest no sure,

Maybe posting the whole code if for the userform will help,


VBA Code: 
Option Explicit
Dim rng As Range



'------------------------     ExcelCampus.com     ------------------------
'Find All User Form
'
'by Jon Acampora, jon@excelcampus.com
'
'Description: The form uses the the FindAll function by Chip Pearson
'             to find and return results to a listbox as the user types.
'             The user can click on a result to go to the cell listed in
'             the results.
'
'Date: 03/19/2013
'
'-------------------------------------------------------------------------

Private Sub TextBox_Find_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Calls the FindAllMatches routine as user types text in the textbox

    Call FindAllMatches
    
End Sub

Private Sub Label_ClearFind_Click()
'Clears the find text box and sets focus

    Me.TextBox_Find.Text = ""
    Me.TextBox_Find.SetFocus
    
End Sub

Sub FindAllMatches()
'Find all matches on activesheet
'Called by: TextBox_Find_KeyUp event

Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim arrResults() As Variant
Dim lFound As Long
Dim lSearchCol As Long
Dim lLastRow As Long
Dim cll As Range
   
    If Len(COGSform_All.TextBox_Find.Value) > 1 Then 'Do search if text in find box is longer than 1 character.
        
        Set SearchRange = ActiveSheet.UsedRange.Cells
        
        FindWhat = COGSform_All.TextBox_Find.Value
        'Calls the FindAll function
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
            ReDim arrResults(1 To 1, 1 To 2)
            arrResults(1, 1) = "No Results"
        Else
            'Add results of FindAll to an array
            ReDim arrResults(1 To FoundCells.Count, 1 To 2)
            lFound = 1
            For Each FoundCell In FoundCells
                arrResults(lFound, 1) = FoundCell.Value
                arrResults(lFound, 2) = FoundCell.Address
                lFound = lFound + 1
            Next FoundCell
        End If
        
        'Populate the listbox with the array
        Me.ListBox_Results.List = arrResults
        
    Else
        Me.ListBox_Results.Clear
    End If
        
End Sub

Private Sub ListBox_Results_Click()
'Go to selection on sheet when result is clicked

Dim strAddress As String
Dim l As Long

    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
            
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
                COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
                COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
                COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
                COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
                COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
                COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
                COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
                COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
                COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
                COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
                COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
                COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
                COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
                COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).Value
            End With
            GoTo EndLoop
        End If
    Next l

EndLoop:
    
End Sub

Private Sub CommandButton_Close_Click()
'Close the userform

    Unload Me
    
End Sub




Private Sub UserForm_Initialize()
'PURPOSE: Position userform to center of Excel Window (important for dual monitor compatibility)
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

'Start Userform Centered inside Excel Screen (for dual monitors)
  Me.StartUpPosition = 0
  Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
  Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)

End Sub

Private Sub CommandButton1_Click()
Dim cll As Range

    ' Find the selected list item value in the data range
    Set cll = rng.Columns(1).Find(COGSform.Value, rng.Cells(1, 1), xlValues)
        ' First column is the lookup range, so we don't update it but the others
    
                
  cll.Cells(, 2).Value = TextBox_Results1.Value
  cll.Cells(, 3).Value = TextBox_Results2.Value
  cll.Cells(, 13).Value = TextBox_Results3.Value
  cll.Cells(, 10).Value = TextBox_Results4.Value
  cll.Cells(, 14).Value = TextBox_Results5.Value
  cll.Cells(, 4).Value = TextBox_Results6.Value
  cll.Cells(, 5).Value = TextBox_Results8.Value
  cll.Cells(, 6).Value = TextBox_Results9.Value
  cll.Cells(, 11).Value = TextBox_Results10.Value
  cll.Cells(, 12).Value = TextBox_Results11.Value
  cll.Cells(, 8).Value = TextBox_Results12.Value
  cll.Cells(, 15).Value = TextBox_Results13.Value
  cll.Cells(, 16).Value = TextBox_Results14.Value
  cll.Cells(, 9).Value = TextBox_Results15.Value
  cll.Cells(, 7).Value = TextBox_Results16.Value
  cll.Cells(, 1).Value = TextBox_Results17.Value
                       
                
                
  'COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
  'COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
  'COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
  'COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
  'COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
  'COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
  'COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
  'COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
  'COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
  'COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
  'COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
  'COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
  'COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
  'COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
  'COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
  'COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).Value
    
  'cll.Cells(, 2).Value = TextBox2.Value
  'cll.Cells(, 3).Value = TextBox3.Value
End Sub
 
Upvote 0
You haven't assigned it a range. What range are you supposed to be searching?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Textbox Editable
2
Replies
12
Views
997
smozgur
smozgur
J
  • Question Question
Adding a year today total textbox.
Replies
6
Views
844
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top