VBA macro programming

1. Feb 12, 2008

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
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
With Selection
.MergeCells = True
End With
Next n
End If

124 Next Counter

Appreciate any assistance!

2. Feb 12, 2008

FredGarvin

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

Code (Text):

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.

3. Feb 13, 2008

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