Making the macro more flexible?
 

Hi,

Could you please help me make this code more flexible?

Basically right now it performs the procedure only for the sheet name
943. But i have 66 sheets that i want it to take care of and they ar
listed on sheet 1 of my workbook (they are not consecutive number
however, so the macro has to go to sheet1 and loop through an array o
sheet numbers/names and perform the below functions on them. Hope thi
doesnt sound too confusing.

Thanks so much.

Regards,
Maria

Sub CommandButtonDRG_Click()


Dim i As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

Dim SheetNumber As String

SheetNumber = "943"

startDate = Sheets(SheetNumber).Range("c2").Value
endDate = Sheets(SheetNumber).Range("e2").Value
Sheets(SheetNumber).Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Sheets(SheetNumber).Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Sheets(SheetNumber).Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
End

End Su

--
marias
-----------------------------------------------------------------------
mariasa's Profile: http://www.excelforum.com/member.php...fo&userid=3172
View this thread: http://www.excelforum.com/showthread.php?threadid=52312

Making the macro more flexible?
 

assume the sheet names are listed in A1 to A whatever in sheet1 with no blank
cells in the list (adjust to fit actual situation)

Sub CommandButtonDRG_Click()


Dim i As Long
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date
Dim rng as Range, cell as Range

Dim SheetNumber As String
with Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with

SheetNumber = "943"
for each cell in rng
SheetNumber = rng.Text
sheets(sheetNumber).Activate
startDate = Sheets(SheetNumber).Range("c2").Value
endDate = Sheets(SheetNumber).Range("e2").Value
Sheets(SheetNumber).Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Sheets(SheetNumber).Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Sheets(SheetNumber).Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
End

Next

End Sub

--
Regards,
Tom Ogilvy



"mariasa" wrote:


Hi,

Could you please help me make this code more flexible?

Basically right now it performs the procedure only for the sheet named
943. But i have 66 sheets that i want it to take care of and they are
listed on sheet 1 of my workbook (they are not consecutive numbers
however, so the macro has to go to sheet1 and loop through an array of
sheet numbers/names and perform the below functions on them. Hope this
doesnt sound too confusing.

Thanks so much.

Regards,
Maria

Sub CommandButtonDRG_Click()


Dim i As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

Dim SheetNumber As String

SheetNumber = "943"

startDate = Sheets(SheetNumber).Range("c2").Value
endDate = Sheets(SheetNumber).Range("e2").Value
Sheets(SheetNumber).Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Sheets(SheetNumber).Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Sheets(SheetNumber).Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
End

End Sub


--
mariasa
------------------------------------------------------------------------
mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
View this thread: http://www.excelforum.com/showthread...hreadid=523123

Making the macro more flexible?
 

Tom, thanks for the suggestion. I tried it out and it gives me th
error, 'invalid use of null' :-(

Does anyone else have any suggestions on how this could work?

Thanks guys!!!

Best,
Mari

--
marias
-----------------------------------------------------------------------
mariasa's Profile: http://www.excelforum.com/member.php...fo&userid=3172
View this thread: http://www.excelforum.com/showthread.php?threadid=52312

Making the macro more flexible?
 

there was a typo in the code:

change
SheetNumber = rng.Text
to
SheetNumber = cell.Text

sorry you couldn't figure it out :-(
--
Regards,
Tom Ogilvy

"mariasa" wrote in
message ...

Tom, thanks for the suggestion. I tried it out and it gives me the
error, 'invalid use of null' :-(

Does anyone else have any suggestions on how this could work?

Thanks guys!!!

Best,
Maria


--
mariasa
------------------------------------------------------------------------
mariasa's Profile:
http://www.excelforum.com/member.php...o&userid=31726
View this thread: http://www.excelforum.com/showthread...hreadid=523123