Can I loop this?
 

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

Can I loop this?
 

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

Can I loop this?
 

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"

Can I loop this?
 

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

.

Can I loop this?
 

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

.

Can I loop this?
 

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

Can I loop this?
 

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

Can I loop this?
 

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

Can I loop this?
 

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

Can I loop this?
 

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

Can I loop this?
 

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