Page 2 of 2 FirstFirst 1 2
Results 11 to 20 of 20

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

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


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

    fair enough. sorry

    Quote Originally Posted by Pecoflyer View Post
    As an admin, one of my jobs is keeping the forum tidy

  2. #12
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    still isn't producing anything for me.
    it runs now, but nothing happens


    also, should i replace 'Year' with 'Month' on this line?
    # testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value) #

  3. #13
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    can you upload the file you're working in?
    can't figure out why it doesn't work on my side


    Quote Originally Posted by Bob Phillips View Post
    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

  4. #14
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    To add code tags select the code and click the #button.
    You can see the result in Bob's post
    It's important so that it can be copied easier maintaining the formatting

    And if possible please do not quote entire posts (makes posts hard to read and clutters), use the "Quick Reply" button instead. Thanks
    Thank you Ken for this secure forum.

  5. #15
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,851
    Articles
    0
    Excel Version
    O365
    [QUOTE=rj1600;45495]still isn't producing anything for me.
    it runs now, but nothing happens[/code]

    You need to have some data with today's date, that was part of your requirement.


    Quote Originally Posted by rj1600 View Post
    also, should i replace 'Year' with 'Month' on this line?

    No.
    # testdate = DateSerial(Year(Date), target.Cells(i, "N").Value, target.Cells(i, "O").Value) #

  6. #16
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,851
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by rj1600 View Post
    can you upload the file you're working in?
    can't figure out why it doesn't work on my side
    I had to manipulate the source data to force a couple of items for today's date.

  7. #17
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    Quote Originally Posted by Bob Phillips View Post
    I had to manipulate the source data to force a couple of items for today's date.

    ok, so i take that as a no.

    i managed to get this done through formulae.
    @pecoflyer, you can delete this thread since it was worthless

  8. #18
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,851
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by rj1600 View Post
    @pecoflyer, you can delete this thread since it was worthless
    That is an incredibly rude comment, I will ensure that I never bother trying to help such an ingrate as yourself again.

    I am sure no-one will delete it, it should stand as a testimony to you and your attitude.

  9. #19
    Banned rj1600's Avatar
    Join Date
    Jan 2021
    Posts
    9
    Articles
    0
    Excel Version
    Version 2012
    Quote Originally Posted by Bob Phillips View Post
    That is an incredibly rude comment, I will ensure that I never bother trying to help such an ingrate as yourself again.

    I am sure no-one will delete it, it should stand as a testimony to you and your attitude.

    oh no.

    hey "bob"
    try treading through the thread and recognizing you weren't much help to begin with.
    learn how to answer people's questions instead of just doing whatever you want.

    i wasn't anticipating anyone deleting it. i'm fully aware of the psyche of coders

  10. #20
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,750
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @rj1600
    Due to your lack of respect towards our members trying to help you for free on their spare time, I am withdrawing your posting privileges for 2 weeks.
    I hope that when you post again, your attitude will have changed.
    Thread closed
    Thank you Ken for this secure forum.

Page 2 of 2 FirstFirst 1 2

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
  •