type mismatch when using UBOUND with Array

Hasson

Not quite sure why you're "trying to use an array" here (e.g. there's no speed advantage at present)

In your Module 2 code (in your Question) you declare variable rr as a Variant (which means it can be nearly any data type- see Microsoft guidance: Variant data type).

When VBA gets to your line:

For i = 2 To UBound(rr)

it finds you haven't said what rr is (it could be an array of worksheets like in your Module 1 macro, a workbook,  a string, a cell etc.) so it throws up an error.

In the file attached, I've modified your code (under a slighly different name -to avoid confusion with Module 1 code)  and told VBA that rr is a string array. Note that the first element of an array normally has index number 0, the next 1 etc. but I've added "Option Base 1" before the module so that the first element has index 1, the second 2 etc. (which tends to be easier for us humans to understand).

In bold, I've then shown you how to call the sheets (you don't need  obe of your loops):

Option Base 1

Sub divide_column2()
Dim lRow, i As Long
Dim mFind As Range
Dim rr As Variant

' define rr as an array of sheet names
rr = Array("rep1", "proc1")

Sheets("RP").Select
lRow = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("B2:B" & lRow)
    ' loop from first element of rr to last
    For i = 1 To UBound(rr)
        ' work with that worksheet
        With Sheets(rr(i))
            Set mFind = .Columns("B").Find(cell.Value)
            If Not mFind Is Nothing Then
                .Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value
                .Range("D" & mFind.Row).Value = cell.Offset(0, 2).Value
                .Range("E" & mFind.Row).Value = cell.Offset(0, 3).Value
            End If
        End With
    Next i

Next cell

End Sub

Note that you could replace lines like:

.Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value

with

mFind.Offset(0, 1).Value = cell.Offset(0, 1).Value

I've also corrected some incorrect spellings in your sheet headings- see red text.

Hope this helps.