VBA Working on parallel ranges

    Hi All,
    I have a range of data (width of 1 column and variable number of rows).
    I am looking for a code that will go loop through the data (top to bottom), check something, and if result is true – enter a variation of the data in a different range (same size as the original range, as selected by the user).
    So basically, the user selects a source range, a destination range (must be the same size of range, “parallel” to the source range) and the code will loop through the source data, do some calculation, and enter the result in the parallel range, at the same row.
    For example:
    User selects range A4:A20 as source, range H4:H20 as destination.
    Code loops though source range. IF cell value is smaller than 50, the code will multiply the result by 2 and place the new value in a parallel cell in the destination range (same row).
    If the value is bigger than 50, the code will put null in the parallel cell in the destination range
    I don’t think I can use offset, because user selection (of both ranges) can change each time the code is run, so I need you help in kick-starting this code for me.
    How do I identify the current X,Y position and pass the relative position to the destination range (X,Y position, but in a different column).
    Many thanks in advance

    Don't see why you couldn't use Offset or just refer to relative position of each cell as it will be the same in each range. How are the ranges to be selected?


    Or something along these lines, which uses a formula so need to loop.


    [VBA]Sub x()


    Dim r1 As Range, r2 As Range


    Set r1 = Range("A4:A20")
    Set r2 = r1.Offset(, 7)


    With r2
    .Formula = "=if(" & r1(1).Address(0, 0) & "<50," & r1(1).Address(0, 0) & "*2,""Null"")"
    .Value = .Value
    End With


    End Sub[/VBA]

    Quote from StephenR

    Don't see why you couldn't use Offset or just refer to relative position of each cell as it will be the same in each range. How are the ranges to be selected?


    Or something along these lines, which uses a formula so need to loop.


    Thank for the very nice code but OFFSET is tricky because I have no idea which range the user will select as destination.
    The user selects a a source range and a destination range, both are unknown to me in advance. I just know they are parallel (I will also need to verify this by code at a later stage, but for now - I have to trust the user).
    That is exactly why I posted my question :)

    Quote

    The user selects a a source range and a destination range, both are unknown to me in advance


    If they are unknown to you, how can the code know? Do you mean they actively select two ranges by highlighting them? Why not incorporate that into the macro so that it invites them to select the ranges?

    Quote from StephenR


    If they are unknown to you, how can the code know? Do you mean they actively select two ranges by highlighting them? Why not incorporate that into the macro so that it invites them to select the ranges?


    My bad... Sorry for not being clearer with the definition of the process/problem.
    At the beginning of the run there are 2 input boxes, asking the user to select both ranges... (UserRange1 for the source and UserRange2 for the destination).
    Now the code should loop through UserRange1, do it s thing, and put the resulting data in the relevant cells of UserRange2.

    Then you can still use the basic of my earlier code.
    [VBA]Sub x()
    Dim UserRange1 As Range, UserRange1 As Range


    'You already define these
    Set UserRange1 = ...
    Set UserRange2 = ...


    With UserRange2
    .Formula = "=if(" & UserRange1(1).Address(0, 0) & "<50," & UserRange1(1).Address(0, 0) & "*2,""Null"")"
    .Value = .Value
    End With


    End Sub[/VBA]

    Hi Pvman,
    maybe so

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login