If a cell value is today's date, than increace cell value by one
 

Hi, I would like to compare a columns that contains date (2005/11/30)
to today's date.
If it is, than increase a cell by one.
I'm having problem with the format

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A1:A10")) Is Null Then
;so it skips the empty cells
Application.EnableEvents = False
if the cell is today's date than ;not sure
of the format
Range("A1").Value = Range("A1").Value + 1
Application.EnableEvents = True
End If
End Sub

Thanks

If a cell value is today's date, than increace cell value by one
 

"magickarle" escribió en el mensaje
oups.com...
Hi, I would like to compare a columns that contains date (2005/11/30)
to today's date.
If it is, than increase a cell by one.
I'm having problem with the format

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A1:A10")) Is Null Then
;so it skips the empty cells
Application.EnableEvents = False
if the cell is today's date than ;not sure
of the format
Range("A1").Value = Range("A1").Value + 1
Application.EnableEvents = True
End If
End Sub

Thanks



Try this:

If [D1] = Date Then

supossing D1 contains the date to compare...

If a cell value is today's date, than increace cell value by one
 

Ok but how can I loop the
If D1 = Date then...

I would like to do a loop between A1 n A10
Thanks

If a cell value is today's date, than increace cell value by one
 

For column = 1 to 10
if Range ("A" & column).Value = Date Then
'Your actions here
End If
Next Column

Or if you want to compare from A1 to F1

For letter= 65 to 70 'Ascii values of letters A to F
if Range (chr(letter) & "1").Value = Date Then
'Your actions here
End If
Next letter

"magickarle" escribió en el mensaje
oups.com...
Ok but how can I loop the
If D1 = Date then...

I would like to do a loop between A1 n A10
Thanks

If a cell value is today's date, than increace cell value by one
 

cool. I didn't think of using ascii value (I did it when I was doing
Ada script. I guess it's the same principle as other script language)

Another question: I would like to compare two columns
Ex: @ an event on column F do
If column F = 1 and Column G got nothing do
Increase F341 by one
If column F = 2 and Column G got nothing do
Increase F342 by one
If column F = 3 and Column G got nothing do
Increase F343 by one
End if.a
What I did is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("F:F")) Is Nothing Then
Application.EnableEvents = False
If Application.Intersect(Target, Columns("F:F")) Is "1" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F341").Value = Range("F341").Value + 1
If Application.Intersect(Target, Columns("F:F")) Is "2" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F342").Value = Range("F342").Value + 1
If Application.Intersect(Target, Columns("F:F")) Is "3" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F343").Value = Range("F343").Value + 1
End If
End If
End Sub

It gives me an error "type not compatible" for the Is "1" in the first
if.
I guess it's bcause I choose Private Sub Worksheet_Change(ByVal Target
As Range)

thanks again for your help.

If a cell value is today's date, than increace cell value by one
 

Do not say Is "1"

Say = "1" or =1 (depends on if your are handling text or numbers

"magickarle" escribió en el mensaje
oups.com...
cool. I didn't think of using ascii value (I did it when I was doing
Ada script. I guess it's the same principle as other script language)

Another question: I would like to compare two columns
Ex: @ an event on column F do
If column F = 1 and Column G got nothing do
Increase F341 by one
If column F = 2 and Column G got nothing do
Increase F342 by one
If column F = 3 and Column G got nothing do
Increase F343 by one
End if.a
What I did is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("F:F")) Is Nothing Then
Application.EnableEvents = False
If Application.Intersect(Target, Columns("F:F")) Is "1" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F341").Value = Range("F341").Value + 1
If Application.Intersect(Target, Columns("F:F")) Is "2" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F342").Value = Range("F342").Value + 1
If Application.Intersect(Target, Columns("F:F")) Is "3" And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then
Range("F343").Value = Range("F343").Value + 1
End If
End If
End Sub

It gives me an error "type not compatible" for the Is "1" in the first
if.
I guess it's bcause I choose Private Sub Worksheet_Change(ByVal Target
As Range)

thanks again for your help.

If a cell value is today's date, than increace cell value by one
 

Cool. Thanks. I got a new issue loll: "Execution error 91: non-defined
object or bloc with."
It happend on the line "If Application.Intersect(Target,
Columns("F:F")) = 1 And Application.Intersect(Target, Columns("G:G"))
Is Nothing Then" when I put something in column G

here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("G:G")) Is Nothing Then
;If the column G got something
Application.EnableEvents = False
Worksheets("Time Sheet").Range("B16").Value = Worksheets("Time
Sheet").Range("B16").Value + 1 ;add one to cell B16
Application.EnableEvents = True
End If
If Application.Intersect(Target, Columns("F:F")) = 1 And
Application.Intersect(Target, Columns("G:G")) Is Nothing Then ;here is
where the debugger hangs
Worksheets("Time Sheet").Range("e16").Value = Worksheets("Time
Sheet").Range("E16").Value + 1
End If
End Sub

I want to add 1 to cell E16 only when I input 1 in a cell of column F
and that the respective cell in column G got nothing.
Hope it makes sense loll!
Thanks