Vba to fill HIGHEST & LOWEST values

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
I need a vba which can perform below 2 actions:

Action1: Fill F2:F16 with the HIGHEST value respectively of C2:C16 when A1=1
Action2: Fill F2:F16 with the LOWEST value respectively of C2:C16 when A1=1

Note: Values of C2:C16 is constantly changing & gets updated regularly when A1=1 using real time data feeds

How to accomplish please, thanks.
Book2.xlsx
ABCDEFG
10ValueHighLow
2609.20
3537.65
4458.65
5395.00
6330.00
7276.00
8222.00
9180.00
10141.95
11110.25
1287.75
1366.30
1449.90
1536.25
1628.30
H51


I have a code but don't know whether it is correct or not
Code
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim maxVal As Double
    Dim minVal As Double
    Dim i As Integer
    
    If Target.Address = "$A$1" Then
        If Target.Value = 1 Then
            'Find the highest value generated in C2
            maxVal = Range("C2").Value
            
            'Loop through C3:C16 to find the highest value generated
            For i = 3 To 16
                If Range("C" & i).Value > maxVal Then
                    maxVal = Range("C" & i).Value
                End If
            Next i
            
            'Fill F2 with the highest value generated
            Range("F2").Value = maxVal
            
            'Find the lowest value in C2:C16
            minVal = WorksheetFunction.Min(Range("C2:C16"))
            
            'Fill F3:F16 with the lowest value
            For i = 3 To 16
                Range("F" & i).Value = minVal
            Next i
        
        End If
    End If
End Sub
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
hsandeep said:
I need a vba which can perform below 2 actions:

Action1: Fill F2:F16 with the HIGHEST value respectively of C2:C16 when A1=1
Action2: Fill F2:F16 with the LOWEST value respectively of C2:C16 when A1=1

Note: Values of C2:C16 is constantly changing & gets updated regularly when A1=1 using real time data feeds

How to accomplish please, thanks.
Book2.xlsx
ABCDEFG
10ValueHighLow
2609.20
3537.65
4458.65
5395.00
6330.00
7276.00
8222.00
9180.00
10141.95
11110.25
1287.75
1366.30
1449.90
1536.25
1628.30
H51


I have a code but don't know whether it is correct or not
Code
Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim maxVal As Double
    Dim minVal As Double
    Dim i As Integer
   
    If Target.Address = "$A$1" Then
        If Target.Value = 1 Then
            'Find the highest value generated in C2
            maxVal = Range("C2").Value
           
            'Loop through C3:C16 to find the highest value generated
            For i = 3 To 16
                If Range("C" & i).Value > maxVal Then
                    maxVal = Range("C" & i).Value
                End If
            Next i
           
            'Fill F2 with the highest value generated
            Range("F2").Value = maxVal
           
            'Find the lowest value in C2:C16
            minVal = WorksheetFunction.Min(Range("C2:C16"))
           
            'Fill F3:F16 with the lowest value
            For i = 3 To 16
                Range("F" & i).Value = minVal
            Next i
       
        End If
    End If
End Sub
[/CODE
Click to expand...
Rich (BB code): 
Please don't consider above code (it doesn't work)
I have another code...can someone check whether this code will give me desired output?
Code (new)

	
	
	
	
	
	




	

		Rich (BB code):

	

	

		
Sub FillMaxMin()
    Dim i As Long
    Dim maxVal As Variant
    Dim minVal As Variant
    
    
    For i = 2 To 16
        
        'Check if the value in cell A1 is 1
        If Range("A1").Value = 1 Then
            
            'Get the maximum value occurred in cell C(i)
            maxVal = Range("C" & i).Value
            
            'Get the minimum value occurred in cell C(i)
            minVal = Range("C" & i).Value
            
            'Loop through the range C(i+1):C16 to find the maximum and minimum values occurred in the range
            For j = i + 1 To 16
                If Range("C" & j).Value > maxVal Then
                    maxVal = Range("C" & j).Value
                End If
                If Range("C" & j).Value < minVal Then
                    minVal = Range("C" & j).Value
                End If
            Next j
            
            'Fill the maximum and minimum values in the range F(i):F(i) and G(i):G(i)
            Range("F" & i).Value = maxVal
            Range("G" & i).Value = minVal
            
        End If
    Next i
    
End Sub
 
