| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max and Min VBA Statement
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Max and Min VBA Statement
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
| Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
| Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Hybrid Mode
