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

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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;
Thanks you very much I really appreciatte it!!.
the macro is working PERFECTLY!!!!!!!

Kind regards.
Maperalia

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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;
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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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;
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" wrote:

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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;
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" wrote:

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

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in
message
...
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;
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


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"maperalia" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy


"maperalia" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"maperalia" wrote in
message
...
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