VBA Macro Programming: Fix "Selection.Offset()" Problem

[big man]

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!

 

[FredGarvin]

This is from John Walkenbach's web site. He's the guru on this:
http://www.j-walk.com/ss/

The Offset method is another useful way to refer to ranges. The Offset method returns a Range object, and takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.

The following statement assigns the value 1 to the cell that is one row below cell C2 and two cells to the right of C2 (i.e., cell E3):

Range("C2").Offset(1,2).Value = 1

The Offset method is most useful when the arguments are variables, rather than numbers. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.

Sub FillRange2()
      Num = 1
      For Row = 0 To 9
          For Col = 0 To 9
              Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num
              Num = Num + 1
          Next Col
      Next Row
End Sub

I think with a little stroking you can make this fir your needs.

 

[big man]

Thanks for that Fred. I've modified it now so that I've broken the "range" up into a beginning point and end point and so now I offset each part.

So as you can see by the code below I have a beginning cell (beg) and an end cell (fin).
Now when I put a MsgBox to print both of these cell addresses I get say D1 for beg and D3 for fin. Then when I do Range(Beg).Select and I offset the Beg cell by 1 column using Selection.Offset(0,1).* and I print the address I get E1. However, the problem arises in the next line when I do a new selection. The new selection is selecting the end cell. But when I print the offset for this selection I again get E1 instead of getting E3. Is there something I'm missing?

For n = 1 To Rangarrsize
Beg = Beginning(n)
Fin = Final(n)
Range(Beg).Select
def = Selection.Offset(0, 1).Address
MsgBox def
Range(Fin).Select
abc = Selection.Offset(0, 1).Address
MsgBox abc
Next n