none
Trouble with continuous data RRS feed

  • Question

  • Hi all.

    Bare with me here. Im in a pickle.

    Im wanting to create a macro which incorporates all of these issues so at the click of a button I can completely format a data set.

    [Problem 1] Essentially the first thing I want to do is create a macro to cut data from column D, E and F and move this data over to E, F and G. The first problem im having is that I need this macro to work all sizes, as each quarter a different amount of data will be ingested into this work sheet.

    so this is where I have gotten:

    Sub movedata()
    ' movedata Macro
    '  

    1) Range("D2:F2").Select 
    2) Range(Selection, Selection.End(xlDown)).Select 
    3) Selection.Cut Destination:=Range("E2:G49")
    4) Range("E2:G49").Select
    End Sub

    Line 1) and 2) I think are ok, line 3) and 4) however are not, as I don't want the range to be only until G49, but until the end of however long the next set of data will be.

    [Problem 2] in this blank column ive created, I want to copy the text "pH_(CS)" from D2 down to the bottom of the range. I then need to cut the data from column G to paste it in the next blank cell in column E (underneath the current data).

     From this, I then need to go to the next blank cell in column D (which will be under all my "pH_(CS)"), insert the text "Turb_(CS)" and copy this down to the bottom of the range.

    In a nutshell:

    Data in column E consists of pH data, and needs to be labeled as such in column D.

    Data in column G consists of turbidity data, and needs to be copied below the pH data, and then needs to be labeled as such in column D.

    What Im having trouble with is that these data sizes change all the time so i cant specify particular ranges for which the macro is to run on. Im new to VBA!!!!

    This is what i came up with:

    for ph:

    Sub ph()
     
     Range("D1:F1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Cut
        Range("E1").Select
        ActiveSheet.Paste
        Range("D2").Select
        ActiveCell.Value = "PH_(CS)"
        Selection.autofill Destination:=Range(Selection, Selection.End(xlDown))
         
    End Sub For turbidity:Sub ph()
     
     Range("D1:F1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Cut
        Range("E1").Select
        ActiveSheet.Paste
        Range("D2").Select
        ActiveCell.Value = "PH_(CS)"
        Selection.autofill Destination:=Range(Selection, Selection.End(xlDown))
       
       
    End Sub

    Help! This is a doozey for me! The ph copies to the end of the worksheet rather than the end of my data, which is affecting the turbidiy macro! elp!

    Thanks again.

    Tuesday, February 2, 2016 6:02 AM

All replies

  •   I will 'bear' with you, but not 'bare' with you ;-)        

    Sub MoveData2()
        Dim lngR As Long

        lngR = Cells(Rows.Count, "D").End(xlUp).Row
        Range("D2:F" & lngR).Cut Range("E2")
        Range("D2:D" & lngR).Value = "PH_(CS)"
        Range("G2:G" & lngR).Cut Range("E" & lngR + 1)
        Range("D" & lngR + 1 & ":D" & 2 * lngR - 1).Value = "Turb_(CS)"

    End Sub



    Tuesday, February 2, 2016 4:10 PM