.....or is there a better way to write this?


Private Sub CB1_Click()



With ThisWorkbook.Worksheets("February")
.Range("B5").Value = Worksheets("020110").Range("B5").Value +
Worksheets("020210").Range("B5").Value


End With
End Sub

The Worksheets go from "020110" to "022710"

Dates are number with the first day Jan 1, 1900 and each day equal 1.
this code should work


Private Sub CB1_Click()

StartDate = DateValue("020110")
EndDate = DateValue("022710")


With ThisWorkbook.Worksheets("February")
For MyDate = StartDate To EndDate
Yesterday = Format(MyDate - 1, "MMDDYY")
DateStr = Format(MyDate, "MMDDYY")

.Range("B5").Value = _
Worksheets(Yesterday).Range("B5").Value + _
Worksheets(DateStr).Range("B5").Value
Next MyDate
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

You could loop it something like this

For A=1 to 27
If A<10 Then
tgtname="020" & Cstr(A) & "10"
Else
tgtname="02" & Cstr(A) & "10"
End If

Sheets("February").Range("B5") = Sheets("February").Range("B5") +
Sheets(tgtname).Range("B5")

Next A
--
If this helps, please remember to click yes.


"Qaspec" wrote:

....or is there a better way to write this?


Private Sub CB1_Click()



With ThisWorkbook.Worksheets("February")
.Range("B5").Value = Worksheets("020110").Range("B5").Value +
Worksheets("020210").Range("B5").Value


End With
End Sub

The Worksheets go from "020110" to "022710"

Joel

I get a Type Mismatch Error when i try to execute.

The highlight shows on -

StartDate = DateValue("020110")


"joel" wrote:


Dates are number with the first day Jan 1, 1900 and each day equal 1.
this code should work


Private Sub CB1_Click()

StartDate = DateValue("020110")
EndDate = DateValue("022710")


With ThisWorkbook.Worksheets("February")
For MyDate = StartDate To EndDate
Yesterday = Format(MyDate - 1, "MMDDYY")
DateStr = Format(MyDate, "MMDDYY")

.Range("B5").Value = _
Worksheets(Yesterday).Range("B5").Value + _
Worksheets(DateStr).Range("B5").Value
Next MyDate
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

.

So did I. Maybe because we both use universal (not USA) date format
I replaced the code with
StartDate = DateValue("Feb 1 2010")
EndDate = DateValue("Feb 27 2010")
and the error when away
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Qaspec" wrote in message
...
Joel

I get a Type Mismatch Error when i try to execute.

The highlight shows on -

StartDate = DateValue("020110")


"joel" wrote:


Dates are number with the first day Jan 1, 1900 and each day equal 1.
this code should work


Private Sub CB1_Click()

StartDate = DateValue("020110")
EndDate = DateValue("022710")


With ThisWorkbook.Worksheets("February")
For MyDate = StartDate To EndDate
Yesterday = Format(MyDate - 1, "MMDDYY")
DateStr = Format(MyDate, "MMDDYY")

.Range("B5").Value = _
Worksheets(Yesterday).Range("B5").Value + _
Worksheets(DateStr).Range("B5").Value
Next MyDate
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

.

sorry, Datevalue only takes string values that VBA recognizes as valid
dates. VBA won't recognize 020110, but wil recognize a lot of different
strings like
2/1/2010 and any other string values that a worksheet will accept.

I also noticed I did move down the rows for each entry

Private Sub CB1_Click()

StartDate = DateValue("Feb 1 2010")
EndDate = DateValue("Feb 27 2010")

With ThisWorkbook.Worksheets("February")
RowCount = 5
For MyDate = StartDate To EndDate
Yesterday = Format(MyDate - 1, "MMDDYY")
DateStr = Format(MyDate, "MMDDYY")

.Range("B" & RowCount).Value = _
Worksheets(Yesterday).Range("B" & RowCount).Value + _
Worksheets(DateStr).Range("B" & RowCount).Value
RowCount = rowcount + 1
Next MyDate
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

I would drop the DateValue() stuff and just use dateserial:

StartDate = DateValue("020110")
becomes
StartDate = Dateserial(2010,2,1) 'ymd order

And I'd declare my variables, too:

Dim StartDate as Date





Qaspec wrote:

Joel

I get a Type Mismatch Error when i try to execute.

The highlight shows on -

StartDate = DateValue("020110")

"joel" wrote:


Dates are number with the first day Jan 1, 1900 and each day equal 1.
this code should work


Private Sub CB1_Click()

StartDate = DateValue("020110")
EndDate = DateValue("022710")


With ThisWorkbook.Worksheets("February")
For MyDate = StartDate To EndDate
Yesterday = Format(MyDate - 1, "MMDDYY")
DateStr = Format(MyDate, "MMDDYY")

.Range("B5").Value = _
Worksheets(Yesterday).Range("B5").Value + _
Worksheets(DateStr).Range("B5").Value
Next MyDate
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

.


--

Dave Peterson

Dave: I agree that using Dateserial is a good idea if you are using the
workbook internationally, but if you are only using the workbook in one
coutry like the USA then Datavalue will always work.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

I still don't think it's a good idea.

joel wrote:

Dave: I agree that using Dateserial is a good idea if you are using the
workbook internationally, but if you are only using the workbook in one
coutry like the USA then Datavalue will always work.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

--

Dave Peterson

I don't like when I get a posted worksheet data from England that have
to fix the dates before I can paste data into a worksheet, but there
isn't much I can do about the problem. It would be nice if could format
the column on the worksheet that you are putting in a foreign date and
excel would automatically do the conversion. Microsoft doesn't have any
solution to this problem.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

And it surely makes it easier to understand what 01/02/03 means when it's
formatted in an umabiguous date format.

I'm still learning that lesson <vbg.



joel wrote:

I don't like when I get a posted worksheet data from England that have
to fix the dates before I can paste data into a worksheet, but there
isn't much I can do about the problem. It would be nice if could format
the column on the worksheet that you are putting in a foreign date and
excel would automatically do the conversion. Microsoft doesn't have any
solution to this problem.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193373

http://www.thecodecage.com/forumz

--

Dave Peterson