.....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"

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" <(E-Mail Removed)> wrote in message
news:322F8114-B17A-4040-A425-(E-Mail Removed)...
> 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
>>
>> .
>>

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

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

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