Results 1 to 9 of 9

Thread: Help with a macro please

  1. #1
    Board Regular
    Join Date
    Feb 2019
    Posts
    138
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Help with a macro please

    Hi Everyone,

    I have a workbook with Sheet 1 in it. Each day Sheet 1 is updated with up to 600 rows of data. We have several rows that we need to copy data to for example, where Cells A3, A4, B3 & B4 below are empty I would like to copy A2 & B2 and so forth down column A & B. I'm trying to come up with a macro to do that if someone could be so kind.

    Thank you!!

    Column A Column B C D E F
    Row1-00000000010054/000 20 OZ FOUNTIAN $1.19 4 5 $5.95
    Row2-00000000010078/000 32 OZ FOUNTIAN $1.39 21 21 $29.19
    Row3- $1.19 1 1 $1.19
    Row4- 22 $30.38
    Row5-00000000010511/000 Refill Fntn $0.79 11 11 $8.69
    Row6-00000000010528/000 44 OZ FOUNTIAN $1.29 3 3 $3.87
    Row7- $1.49 17 21 $31.29
    Row8- 24 $35.16
    00000000011037/000 16oz Coffee $1.39 4 4 $5.56
    Last edited by Buns1976; Apr 17th, 2019 at 06:53 PM.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with a macro please

    So does A2:B2 go in rows 7 & 8 as well ??
    The prices in C are different !
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Feb 2019
    Posts
    138
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a macro please

    Hi there,

    A6 & B6 would go in A7, A8, B7 & B8!

    Thank you!

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with a macro please

    Maybe this then
    Code:
    Sub MM1()
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 2 To lr
        If Cells(r, 1).Value = "" Then
            Range(Cells(r - 1, 1), Cells(r - 1, 2)).Copy Cells(r, 1)
        End If
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    Feb 2019
    Posts
    138
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a macro please

    Michael,
    I messed up! This works great in my test sheet where the first column is "A" however in the actual sheet the first column is "D".
    I tried changing the macro to the following but that doesn't seem to work?

    Thanks!

    Code:
     Sub Merge()Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    For r = 2 To lr
        If Cells(r, 1).Value = "" Then
            Range(Cells(r - 1, 1), Cells(r - 1, 2)).Copy Cells(r, 1)
        End If
    Next r
    End Sub

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with a macro please

    You need to change the column reference

    Code:
    Sub MM1()
    Dim lr As Long, r As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 2 To lr
        If Cells(r, 4).Value = "" Then
            Range(Cells(r - 1, 4), Cells(r - 1, 5)).Copy Cells(r, 4)
        End If
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help with a macro please

    Try the following:

    Code:
    Sub Macro1()
        With Range("D2:E" & Range("D" & Rows.Count).End(xlUp).Row)
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
    End Sub
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Feb 2019
    Posts
    138
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a macro please

    THANK YOU Michael and Dante!

    Much appreciated!

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help with a macro please

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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