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

 

[erik.van.geit]

I do not understand what you mean
perhaps Krishnakumar or HOTPEPPER do
else clarify a bit please

 

[frsm]

thank you for you attention


Mr.Krishnakumar made this code for my request

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

now i want this code to order the results from the small value to the greater in the cells (ap2:aw2)

thank you

 

[Krishnakumar]

Hi,

Try,

Sub kTest1()
Dim a, w(), i As Integer, c As Integer, n As Integer, k As Integer, v()
Dim ws1 As Worksheet, ws2   As Worksheet, j As Integer, u   As Integer, l  As Integer
Set ws1 = Sheets("Sheet1")
Application.ScreenUpdating = False
a = ws1.[ag1:an55].Value
l = UBound(a, 1)
u = (l - 1) * 2
ReDim w(1 To u, 1 To 8)
For i = 2 To l
    n = n + 1
    For c = 1 To 8
        w(n, c) = a(1, c): w(n + 1, c) = a(i, c)
    Next: n = n + 1
Next
On Error Resume Next
Set ws2 = Sheets.Add
ws2.Name = "Temp"
On Error GoTo 0
ws2.[a1].Resize(u, 8) = w
For i = 1 To u Step 2
    With Range(ws2.Cells(i, 1), ws2.Cells(i + 1, 8))
        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    End With
Next
a = ws2.[a1].Resize(u, 8).Value
ReDim v(1 To l, 1 To 8)
For i = 1 To u Step 2
    k = k + 1
    For c = 1 To 8
        If Len(a(i + 1, c)) > 0 And a(i + 1, c) < 50 Then
            j = j + 1: v(k, j) = a(i, c)
        End If
    Next: j = 0
Next
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ws1.[ap2:aw55].Resize(l, 8) = v
Application.ScreenUpdating = True
End Sub

HTH

 

[frsm]

thank you vry much

it is completly perfect

thank you

 

[frsm]

hello friends

thank you for you help

this code do the mission perfetly ; i want to add anew condition to this code to be ultra perfet

i want this code to do its mission only with the rows that has the word "month" in column "Gl" in the range "gl2:gl55"
in the rows that hasn't this word in the Gl column to leave blank

thank you

 

[Krishnakumar]

Hi,

Sub kTest2()
Dim a, w(), i As Integer, c As Integer, n As Integer, k As Integer, v()
Dim ws1 As Worksheet, ws2   As Worksheet, j As Integer, u   As Integer, l  As Integer
Set ws1 = Sheets("Sheet1")
Application.ScreenUpdating = False
a = ws1.[ag1:gl55].Value
l = UBound(a, 1)
u = (l - 1) * 2
ReDim w(1 To u, 1 To 8)
For i = 2 To l
    n = n + 1
    For c = 1 To 8
        If UCase(a(i, UBound(a, 2))) = "MONTH" Then
            w(n, c) = a(1, c): w(n + 1, c) = a(i, c)
        Else
            w(n, c) = a(1, c): w(n + 1, c) = ""
        End If
    Next: n = n + 1
Next
On Error Resume Next
Set ws2 = Sheets.Add
ws2.Name = "Temp"
On Error GoTo 0
ws2.[a1].Resize(u, 8) = w
For i = 1 To u Step 2
    With Range(ws2.Cells(i, 1), ws2.Cells(i + 1, 8))
        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    End With
Next
a = ws2.[a1].Resize(u, 8).Value
ReDim v(1 To l, 1 To 8)
For i = 1 To u Step 2
    k = k + 1
    For c = 1 To 8
        If Len(a(i + 1, c)) > 0 And a(i + 1, c) < 50 Then
            j = j + 1: v(k, j) = a(i, c)
        End If
    Next: j = 0
Next
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ws1.[ap2:aw55].Resize(l, 8) = v
Application.ScreenUpdating = True
End Sub

HTH

 

[frsm]

hi all
Thank you Mr.Krishnakumar for this nice work

but sorry the code did't work as i want maybe i didn't explan my issue right
i want if(GL2="month";the code makes its mission ; "") to the end of the range ( GL2:GL55)
thak you

 

[frsm]

sorry
it works well

thank you