find the lowest values

[frsm]

hi all

i have in the range (Ag1:an1)the names of the months from january- august)in the range (Ag2:An55) ihave numbers in every cell
now in every row for example Ag2:An2 i want to find the values less than 50 then i want to write thier month's names in the cells from Ap2:Aw2
i want to do this with every row from row 2 to 55

i hope if it can be done with vba code it will be better !

thank you

 

[Scott Huish]

Do you want the names in the same respective position in AP2:AW2?

So if the results were for JANUARY and APRIL, it would be JANUARY then 2 blank cells then APRIL?

Why do you need VBA?

 

[frsm]

thank you

i want the results to be with out balanks and , i need any way to do it ,and i prefer if it can be with vba code if it is possible

 

[Krishnakumar]

Hi,

Sub kTest()
Dim a, w(), i As Integer, c As Byte, k  As Byte
a = [ag1:an55].Value
ReDim w(1 To 54, 1 To 8)
For i = 2 To UBound(a, 1)
    For c = 1 To 8
        If a(i, c) < 50 Then k = k + 1: w(i - 1, k) = a(1, c)
    Next: k = 0
Next
[ap2:aw55] = w
End Sub

HTH

 

[frsm]

Thank you very much
it is great and works well
but it considers the blank cells as zero value and calculate it .

now i want it first to test evey row range (ag:an) if all the cells are blanks to give blank result in range (ap:aw)in the same row

thank you very much

 

[Krishnakumar]

Hi,

Replace

If a(i, c) < 50 Then k = k + 1: w(i - 1, k) = a(1, c)

with

If Len(a(i, c)) > 0 And a(i, c) < 50 Then k = k + 1: w(i - 1, k) = a(1, c)

HTH

 

[frsm]

thank you

it is ok

now i consider that the code will order the months accoding to the values from the low value then the greater to the greatest value .
the small value will be in the column Ap the the greater in the column AQ
......etc

 

[frsm]

HELLO ALL

HOW CAN MAKE THIS CODE WORK ONLY IN SPECIFIC SHEET FOR EXAMPLE I WANT IT TO WORK IN SHHET1 ONLY

 

[erik.van.geit]

Hi,
just add the sheetreference

a = Sheets("nameofthesheet").[ag1:an55].Value
or
a = Sheets(1).[ag1:an55].Value

kind regards,
Erik

 

[frsm]

thanks Eric

can u help in solving the privious question , u answered the last one.

thank you

it is ok

now i consider that the code will order the months accoding to the values from the low value then the greater to the greatest value .
the small value will be in the column Ap the the greater in the column AQ
......etc