jolivanes
01-22-2013, 10:23 PM
Related to Mark's code in
http://www.vbaexpress.com/forum/showthread.php?p=284213#post284213
Is it possible to store sheetnames in an array as numbers or convert them to numbers while in the array and then retrieve the highest number?
Mark's code works like a charm of course but I was wondering if there is another way.
I was trying with the below code
Some of the Sheet names are 2009, 2010, 2011 etc
Sub Macro1()
Dim ShtNames() As Variant
Dim i As Variant
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i
Range("B12").Value = Application.Max(ShtNames) '<---- Gives a zero (0)
'Following works OK
For i = 1 To Sheets.Count
Cells(i, 1) = ShtNames(i)
Next i
End Sub
Or am I on a totally wrong track with this?
Thanks and Regards
John
http://www.vbaexpress.com/forum/showthread.php?p=284213#post284213
Is it possible to store sheetnames in an array as numbers or convert them to numbers while in the array and then retrieve the highest number?
Mark's code works like a charm of course but I was wondering if there is another way.
I was trying with the below code
Some of the Sheet names are 2009, 2010, 2011 etc
Sub Macro1()
Dim ShtNames() As Variant
Dim i As Variant
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i
Range("B12").Value = Application.Max(ShtNames) '<---- Gives a zero (0)
'Following works OK
For i = 1 To Sheets.Count
Cells(i, 1) = ShtNames(i)
Next i
End Sub
Or am I on a totally wrong track with this?
Thanks and Regards
John