Array Problem

[menschmaschine]

Hi,

First time posting here (I think... was on an Excel forum years ago, but don't remember which). Recently got back into VBA and ran into a problem. Was hoping someone could help.

Two sheets in a workbook. Sheet 1 contains a column with cells containing the generic reference "[Unit]" in various cells (non-contiguous). Sheet 2 contains names of Units in a contiguous list in one column.

I need to copy the names of the Units from Sheet 2 and paste them in the cells that contain "[Unit]" on Sheet 1.

Here is the code I have and it is hanging up after the first one, i.e., it places the name of the first unit from Sheet 2 to the first occurrence of "[Unit]" on Sheet 1, but then gives a Subscript Out of Range error.

Help is greatly appreciated!

Sub UnitName()

Dim URng As Range, ULRow As Long, UCel As Range, u As Long
Dim RefRng As Range, RLRow As Long
Dim indx As Long, ary()

Sheets("Sheet 1").Select
ULRow = Cells(Rows.Count, "B").End(xlUp).Row
For u = 1 To ULRow
    If Cells(u, "B").value = "[Unit]" Then
        If URng Is Nothing Then
            Set URng = Cells(u, "B")
            Else
                Set URng = Union(URng, Cells(u, "B"))
        End If
    End If
Next u

RLRow = Sheets("Sheet 2").Range("AE" & Rows.Count).End(xlUp).Row
Set RefRng = Sheets("Sheets 2").Range("AE8:AE" & RLRow)

ary = RefRng

indx = 1
For Each UCel In URng
    UCel.value = ary(1, indx)
    indx = indx + 1
Next UCel

End Sub

 

[Micron]

My guess is that's because in one place you have Sheet 2 and in the other, Sheets 2.

Cells(u, "B").value << you can do this? I thought cell reference syntax was letterNumber whereas you have B (for the column?) coming second.

 

[Alex Blakenburg]

Welcome to the Forum (or welcome back )
(@Micron - good spot didn't see that one.)

@menschmaschine, In addition to that, you don't mention which row is highlighted when you get the error,
You are loading a range of 1 column (AE) into an array (ary) but in the loop you are incrementing the Column index of the ary, which is going to error out as soon as you change it to 2.

    UCel.value = ary(1, indx)
    indx = indx + 1

' should be
    UCel.value = ary(indx, 1)
    indx = indx + 1

 

[menschmaschine]

Thanks to both for your replies! The sheet name issue is a non-issue... they are actually different names (and correctly typed)... I renamed them for this thread and incorrectly typed one. But good catch, Macron!

Alex, I suspect you are correct. The row highlighted for the error is UCel.value = ary(indx, 1). The workbook in question is on my computer at work, but I will try this first thing tomorrow and let you know!

 

[menschmaschine]

Correction to above: The row highlighted for error is UCel.value = ary(1, indx).

(indx, 1) is hopefully correct!

 

[Alex Blakenburg]

Let us know how you go.

 

[menschmaschine]

Wow. Totally worked. Changed it to (indx, 1) and bam, there it was. Thanks a lot, Alex. I spent days trying to figure this out.

 

[Alex Blakenburg]

You're welcome. Glad we could help.