Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active Cell Offset (2003)

    I have a spreadsheet with several rows of data. I am wanting to use vba to sort through the data. This is part of my code:

    Range("C6").Select
    x = ActiveCell.Value
    Range("D6").Select
    y = ActiveCell.Value

    I then do a compare between x and y and then want to move down the spreadsheet. I know how to offset the activecell, but i need to offset in both columns. Any help is appreciated.
    So I guess my question is how do move down multiple columns while setting the value in the particular column to a defined variable?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Cell Offset (2003)

    First, it is almost never necessary to select a cell to work with it, and you should avoid doing so whenever possible. So, the code in your post could be better written as:

    <code>
    x = Range("C6").Value
    y = Range("D6").Value
    </code>

    I am afraid I don't understand the rest of your question. Could you maybe put together a simple workbook that shows what you want to do and upload it?
    Legare Coleman

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Active Cell Offset (2003)

    Keeping Legare's suggestion in mind. I would use a For Next loop to move through the cells.
    You'd have to determine how many rows are in the area you are working with (I'm assuming it is oriented by row).

    Something like this:
    <img src=/w3timages/greenline.gif width=33% height=2>
    Dim intRowCount as Integer, intCounter as Integer
    Dim stAddress as String

    intRowCount=Your Value here
    intCounter = 6
    For intCounter = 6 to intRowCount
    stAddress = "C" & intCounter
    x = Range(stAddress).Value
    stAddress = "D" & intCounter
    y=Range(stAddress).Value
    if x <>y then
    msgbox "Whatever you want to do when they aren't equal"
    end if
    Next intCounter
    <img src=/w3timages/greenline.gif width=33% height=2>

    Now I'll wait for Legare's comments... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Active Cell Offset (2003)

    just for reference - the reason Legare suggested not "selecting" the cells in question is that it will really slow down the code.

    I think you will be doing something like this (<font color=red>Warning - Air Code</font color=red>)

    Dim dblMaxErr As Double
    Dim i As Integer
    Dim rngX As Range
    Dim rngY As Range

    Set rngX = Range("C6")
    Set rngY = Range("D6")
    dblMaxErr = 0.00000001

    For i = 0 To 100 'or whatever
    If Abs(rngX.Offset(i, 0).Value - rngY.Offset(i, 0)) < dblMaxErr Then
    ' unless you are dealing with integers, test for small inequalities to allow for rounding
    'do statements that follow equal values
    Else
    'do statements that follow unequal values
    End If
    Next i

    'and any other code required....

    I think that is a little easier than manipulating the string addresses (but that may just be a personal preference)

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Active Cell Offset (2003)

    Yes, I realize the speed advantage that comes by not selecting.
    Its' just that Legare usually spots some honkin' error in my code, that makes me <img src=/S/blush.gif border=0 alt=blush width=15 height=15> and <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15> my pride.

    So, I learn from him - but its' painful <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Active Cell Offset (2003)

    <hr>Yes, I realize the speed advantage that comes by not selecting.<hr>

    Sorry, Catherine - my comment was really directed at the original poster, just in case he wasn't sure why Legare had used that approach, instead of selecting and re-selecting all the way down the column. I knew that you would know that! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •