VBA IF column x row x = Date +1 then

Shutz

New Member
Joined
Aug 23, 2019
Messages
8
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,142
Office Version
2007
Platform
Windows
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
 

Shutz

New Member
Joined
Aug 23, 2019
Messages
8
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.
 

Shutz

New Member
Joined
Aug 23, 2019
Messages
8
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,142
Office Version
2007
Platform
Windows
Why do you want it repeated 180 times?

This instruction runs through the entire column:
Code:
[COLOR=#333333]For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))[/COLOR]
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
 

Shutz

New Member
Joined
Aug 23, 2019
Messages
8
Why do you want it repeated 180 times?

This instruction runs through the entire column:
Code:
[COLOR=#333333]For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))[/COLOR]
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.
 

Shutz

New Member
Joined
Aug 23, 2019
Messages
8
not sure if have permissions to uplad documents.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,142
Office Version
2007
Platform
Windows
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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,142
Office Version
2007
Platform
Windows
Glad to help you. thanks for the feedback.

If it is a new topic, I recommend you create a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,193
Messages
5,485,297
Members
407,493
Latest member
J4ms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top