VBA IF column x row x = Date +1 then
Results 1 to 10 of 10

Thread: VBA IF column x row x = Date +1 then

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA IF column x row x = Date +1 then

    hi guys, just joined this forum as a new project is underway. i have been recording my bank transactions for some time now in multiple columns of excel and have been trying to predict amounts. this has all been manual and now i think its time to make it a little more automated.

    i have built database in access before so im not new to VBA but i am struggling. i dodnt really know vba that much and rely on the pop up box in access. ie start typing If and the string appears. i dont get that in excel. is there something i can toggle.

    thats not my main question. i want to insert values based on results.

    column a is day, column b is date.
    i want to say if column b row x = DateAdd(Date(),"d",1) then
    clear contents. of k(where row = that of b
    i want to cycle this for 180 days.

    im not sure if i have explained that correctly

    this is on sheet activation sub. so when i select the sheet it performs this action.
    i will repeat this for each of the columns that are attributed to each of my bank accounts.

    thanks for any help that is given. i tend to learn as i go.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,711
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    Try the following code and tell me if it is what you need.

    Code:
    Sub test2()
      Dim c As Range
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Cells(c.Row, "B") = DateAdd("d", 1, Date) Then
          Cells(c.Row, "K").ClearContents
        End If
      Next
    End Sub
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    yes thank you it does work. now how do i repeat this for 180 rows starting at c.Row.
    i plan on repeating this for each of the accounts i have in my sheet that are active. the ones that are hidden(no longer in use) dont matter.

    for the next step after this will be about data input from another sheet on the same document. do you recommend i start another thread for help with that part?

    thank you for your help so far.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    hi ive managed to do it rhough a google search.

    is this the correct method. it does seem 'clunky'.

    Private Sub Worksheet_Activate()
    'delete all data in column k for 180 days from todays date
    Dim c As Range
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    'Gets Current Date'
    If Cells(c.Row, "B") = DateAdd("d", 1, Date) Then
    'Repeat X times'
    For x = 0 To 179
    Cells(c.Row + x, "K").ClearContents
    Cells(c.Row + x, "O").ClearContents
    Cells(c.Row + x, "S").ClearContents
    Cells(c.Row + x, "W").ClearContents
    Cells(c.Row + x, "Y").ClearContents
    Cells(c.Row + x, "AA").ClearContents
    Cells(c.Row + x, "AC").ClearContents
    Cells(c.Row + x, "AG").ClearContents
    Cells(c.Row + x, "AI").ClearContents
    Cells(c.Row + x, "AM").ClearContents
    Next x
    End If
    Next

    like i said, clunky. i think it is slow because in the column next to k, o, s etc there is a running total of each account. so each time row x from k is deleted it recalculates row 'L'. takes its time over many columns and rows but it does work how i want it to.

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

    Default Re: VBA IF column x row x = Date +1 then

    Why do you want it repeated 180 times?

    This instruction runs through the entire column:
    Code:
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    You can give an example of the data you have.

    -------------
    To make your process faster, use the following:

    Code:
    Private Sub Worksheet_Activate()
      'delete all data in column k for 180 days from todays date
      Dim c As Range, x As Long
      
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        'Gets Current Date'
        If Cells(c.Row, "B") = DateAdd("d", 1, Date) Then
          'Repeat X times'
          Range(Cells(c.Row, "K"), Cells(c.Row + 179, "K")).ClearContents
          Range(Cells(c.Row, "O"), Cells(c.Row + 179, "O")).ClearContents
          Range(Cells(c.Row, "S"), Cells(c.Row + 179, "S")).ClearContents
          Range(Cells(c.Row, "W"), Cells(c.Row + 179, "W")).ClearContents
          Range(Cells(c.Row, "Y"), Cells(c.Row + 179, "Y")).ClearContents
          Range(Cells(c.Row, "AA"), Cells(c.Row + 179, "AA")).ClearContents
          Range(Cells(c.Row, "AC"), Cells(c.Row + 179, "AC")).ClearContents
          Range(Cells(c.Row, "AG"), Cells(c.Row + 179, "AG")).ClearContents
          Range(Cells(c.Row, "AI"), Cells(c.Row + 179, "AI")).ClearContents
          Range(Cells(c.Row, "AM"), Cells(c.Row + 179, "AM")).ClearContents
        End If
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
    End Sub
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    Quote Originally Posted by DanteAmor View Post
    Why do you want it repeated 180 times?

    This instruction runs through the entire column:
    Code:
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    You can give an example of the data you have.

    -------------
    To make your process faster, use the following:

    Code:
    Private Sub Worksheet_Activate()
      'delete all data in column k for 180 days from todays date
      Dim c As Range, x As Long
      
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      
      For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        'Gets Current Date'
        If Cells(c.Row, "B") = DateAdd("d", 1, Date) Then
          'Repeat X times'
          Range(Cells(c.Row, "K"), Cells(c.Row + 179, "K")).ClearContents
          Range(Cells(c.Row, "O"), Cells(c.Row + 179, "O")).ClearContents
          Range(Cells(c.Row, "S"), Cells(c.Row + 179, "S")).ClearContents
          Range(Cells(c.Row, "W"), Cells(c.Row + 179, "W")).ClearContents
          Range(Cells(c.Row, "Y"), Cells(c.Row + 179, "Y")).ClearContents
          Range(Cells(c.Row, "AA"), Cells(c.Row + 179, "AA")).ClearContents
          Range(Cells(c.Row, "AC"), Cells(c.Row + 179, "AC")).ClearContents
          Range(Cells(c.Row, "AG"), Cells(c.Row + 179, "AG")).ClearContents
          Range(Cells(c.Row, "AI"), Cells(c.Row + 179, "AI")).ClearContents
          Range(Cells(c.Row, "AM"), Cells(c.Row + 179, "AM")).ClearContents
        End If
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
    End Sub
    i knew there was a way to shorten this. i was trying eg. a2:a182 but vba doesnt like that.

    i think im getting used to the format of how xcel vba likes it. it seems very much different to access vba.

    thank you. the reason i want to repeat 180 times is because each row i a date and 180 times means the next 180 days of days are removed before the next step.

    the next step in my project draws data from one sheet to this current sheet.

    i want to find the current date +1 again. from this, if the 'd' = sheet 7. b3 then k = sheet 7.d3

    ive uploaded the excel sheet so you can see what im doing. ive removed all the account numbers and non relevant sheets.

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    not sure if have permissions to uplad documents.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,711
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    Quote Originally Posted by Shutz View Post
    the next step in my project draws data from one sheet to this current sheet.
    Before moving on to the next step or steps.


    Does the code sent in post #6 work for you?
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    yes. much much quicker than my way. around 0.5 secs compared to minutes

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,711
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA IF column x row x = Date +1 then

    Glad to help you. thanks for the feedback.

    If it is a new topic, I recommend you create a new thread.
    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
  •