Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

VBA macro programming

  1. Feb 12, 2008 #1

    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"
    For n = 1 To Rangarrsize
    SelRange = Beginning(n) & ":" & Final(n)
    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)
    MsgBox Selection.Offset(0, Index).Address
    With Selection
    .MergeCells = True
    End With
    Next n
    End If

    124 Next Counter

    Appreciate any assistance!
  2. jcsd
  3. Feb 12, 2008 #2


    User Avatar
    Science Advisor

    This is from John Walkenbach's web site. He's the guru on this:

    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.
  4. Feb 13, 2008 #3
    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)
    def = Selection.Offset(0, 1).Address
    MsgBox def
    abc = Selection.Offset(0, 1).Address
    MsgBox abc
    Next n
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook