PDA

View Full Version : Retrieve highest number from an array



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

patel
01-23-2013, 12:50 AM
ShtNames(i) = Val(Sheets(i).Name)

you can not have max of a text Array

snb
01-23-2013, 04:37 AM
Sub M_snb()
x=1
for each sh in sheets
x=application.max(x,Val(sh.name))
next

msgbox x
End Sub

or

sub M_snb()
dim sn(sheets.count-1)

for j=1 to sheets.count
sn(j-1)=val(sheets(j).name)
next

msgbox application.max(sn)
End Sub

jolivanes
01-23-2013, 12:44 PM
@patel
Perfect. I was trying to multiply the values by 1 but that didn't work
Thanks patel

@snb.
Perfect also. The 2nd code needed changing (ReDim instead of Dim) maybe because I use Option Explicit. Did not try it without it.
Tanks snb

Thanks and Regards

John