VB Confusion

[pickwa]

Hi Guys,

Some one on here very kindly helped me to start a VB script to go in my spread sheet. I have since changed a few things and thought i was getting the hang of things.....Until! i started playing with dates

In my spread sheet i have dates written like 05/02/2011

the following script actions on a yearly basis and only seems to look at the year at a whole, i need it to use the month value.

So if the date in the box is more than one month ago "pass on"

If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date + 1 Then Target.Offset(0, -5).Value = "Pass On"

Im new to VB so i found that quite hard to explain. if anyone could help me out it would be much appreciated!

Cheers

Pickwa

 

[kpark91]

I really am not too sure what you're trying to do,
but if your "Target" is in date and you want to capture the month part.

You can simply put

msgBox Month(Target.Value)

If this doesn't work try,

MsgBox Month(DateValue(Target.Value))

DateValue() will change a string into Date, which is essentially a number represented in a different way.

 

[pickwa]

im sorry i didnt explain my self very well

i have a speadsheet that is going to tell me when to send out letters to people.

i am trying to create a status box

when a date is entered into a cell(date of letter sent) the status box says one thing. eg 'letter sent'

then(this is where i got stuck) one month later i want the status box to automatically change to somthing else "pass on"

i really am clueless when it comes to VB and this is litterally the last thing i need to complete my spreadsheet.

if anyone could show me how to change my script it would be massively apprecaited because im completely stumped

cheers,

Pickwa

ive included all of my script below it make make it clearer what i am trying to achieve.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -1).Value = "processing"
End If
If Target.Column = 11 Then
If Target.Value = "No" Then Target.Offset(0, -2).Value = "Send 1st Letter"
If Target.Value = "Yes" Then Target.Offset(0, -2).Value = "Case Closed"
End If
If Target.Column = 12 Then
If Target.Value <> "" Then Target.Offset(0, -3).Value = "1st letter sent"
If Target.Value > Date - 40 Then Target.Offset(0, -3).Value = "send Charge Letter"
End If
If Target.Column = 13 Then
If Target.Value <> "" Then Target.Offset(0, -4).Value = "Charge Letter Sent"
If Target.Value > Date - 40 Then Target.Offset(0, -4).Value = "Send Surcharge Leter"
End If
If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date - 1 Then Target.Offset(0, -5).Value = "Pass On"
End If
If Target.Column = 15 Then
If Target.Value >= "0" Then Target.Offset(0, -6).Value = "Case Closed"
End If
If Target.Column = 17 Then
If Target.Value = "YES" Then Target.Offset(0, -6).Value = "Paid"
End If
End Sub