smma
Mechanical
- Jun 4, 2007
- 24
Hello! I have a program embedded in an excel workbook that controls the taborder of a worksheet. The macro which controls the taborder is in the ThisWorkbook object, while the function telling the macro the order of the range is embedded in the Worksheet itself. The function began like this:
Public Function TabOrder() As Range
Set TabOrder = [A1,B1,C1]
End Function
The worksheet this function is in has both merged and unmerged cells, and I only wanted to capture the unmerged cells along with the first cell of each merged area. I tweaked the code thus:
Public Function TabOrder() As Range
Dim Tabulate() As Variant
InfoArr = Array("C3", "D5", "D7", "F7", "J2")
BOMArr = Array("B", "C", "D", "G") 'D merged w/ E & F
ArrBOM = Array("K", "L", "O", "P") 'L merged w/ M & N
For r = 0 To 5
v = v + 1
ReDim Preserve Tabulate(1 To v)
Set Tabulate(v) = InfoArr(r)
Next r
For s = 1 To 2
For t = 1 To 30
For u = 0 To 3
v = v + 1
ReDim Preserve Tabulate(1 To v)
If s = 1 Then
Set Tabulate(v) = BOMArr(u) & t + 10
ElseIf s = 2 Then
Set Tabulate(v) = ArrBOM(u) & t + 10
End If
Next u
Next t
Next s
Set TabOrder = [Tabulate]
End Function
However, when I try to set the TabOrder range, it errors out. Is there a way to put an array of cell references into a range? I haven't been able to find anything specific to this in VBA Help or online.
Thanks!!
Public Function TabOrder() As Range
Set TabOrder = [A1,B1,C1]
End Function
The worksheet this function is in has both merged and unmerged cells, and I only wanted to capture the unmerged cells along with the first cell of each merged area. I tweaked the code thus:
Public Function TabOrder() As Range
Dim Tabulate() As Variant
InfoArr = Array("C3", "D5", "D7", "F7", "J2")
BOMArr = Array("B", "C", "D", "G") 'D merged w/ E & F
ArrBOM = Array("K", "L", "O", "P") 'L merged w/ M & N
For r = 0 To 5
v = v + 1
ReDim Preserve Tabulate(1 To v)
Set Tabulate(v) = InfoArr(r)
Next r
For s = 1 To 2
For t = 1 To 30
For u = 0 To 3
v = v + 1
ReDim Preserve Tabulate(1 To v)
If s = 1 Then
Set Tabulate(v) = BOMArr(u) & t + 10
ElseIf s = 2 Then
Set Tabulate(v) = ArrBOM(u) & t + 10
End If
Next u
Next t
Next s
Set TabOrder = [Tabulate]
End Function
However, when I try to set the TabOrder range, it errors out. Is there a way to put an array of cell references into a range? I haven't been able to find anything specific to this in VBA Help or online.
Thanks!!