- #1
big man
- 254
- 1
Hi
I just have a quick question about a VBA program I've been writing. It's mostly working, but there is a slight problem when I use the "Selection.Offset" capability. For example, if I had a range selected of $C$23:$C$34 then I would have thought that if I did "MsgBox Selection.Offset(0, 1).Address" I would get a new range of $D$23:$D$34. However, what I get is actually D23 and that's all. There essentially isn't any range anymore. I've attached the section of code for you to look at so that maybe it makes more sense, but hopefully you have an idea as to why this isn't bloody working. I have to warn you though that I am a messy coder and so it isn't the most elegant work.
For Counter = 1 To NumberofColumns
If Counter = NumberofColumns - 3 Or Counter = NumberofColumns - 2 Or Counter = NumberofColumns - 1 Then
GoTo 124
End If
If AccessNoLoc - Counter = 0 Then
GoTo 124 'GOTO "Next Counter" in this loop
ElseIf AccessNoLoc - Counter < 0 Then
GoTo 115 'GOTO "Index = Counter - AccessNoLoc"
Else
For n = 1 To Rangarrsize
SelRange = Beginning(n) & ":" & Final(n)
Range(SelRange).Select
MsgBox Selection.Offset(0, -Counter).Address
With Selection
.MergeCells = True
End With
Next n
End If
115 Index = Counter - AccessNoLoc
If Index > 0 Then
For n = 1 To Rangarrsize
SelRange = Beginning(n) & ":" & Final(n)
Range(SelRange).Select
MsgBox Selection.Offset(0, Index).Address
With Selection
.MergeCells = True
End With
Next n
End If
124 Next Counter
Appreciate any assistance!
I just have a quick question about a VBA program I've been writing. It's mostly working, but there is a slight problem when I use the "Selection.Offset" capability. For example, if I had a range selected of $C$23:$C$34 then I would have thought that if I did "MsgBox Selection.Offset(0, 1).Address" I would get a new range of $D$23:$D$34. However, what I get is actually D23 and that's all. There essentially isn't any range anymore. I've attached the section of code for you to look at so that maybe it makes more sense, but hopefully you have an idea as to why this isn't bloody working. I have to warn you though that I am a messy coder and so it isn't the most elegant work.
For Counter = 1 To NumberofColumns
If Counter = NumberofColumns - 3 Or Counter = NumberofColumns - 2 Or Counter = NumberofColumns - 1 Then
GoTo 124
End If
If AccessNoLoc - Counter = 0 Then
GoTo 124 'GOTO "Next Counter" in this loop
ElseIf AccessNoLoc - Counter < 0 Then
GoTo 115 'GOTO "Index = Counter - AccessNoLoc"
Else
For n = 1 To Rangarrsize
SelRange = Beginning(n) & ":" & Final(n)
Range(SelRange).Select
MsgBox Selection.Offset(0, -Counter).Address
With Selection
.MergeCells = True
End With
Next n
End If
115 Index = Counter - AccessNoLoc
If Index > 0 Then
For n = 1 To Rangarrsize
SelRange = Beginning(n) & ":" & Final(n)
Range(SelRange).Select
MsgBox Selection.Offset(0, Index).Address
With Selection
.MergeCells = True
End With
Next n
End If
124 Next Counter
Appreciate any assistance!