Taking the Max Value from a varying range of data

[Luke199]

Hi,

i have a data set I.e.

a1 50 XX
a2 62 XXX
a3 6
a4 67
a5 69
a6 68
a7 84
a8 51

using VBA, i am wanting to take the Max value of Range(a1:a4) insert the value into XX then take the next set of 4 cells [Range(a5:a8)] and insert the value into XXX. this sequence needs looping till the end of the data set.

Could anybody be of assistance?

Cheers
Luke

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG05Apr58
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Cells(Rows.Count, "A").End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 1 To Lst [COLOR="Navy"]Step[/COLOR] 4
    c = c + 1
    Cells(c, 2) = Application.Max(Range("A" & n).Resize(4).Value)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Luke199]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG05Apr58
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Lst = Cells(Rows.Count, "A").End(xlUp).Row
[COLOR=Navy]For[/COLOR] n = 1 To Lst [COLOR=Navy]Step[/COLOR] 4
    c = c + 1
    Cells(c, 2) = Application.Max(Range("A" & n).Resize(4).Value)
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks MickG

 

[MickG]

You're welcome