Type mismatch when using for each row in vba

Question

I'm trying to code a routine to add records from a range on the worksheet SR to a listbox on a form. I want the record in the listbox to be made up of the values from a few cells in each row.

I'm getting a type mismatch on the ResultString line. I'm sure it's something simple, but I'm not seeing how what I've written is different from the usual suggestions.

Thanks in advance!

With SR
    LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
End With

Dim Rng As Range
Dim Row As Range
Dim ResultString As String

Set Rng = Range("A2:AC" & LastRow)

For Each Row In Rng.Rows
    With SR
        ResultString = .Cells(Row, 1).Value & " - " & .Cells(Row, 2).Value & " - " & .Cells(Row, 3).Value & " - " & .Cells(Row, 9).Value & " - " & .Cells(Row, 25).Value
    End With
    Me.lstResults.AddItem ResultString
Next Row

In .Cells(Row, 1) you would need a numerical index rather than a range object (which is what Row is). Perhaps you mean Row.Cells(1) although in that case the With construct is superfluous. — John Coleman
– John Coleman, Commented Apr 23, 2018 at 10:58

DisplayName · Accepted Answer · 2018-04-23 11:18:19Z

1

I propose you a different approach:

Dim SR As Worksheet
Set SR = ActiveSheet

Dim LastRow As Long
With SR
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Me.lstResults
    .ColumnCount = 25
    .ColumnWidths = "20;20;20;0;0;0;0;0;20;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;20" ' adjust it to suit your needs
    .List = SR.Range("A2:AC" & LastRow).Value
End With

this way you don't need to iterate every row and join relevant columns content while filling the listbox in one shot with ALL range values and have relevant columns visible only

this way you loose the "-" delimiter but may be yo can get around it

answered Apr 23, 2018 at 11:18

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

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

1 Comment

Tom Over a year ago

Thanks! I like this approach as it'll organise my listbox much more neatly

Collectives™ on Stack Overflow

Type mismatch when using for each row in vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related