Can I loop this?

[Qaspec]

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

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

 

[Paul C]

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

 

[Qaspec]

Joel

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

The highlight shows on -

StartDate = DateValue("020110")

 

[Bernard Liengme]

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

 

[joel]

sorry, Datevalue only takes string values that VBA recognizes as vali
dates. VBA won't recognize 020110, but wil recognize a lot of differen
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

 

[Dave Peterson]

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

 

[joel]

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

 

[Dave Peterson]

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

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/showthread.php?t=193373

http://www.thecodecage.com/forumz

 

[joel]

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

 

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