VBA IF column x row x = Date +1 then

[Shutz]

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.

 

[DanteAmor]

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

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]

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]

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]

Why do you want it repeated 180 times?

This instruction runs through the entire column:

[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:

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]

Why do you want it repeated 180 times?

This instruction runs through the entire column:

[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:

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]

not sure if have permissions to uplad documents.

 

[DanteAmor]

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?

 

[Shutz]

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

 

[DanteAmor]

Glad to help you. thanks for the feedback.

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