'VBA Cells range address syntax (FIND specific value to LastColumn)

I have a question regarding the error I am facing, since I can't seem to figure it out. I have:

With Worksheets("Machine Specification")
  Range("C138", .Cells(Cells.Find("Norm. Current (A)").Row, LastColumn)).Value = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text
End With

Which I need to change to one that instead of C138 will find the row based on it's value in
column C. So It seems to me that the code would be:

With Worksheets("Machine Specification")
  Cells(Cells.Find("Norm. Current (A)").Row, 3), .Cells(Cells.Find("Norm. Current (A)").Row, LastColumn)).Value = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text
End With

And VBA returns a red line.

What is the mistake I'm making, can someone, please, help?

excel vba cell

Solution 1:^[1]

Range(.Cells(.Cells.Find("Norm. Current (A)").Row, 3), .Cells(.Cells.Find("Norm. Current (A)").Row, LastColumn)).value = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text

But it looks easier:

iRow = .Cells.Find("Norm. Current (A)").Row
Range(.Cells(iRow, 3), .Cells(iRow, LastColumn)).value = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text

Solution 2:^[2]

Your second example won't work as Cells references a single cell, while 'Range' can reference one or more cells.

I guess your line of code should be written as:

.Range(.Cells(.Cells.Find("Norm. Current (A)").Row, 3), .Cells(.Cells.Find("Norm. Current (A)").Row, LastColumn)) = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text

The problem with that is, as @SJR commented, you're chaining commands that can fail together. If "Norm. Current (A)" doesn't exist on the sheet you'll get an error message.

Also, your line of code only partially makes use of the With command.

Cells(Cells.Find("Norm. Current (A)").Row, 3), .Cells(Cells.Find

In the example above only the second from last Cells is looking at the Machine Specification sheet as it has the . before it. All the other Cells are looking at whichever sheet is currently active - if it's not the correct sheet you'll get an error as you can't build a range reference that looks at different sheets.

I'd write the code as:

Sub Test()

    'Don't know where you get your LastColum figure from, so made it up.
    Dim LastColumn As Long
    LastColumn = 5
    
    Dim NormCurrentA As Range

    With Worksheets("Machine Specification")
        
        'Find remembers the last settings you used, so best to set them all.
        Set NormCurrentA = .Cells.Find( _
            What:="Norm. Current (A)", _
            After:=.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=True)
            
        'Check that something was found before trying to use it.
        If Not NormCurrentA Is Nothing Then
        
            'All Ranges and Cells must be preceded by a '.' to work with the With....End With command.
            .Range(.Cells(NormCurrentA.Row, 3), .Cells(NormCurrentA.Row, LastColumn)) = UserForm_ConnectionParameters.NormCurrent_ComboBox.Text
            
        End If
    End With

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Darren Bartrup-Cook

'VBA Cells range address syntax (FIND specific value to LastColumn)

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]