Upvote 0
How about showing us a mocked up solution. You might find you will get more responses when you show us exactly what you want rather then have us try and reverse engineer and guess at what you really want.
 
Upvote 0
alansidman said:
How about showing us a mocked up solution. You might find you will get more responses when you show us exactly what you want rather then have us try and reverse engineer and guess at what you really want.
Click to expand...
@alansidman Thanks for showing your interest...now I feel I would be able to brake this ice.
One output can be:
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2777555
3537.65650450
4458.65500400
5395396394
6330330320
7276290260
8222222220
9180199111
10141.95199100
11110.25120100
1287.75120100
1366.3120100
1449.98040
1536.258040
1628.33020
Sheet1


It is possible that such output is also possible: This may happen when A1 has become=1 just now & the values in C2:C16 has not changed.
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2609.2609.2
3537.65537.65537.65
4458.65458.65458.65
5395395395
6330330330
7276276276
8222222222
9180180180
10141.95141.95141.95
11110.25110.25110.25
1287.7587.7587.75
1366.366.366.3
1449.949.949.9
1536.2536.2536.25
1628.328.328.3
Sheet1


A1=1 in above 2 screenshots & not 0

Note: C2:C16 gets updated/changed very fast. I want to fill F2:F16 with the corresponding Highest values occurred in each C2:C16 when A1=1. Similarly, G2:G16 with the corresponding Lowest values occurred in each C2:C16 when A1=1. It is..Highest value occurred TILL NOW in C2 goes in F2 & Lowest value occurred in C2 TILL NOW goes in G2. Similarly, Highest value occurred in C3 TILL NOW goes in F3 & Lowest value occurred in C3 TILL NOW goes in G3 & so on till Highest value occurred in C16 TILL NOW goes in F16 & Lowest value occurred in C16 TILL NOW goes in G16. Highest & Lowest values, whatever generated in C, should keep on getting filled in F & G columns

