Public Sub Worksheet_Change(ByVal Target As excel.Range)


Dim dato As Date, DatoAug As Date

dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-30-2006")



With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub

Application.EnableEvents = False
Range("F5").value = Application.Sum([Target:A5])
Application.EnableEvents = True

End With
End Sub


This formular obviously doesn't work...but since i'am noob I don't know
why. The targeted cell(If .Column < 1 Then Exit Sub) has to be collum
A otherwise it will exit sub(this part works. So the problem is that I
cannot SUM the targeted(this value is A5 to infinitive) with cell A5.

Many thx for any help

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dato As Date, DatoAug As Date

dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-30-2006")

With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub

Application.EnableEvents = False
Range("F5").Value = Application.Sum(Range(Target, Range("A5")))
Application.EnableEvents = True

End With
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"San" wrote in message
oups.com...
Public Sub Worksheet_Change(ByVal Target As excel.Range)


Dim dato As Date, DatoAug As Date

dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-30-2006")



With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub

Application.EnableEvents = False
Range("F5").value = Application.Sum([Target:A5])
Application.EnableEvents = True

End With
End Sub


This formular obviously doesn't work...but since i'am noob I don't know
why. The targeted cell(If .Column < 1 Then Exit Sub) has to be collum
A otherwise it will exit sub(this part works. So the problem is that I
cannot SUM the targeted(this value is A5 to infinitive) with cell A5.

Many thx for any help

San,

Just clear my following queries...

1. I think your point is to run the macro whenever you are entering in
Column-A (Tell me Yes/No)
2. If you are entering in Column-A and the date is also 30-Aug-06, then the
sum value should come in the cell F5, using the below code,
Application.Sum([Target:A5])
But if we add like this then all the values between Target and A5 will be of
Date, then it will result to a bulk amount.
3. Tell me in all the rows you want the same formula, or till the dates
entered from 1st row to 4th row.

Please clear my queries, so that I can try for a solution.

Regards,
Sriram


"San" wrote:

Public Sub Worksheet_Change(ByVal Target As excel.Range)


Dim dato As Date, DatoAug As Date

dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-30-2006")



With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub

Application.EnableEvents = False
Range("F5").value = Application.Sum([Target:A5])
Application.EnableEvents = True

End With
End Sub


This formular obviously doesn't work...but since i'am noob I don't know
why. The targeted cell(If .Column < 1 Then Exit Sub) has to be collum
A otherwise it will exit sub(this part works. So the problem is that I
cannot SUM the targeted(this value is A5 to infinitive) with cell A5.

Many thx for any help

Sriram wrote:
San,

Just clear my following queries...

1. I think your point is to run the macro whenever you are entering in
Column-A (Tell me Yes/No)
2. If you are entering in Column-A and the date is also 30-Aug-06, then the
sum value should come in the cell F5, using the below code,
Application.Sum([Target:A5])
But if we add like this then all the values between Target and A5 will be of
Date, then it will result to a bulk amount.
3. Tell me in all the rows you want the same formula, or till the dates
entered from 1st row to 4th row.

Please clear my queries, so that I can try for a solution.

Regards,
Sriram



Well this is my code for now. Not sure if it is the right approach.


--------------------------------------------------------------------------------------------------------------------------------
Option Explicit


Public Sub Worksheet_change(ByVal Target As excel.Range)



On Error GoTo ErrorHandler

Dim dato As Date, DatoAug As Date, DatoSept As Date, _
DatoOkt As Date, DatoNov As Date, DatoDec As Date


dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-31-2006")
DatoSept = Format("09-30-2006")
DatoOkt = Format("10-31-2006")
DatoNov = Format("11-30-2006")
DatoDec = Format("12-31-2006")



With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub ' of course I have to make
one function for each month

Application.EnableEvents = False
Range("E4").Value = Application.Sum(Range(Target, Range("A5")))
'I start in A5
Application.EnableEvents = True


End With

With Target
If .Count 1 Then Exit Sub
If .Column < 3 Then Exit Sub

Application.EnableEvents = False
.Offset(0, 1).Value = Format(Now, "mm-dd-yy")
Application.EnableEvents = True


End With


MsgBox prompt:="Husk at gemme ændringen!", Title:=" " & "OBS
OBS OBS "

End

ErrorHandler:
MsgBox "Der er opstået en fejl: " & Error() & Chr(13) _
& "Ending Sub.......venligst prøv igen", 48


End Sub
--------------------------------------------------------------------------------------------------------------------------------

I don't use any macro as it is, it just updates itself right now.

I want to enter the number of items I have made in colum A.
In collum B I have the number of items I have made (but doesn't work)
Collum C is my initials.
Collum D is the date

What I am trying to do is:

1. To be able to type the number of items in A and have it save it in
E4, so that I have the total number of items made at the end of the
month in saved in E4

2. Same as for collum A except that is had to be saved in F4

3. I type my initials in collum C and have it automatically put in the
date that given day in column D(This part works except that I can't
have 2 "Target" function in the same worksheet! As you can see I don't
have any experience :D

4. Next month (september) I would like to have the amount of items made
saved in G and H. So I have the total amount of items made and broken
for each month. The hard part is because there is made a variable
amount each day and each month, therefore there can be several cells.


--------------------------------------------------------------------------------------------------------------------------------

A5 B5 C5 D5 E
F
2 1 SJA August-31-06 4(total of aug)
2(total broken in aug)

A5 B5 C5 D5
2 1 JHN August-31-06 '(JHN another guy)

--------------------------------------------------------------------------------------------------------------------------------


now I have the same date twice and both of them are in august so they
both must be included for the total of that month.

I just need it to start in auguat to december

many thx

San,

If possible could you please send me the excel file to my email id.



And one more thing, I know only English language. So, I hope that, your file
will also have English contents. :D

C U with a solution.
Sriram

--
Regards,
Sriram Subramaniyan


"San" wrote:


Sriram wrote:
San,

Just clear my following queries...

1. I think your point is to run the macro whenever you are entering in
Column-A (Tell me Yes/No)
2. If you are entering in Column-A and the date is also 30-Aug-06, then the
sum value should come in the cell F5, using the below code,
Application.Sum([Target:A5])
But if we add like this then all the values between Target and A5 will be of
Date, then it will result to a bulk amount.
3. Tell me in all the rows you want the same formula, or till the dates
entered from 1st row to 4th row.

Please clear my queries, so that I can try for a solution.

Regards,
Sriram



Well this is my code for now. Not sure if it is the right approach.


--------------------------------------------------------------------------------------------------------------------------------
Option Explicit


Public Sub Worksheet_change(ByVal Target As excel.Range)



On Error GoTo ErrorHandler

Dim dato As Date, DatoAug As Date, DatoSept As Date, _
DatoOkt As Date, DatoNov As Date, DatoDec As Date


dato = Format(Now, "mm-dd-yy")
DatoAug = Format("08-31-2006")
DatoSept = Format("09-30-2006")
DatoOkt = Format("10-31-2006")
DatoNov = Format("11-30-2006")
DatoDec = Format("12-31-2006")



With Target

If .Column < 1 Then Exit Sub
If dato < DatoAug Then Exit Sub ' of course I have to make
one function for each month

Application.EnableEvents = False
Range("E4").Value = Application.Sum(Range(Target, Range("A5")))
'I start in A5
Application.EnableEvents = True


End With

With Target
If .Count 1 Then Exit Sub
If .Column < 3 Then Exit Sub

Application.EnableEvents = False
.Offset(0, 1).Value = Format(Now, "mm-dd-yy")
Application.EnableEvents = True


End With


MsgBox prompt:="Husk at gemme ændringen!", Title:=" " & "OBS
OBS OBS "

End

ErrorHandler:
MsgBox "Der er opstået en fejl: " & Error() & Chr(13) _
& "Ending Sub.......venligst prøv igen", 48


End Sub
--------------------------------------------------------------------------------------------------------------------------------

I don't use any macro as it is, it just updates itself right now.

I want to enter the number of items I have made in colum A.
In collum B I have the number of items I have made (but doesn't work)
Collum C is my initials.
Collum D is the date

What I am trying to do is:

1. To be able to type the number of items in A and have it save it in
E4, so that I have the total number of items made at the end of the
month in saved in E4

2. Same as for collum A except that is had to be saved in F4

3. I type my initials in collum C and have it automatically put in the
date that given day in column D(This part works except that I can't
have 2 "Target" function in the same worksheet! As you can see I don't
have any experience :D

4. Next month (september) I would like to have the amount of items made
saved in G and H. So I have the total amount of items made and broken
for each month. The hard part is because there is made a variable
amount each day and each month, therefore there can be several cells.


--------------------------------------------------------------------------------------------------------------------------------

A5 B5 C5 D5 E
F
2 1 SJA August-31-06 4(total of aug)
2(total broken in aug)

A5 B5 C5 D5
2 1 JHN August-31-06 '(JHN another guy)

--------------------------------------------------------------------------------------------------------------------------------


now I have the same date twice and both of them are in august so they
both must be included for the total of that month.

I just need it to start in auguat to december

many thx