menschmaschine
New Member
- Joined
- Dec 21, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
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!
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!
Excel Formula:
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