Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: Macro that concatenates two cells, references a Date and then copy pastes value into

  1. #1
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012

    Macro that concatenates two cells, references a Date and then copy pastes value into



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Would love some help writing this macro
    i tried attaching a file with more explanations within the file if it helps make it clearer. (in yellow highlights & red text)


    I have two sheets. One titled 'Money', the second titled 'Data'
    In the 'Money' tab, there's a section which currently begins from row 41 where the macro would be populating the data it's pulling. For clarity's sake, i'll refer to this section as the Expense section.

    Essentially, the macro is pulling data from the 'Data' tab based on certain criteria (the date). If the criteria is met, it will copy the data of its respective row from the 'Data' tab; will go to the 'Money' tab, look for the last row that contains data in the first portion of the Expense section, creates a new row and pastes values in this new row; making sure not to be pasting over any previous data

    written out as steps:

    if the concatenation of N & O of 'Data' sheet, viewed as Date format, matches the month of today's date; then copy and paste values of the concatenation into the first available row in column B of 'Money' sheet (beginning from B42 onwards)
    then still referencing that same row in 'Data' sheet, copy columns P thru S and paste values into columns C thru F of 'Money' sheet in the same row that was previously referenced.
    then still referencing that same row in 'Data' sheet, copy columns T and U and paste values into columns H & I of 'Money' sheet in the same row that was previously referenced.
    then still referencing that same row in 'Data' sheet, copy column V and paste values into column G of 'Money' sheet in the same row that was previously referenced



    i hope i explained that decently. please let me know if the Excel file isn't any clearer and i'll add more clarity.
    Attached Files Attached Files

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question
    As you are new I will add it for you this once https://www.ozgrid.com/forum/index.p...i/#post1243461
    Thank you Ken for this secure forum.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    Try this

    Code:
    Public Sub GetData()
    Dim target As Worksheet
    Dim testdate As Date
    Dim nextrow As Long
    Dim lastrow As Long
    Dim i  As Long
        Application.ScreenUpdating = False
        
        Set target = Worksheets("Data")
        With target
        
            lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
        End With
        
        
        With Worksheets("Money")
        
            If .Cells(42, "B").Value = vbNullString Then
            
                nextrow = 42
            ElseIf .Cells(43, "B").Value = vbNullString Then
            
                nextrow = 43
            Else
            
                nextrow = Cells(42, "B").End(xlDown).Row + 1
            End If
            
            For i = 2 To lastrow
            
                testdate = 0
                On Error Resume Next
                testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value)
                On Error GoTo 0
                
                If testdate = Date Then
                
                    .Cells(nextrow, "B").Value = Date
                    target.Cells(i, "P").Resize(1, 4).Copy .Cells(nextrow, "C")
                    target.Cells(i, "T").Resize(1, 2).Copy .Cells(nextrow, "H")
                    target.Cells(i, "V").Copy .Cells(nextrow, "G")
                    
                    nextrow = nextrow + 1
                End If
            Next i
        End With
        Application.ScreenUpdating = True
    End Sub
    

  4. #4
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    thanks so much for the help Bob.

    Just tried running it, getting this error: Run-time error '13' Type mismatch.
    when i click Debug, it stops at this point:

    If .Cells(42, "B").Value = vbNullString Then


    any ideas why? i'm going to do some googling and try to understand what the code is doing

  5. #5
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please add code tags around code ( the #button). Thanks

    Please confirm you have read and understood post #2
    Thank you Ken for this secure forum.

  6. #6
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    yes i understand post 2

  7. #7
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please add code tags as requested in post #4
    Thank you Ken for this secure forum.

  8. #8
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    i didn't see the option to edit my post


    are you also able to help with my question or just policing?

  9. #9
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    As an admin, one of my jobs is keeping the forum tidy
    Thank you Ken for this secure forum.

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by rj1600 View Post
    Just tried running it, getting this error: Run-time error '13' Type mismatch.
    when i click Debug, it stops at this point:

    If .Cells(42, "B").Value = vbNullString Then


    any ideas why? i'm going to do some googling and try to understand what the code is doing
    No, I ran it on the workbook you provided and it worked for me.

    But, I have just tried it again, and I forgot to mention you need to clear out all of those #N/As in A42:F52.

    I also noticed an omission in my code, one unqualified cell reference. this is the corrected version

    Code:
    Public Sub GetData()
    Dim target As Worksheet
    Dim testdate As Date
    Dim nextrow As Long
    Dim lastrow As Long
    Dim i  As Long
        Application.ScreenUpdating = False
        
        Set target = Worksheets("Data")
        With target
        
            lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
        End With
        
        
        With Worksheets("Money")
        
            If .Cells(42, "B").Value = vbNullString Then
            
                nextrow = 42
            ElseIf .Cells(43, "B").Value = vbNullString Then
            
                nextrow = 43
            Else
            
                nextrow = .Cells(42, "B").End(xlDown).Row + 1
            End If
            
            For i = 2 To lastrow
            
                testdate = 0
                On Error Resume Next
                testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value)
                On Error GoTo 0
                
                If testdate = Date Then
                
                    .Cells(nextrow, "B").Value = Date
                    target.Cells(i, "P").Resize(1, 4).Copy .Cells(nextrow, "C")
                    target.Cells(i, "T").Resize(1, 2).Copy .Cells(nextrow, "H")
                    target.Cells(i, "V").Copy .Cells(nextrow, "G")
                    
                    nextrow = nextrow + 1
                End If
            Next i
        End With
        Application.ScreenUpdating = True
    End Sub

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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