Accuracy of the code & speed of the code is also important since the values of C2:C16 fluctuates vary fast.
By default (when A1<>1, the table looks like
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2
3537.65
4458.65
5395
6330
7276
8222
9180
10141.95
11110.25
1287.75
1366.3
1449.9
1536.25
1628.3
Sheet1
 
Last edited:
Upvote 0
hsandeep said:
@alansidman Thanks for showing your interest...now I feel I would be able to brake this ice.
One output can be:
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2777555
3537.65650450
4458.65500400
5395396394
6330330320
7276290260
8222222220
9180199111
10141.95199100
11110.25120100
1287.75120100
1366.3120100
1449.98040
1536.258040
1628.33020
Sheet1


It is possible that such output is also possible: This may happen when A1 has become=1 just now & the values in C2:C16 has not changed.
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2609.2609.2
3537.65537.65537.65
4458.65458.65458.65
5395395395
6330330330
7276276276
8222222222
9180180180
10141.95141.95141.95
11110.25110.25110.25
1287.7587.7587.75
1366.366.366.3
1449.949.949.9
1536.2536.2536.25
1628.328.328.3
Sheet1


A1=1 in above 2 screenshots & not 0

Note: C2:C16 gets updated/changed very fast. I want to fill F2:F16 with the corresponding Highest values occurred in each C2:C16 when A1=1. Similarly, G2:G16 with the corresponding Lowest values occurred in each C2:C16 when A1=1. It is..Highest value occurred TILL NOW in C2 goes in F2 & Lowest value occurred in C2 TILL NOW goes in G2. Similarly, Highest value occurred in C3 TILL NOW goes in F3 & Lowest value occurred in C3 TILL NOW goes in G3 & so on till Highest value occurred in C16 TILL NOW goes in F16 & Lowest value occurred in C16 TILL NOW goes in G16. Highest & Lowest values, whatever generated in C, should keep on getting filled in F & G columns

Accuracy of the code & speed of the code is also important since the values of C2:C16 fluctuates vary fast.
By default (when A1<>1, the table looks like
Referencing.xlsx
ABCDEFG
10ValueHighLow
2609.2
3537.65
4458.65
5395
6330
7276
8222
9180
10141.95
11110.25
1287.75
1366.3
1449.9
1536.25
1628.3
Sheet1
Click to expand...
I have 3 codes: code 1 & code 2 & code 3. But I don't know whether these 3 codes are correct or not? Plus which code is faster in execution (if they are correct)?
Code 1
Rich (BB code): 
Sub FillMaxMin()
    Dim i As Long, j As Long
    Dim maxVal As Variant, minVal As Variant
    Dim cVals As Variant
   
    'Read the values of the range C2:C16 into a variant array
    cVals = Range("C2:C16").Value
   
    'Check if the value in cell A1 is 1
    If Range("A1").Value = 1 Then
       
        'Loop through the cells in the range F2:F16 and fill with the maximum value occurred in the corresponding cell in range C2:C16
        For i = 2 To 16
            maxVal = cVals(i - 1, 1)
            For j = i To 15
                If cVals(j, 1) > maxVal Then
                    maxVal = cVals(j, 1)
                End If
            Next j
            Range("F" & i).Value = maxVal
        Next i
       
        'Loop through the cells in the range G2:G16 and fill with the minimum value occurred in the corresponding cell in range C2:C16
        For i = 2 To 16
            minVal = cVals(i - 1, 1)
            For j = i To 15
                If cVals(j, 1) < minVal Then
                    minVal = cVals(j, 1)
                End If
            Next j
            Range("G" & i).Value = minVal
        Next i
   
    End If
   
End Sub


Code 2
Rich (BB code): 
Sub FillMaxMin()
    Dim i As Long, j As Long
    Dim maxVal As Variant, minVal As Variant
    Dim cVals() As Variant, fVals() As Variant, gVals() As Variant
   
    'Read the values of the range C2:C16 into a variant array
    cVals = Range("C2:C16").Value
   
    'Initialize the arrays for F2:F16 and G2:G16
    ReDim fVals(1 To 15, 1 To 1)
    ReDim gVals(1 To 15, 1 To 1)
   
    'Check if the value in cell A1 is 1
    If Range("A1").Value = 1 Then
       
        'Loop through the cells in the range F2:F16 and fill with the maximum value occurred in the corresponding cell in range C2:C16
        For i = 1 To 15
            maxVal = cVals(i, 1)
            For j = i + 1 To 15
                If cVals(j, 1) > maxVal Then
                    maxVal = cVals(j, 1)
                End If
            Next j
            fVals(i, 1) = maxVal
        Next i
       
        'Loop through the cells in the range G2:G16 and fill with the minimum value occurred in the corresponding cell in range C2:C16
        For i = 1 To 15
            minVal = cVals(i, 1)
            For j = i + 1 To 15
                If cVals(j, 1) < minVal Then
                    minVal = cVals(j, 1)
                End If
            Next j
            gVals(i, 1) = minVal
        Next i
   
        'Write the values back to the worksheet
        Range("F2:F16").Value = fVals
        Range("G2:G16").Value = gVals
   
    End If
   
End Sub
Code 3
Rich (BB code): 
Sub FillMaxMin()
    Dim i As Long, j As Long
    Dim maxVal As Variant, minVal As Variant
    Dim cVals As Variant
    Dim maxVals() As Variant, minVals() As Variant
    
    'Read the values of the range C2:C16 into a variant array
    cVals = Range("C2:C16").Value
    
    'Check if the value in cell A1 is 1
    If Range("A1").Value = 1 Then
        
        'Fill the array with the maximum and minimum values
        ReDim maxVals(1 To 15, 1 To 1)
        ReDim minVals(1 To 15, 1 To 1)
        For i = 1 To 15
            maxVal = WorksheetFunction.Max(Range("C" & (i + 1) & ":C16"))
            minVal = WorksheetFunction.Min(Range("C" & (i + 1) & ":C16"))
            maxVals(i, 1) = maxVal
            minVals(i, 1) = minVal
        Next i
        
        'Write the results to the worksheet using the array
        Range("F2:F16").Value = maxVals
        Range("G2:G16").Value = minVals
    
    End If
    
End Sub



Someone please help
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Doubt with Offset
Replies
6
Views
123
Ben AFF
B
H
  • Question
VBA to Create a speaker object
Replies
0
Views
118
hsandeep
H
L
  • Question
Code stopped working
Replies
1
Views
182
Micron
M
H
  • Question
Code to fill the highest & lowest value from a range in 2 ranges
2
Replies
14
Views
685
hsandeep
H
M
  • Question
Loop in macro calculating twice instead of once.
Replies
1
Views
358
RoryA
RoryA

Forum statistics

Threads
1,215,151
Messages
6,123,319
Members
449,094
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top