Max and Min VBA Statement

[maperalia]

I have the following formulas setup in the cells:

K10=IF(A1=0,"",MIN(IF(RIGHT($A$1:$A$30000)="A",--(LEFT($A$1:$A$30000,LEN($A$1:$A$30000)-1)))))

and

L10=IF(A1=0,"",MAX(IF(RIGHT($A$1:$A$30000)="A",--(LEFT($A$1:$A$30000,LEN($A$1:$A$30000)-1)))))

It is working Ok!, however, takes too much time to run it because I am using
30,000 rows.

I wonder if there is any way if you can help me to get the statement to make
it run as a VBA and make it until last row is empty because as you see in my
formula I am calculating for 30,000 rows and in addition of the time is been
taking to run it, I am concern if my data exceed this number and I have to
change the formula all the time.

Thanks in advance.
Maperalia

 

[Tom Ogilvy]

You mean as a macro to run.

Option Explicit
Sub CalculateMinAndMax()
Dim rng As Range, l As Long
Dim lMin As Long, lMax As Long
Dim v As Variant, i As Long
Dim s As String
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
v = rng.Value
lMin = 1000000
lMax = -1000000
For i = LBound(v) To UBound(v)
s = v(i, 1)
If UCase(Right(s, 1)) = "A" Then
l = Val(s)
If lMin > l Then
lMin = l
End If
If lMax < l Then
lMax = l
End If
End If
Next
Range("K1").Value = lMin
Range("L1").Value = lMax
End Sub

 

[maperalia]

Tom;
Thanks you very much I really appreciatte it!!.
the macro is working PERFECTLY!!!!!!!

Kind regards.
Maperalia

 

[maperalia]

Tom;
One last question. I have tried to use the macro for the following:
1.- Column "B" with the value with "B" text and get the results at K2,L2
2.- Column "C" with the value with "C" text and get the results at K3, L3

And I have gotten just at K2,L2 and K3,L3 min=1000000 and max=-1000000

Obviously, I have made the necessary adjustments in the macro the match the
column B and C with the texts B and C respectively.

Could you please tell if the macro can be adjusted in the way I mentioned or
I have to add and additionalline to make it work?

Thanks in advance.
Maperalia.

 

[Tom Ogilvy]

Assuming B and C will end on the same row as column A,

Option Explicit
Sub CalculateMinAndMax()
Dim rng As Range, l As Long
Dim lMin As Long, lMax As Long
Dim v As Variant, i As Long
Dim s As String, ii as Long
Dim vVal as Variant

vVal = Array("A","B","C")
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
for ii = 1 to 3
s2 = vVal(lbound(vVal) + ii - 1)
v = rng.offset(0,ii - 1).Value
lMin = 1000000
lMax = -1000000
For i = LBound(v) To UBound(v)
s = v(i, 1)
If UCase(Right(s, 1)) = s2 Then
l = Val(s)
If lMin > l Then
lMin = l
End If
If lMax < l Then
lMax = l
End If
End If
Next i
Next ii
Range("K1").offset(ii - 1,0).Value = lMin
Range("L1").offset(ii - 1,0).Value = lMax
End Sub

 

[maperalia]

Tom;
Thanks for your quick response.
I ran your macro with the following data:

1a
2a
3a
1b
2b
3b
1c
2c
3c
Where the "a" is located in the column A, "b" is located in the column B
and, "c" is located in the column C. However, I could not get the results at:

K1(min) L1(max) for the column A
K2(min) L2(max) for the column B and
K3(min) L3(max) for the column C

I just gotten 100000 and -100000 at K4L4.
Could you please tell me how can I adjust it?

Thanks very much.

Maperalia

 

[Tom Ogilvy]

There was an error in the code. I ran the below with the data you show in
Column A and reproduced in Column B and Column C.

It produce 1 in K1, K2, K3 and 3 in L1, L2, and L3.

If it doesn't work for you, then I guess I haven't understood how your data
is organized.

Sub CalculateMinAndMax()
Dim rng As Range, l As Long
Dim lMin As Long, lMax As Long
Dim v As Variant, i As Long
Dim s As String, ii As Long
Dim vVal As Variant, s2 As String

vVal = Array("A", "B", "C")
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
For ii = 1 To 3
s2 = vVal(LBound(vVal) + ii - 1)
v = rng.Offset(0, ii - 1).Value
lMin = 1000000
lMax = -1000000
For i = LBound(v) To UBound(v)
s = v(i, 1)
If UCase(Right(s, 1)) = s2 Then
l = Val(s)
If lMin > l Then
lMin = l
End If
If lMax < l Then
lMax = l
End If
End If
Next i
Range("K1").Offset(ii - 1, 0).Value = lMin
Range("L1").Offset(ii - 1, 0).Value = lMax
Next ii

End Sub


If you want to process column A once each for the right letters A, then
B, then C, then here is code for that

Sub CalculateMinAndMax()
Dim rng As Range, l As Long
Dim lMin As Long, lMax As Long
Dim v As Variant, i As Long
Dim s As String, ii As Long
Dim vVal As Variant, s2 As String

vVal = Array("A", "B", "C")
Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
v = rng.Value

For ii = 1 To 3
s2 = vVal(LBound(vVal) + ii - 1)
lMin = 1000000
lMax = -1000000
For i = LBound(v) To UBound(v)
s = v(i, 1)
If UCase(Right(s, 1)) = s2 Then
l = Val(s)
If lMin > l Then
lMin = l
End If
If lMax < l Then
lMax = l
End If
End If
Next i
Range("K1").Offset(ii - 1, 0).Value = lMin
Range("L1").Offset(ii - 1, 0).Value = lMax
Next ii

End Sub

 

[maperalia]

Tom;
Thanks for your quick response.
Indeed, the macro it is working PERFECTLY!!!.
In order that macro to run properly I just realize that all the data must
start for A1, B1 and C1.
Unfortunately, my data does not start in that way because was splited. For
example; for the number with the text:
• “a†will start in A1 and finish in A10
• “ b†will start in A11 and finish in A20
• “ c†will start in A21 and finish in 30

I do apologize for not explained properly. Anyway, I wonder if you can
adjust the program to run it in the condition I mentioned above because I
have to move the data to the top to make it run.

Thanks very much and really appreciate your taking your time to help me.

Kind regards.
Maperalia.

 

[Tom Ogilvy]

I posted 2 macros in my last post. The second should work on your data with
all the data in column A as you have just described.

 

[maperalia]

Tom;
I found it. I sorry I did not see it before. I ran and it is working
perfectly!!!!
Thanks for your help I really appreciatte it!!

Kind regards.
Maperalia