Looping through a range

[stephenmbell]

I am automating the creation of an excel spreadsheet from a microsoft access database. I have googled and failed to find a good example of looping through a range (atleast, the way I want to do it).

the spreadsheet is showing product sales over a 9 day period. At the beginning of displaying each individual products information, I would like to display the date (both in m/d and Ddd e.g 1/30 & Wed).

So I would like to write a procedure that you pass the workbook and a range, and it outputs the date to the range.

Calling OutputDate(wbk, "A4:H5") would basically do the following:

A B C D E F G H
4 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8
5 TUE WED THU FRI SAT SUN MON TUE

I am aware of how to output the date the way I want to, but I am looking for a way to loop the range, without necessarily knowing what row or column I am in.

Hope this makes sense

Thank you in advance.

 

[SkipVought]

Hi,

"...I am looking for a way to loop the range..."

dim r as range
with activesheet
  for each r in .range(.[A1], .[A1].end(xltoright))
     
  next

end with

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[ck1999]

If you want to specify for

So I would like to write a procedure that you pass the workbook and a range, and it outputs the date to the range.[/code]

you could use something like

Sub outputdate(sh As String, r As Range, startdate As Date)

   For vcol = 0 To 8
       Sheets(sh).Cells(r.Row, r.Column + vcol).Value = DateAdd("y", vcol, startdate)
       Sheets(sh).Cells(r.Row + 1, r.Column + vcol).Value = Format(DateAdd("y", vcol, startdate), "ddd")
   Next vcol

Sheets(sh).Range(r, Cells(r.Row, r.Column + vcol)).NumberFormat = "m/d"

End Sub

Sub init()
Dim vdate As Date
Dim vsheet As String
vsheet = "sheet2"
vdate = #1/30/2008#
Call outputdate(vsheet, Sheets(vsheet).Range("a4"), vdate)
End Sub

Hope this helps

ck1999