Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to capture additional highest and lowest value for additional Columns

0

Hi Variatus,

First of all, I would like to appreciate your work done in attached excel sheet which works wonders. I must say, You are a real excel genius. Thank you.

Further, I am looking for additional highest and lowest value for additional respective columns ( i.e. Column C and result in Column G & Column H).

I tried several attempts to copy the codes to add the same, but understand its difficult to copy and failing to do the same.

Hence, Can I request you to extend your help for the same.

Regards 

Revised Question:

Hi Variatus,

You have already implemented High low for 1st instance (Factory 1).

Thank you for the same.

Further, I am looking for 2nd high low for 2nd instance (Factory 2) i.e. result in Column G and Column H of Column D Highlighted in yellow.

Please find attached excel sheet for your ready reference.

Request you to please extend your support with the coding for the same.

Regards

Answer
Discuss

Discussion

Hi Sunil,
As a matter of format, there is no need to preserve a previous version  of a question subsequently judged faulty. You might just have removed or replaced the part that needed improvement.
The same applies to the answer.
Variatus (rep: 4889) Jun 20, '21 at 3:13 am
Add to Discussion

Answers

0
Selected Answer

The addition requires extra specification of columns.

Enum Nws                                        ' worksheet navigation (Sheet1)
    ' 267 (ex 206)
    NwsFirstRow = 5                             ' change to suit
    NwsAvg1 = 3                                 ' change to suit (3 =column C)
    NwsAvg2                                     ' undefined = 1 larger than preceding
    NwsMax1 = 5                                 ' change to suit (5 =column E)
    NwsMin1
    NwsMax2 = 7                                 ' change to suit (7 =column G)
    NwsMin2                                     ' NwsMin2 must be the last column here defined
End Enum

and the main macro must be expanded to process the extra data.

Private Sub MyMacro()
    ' 267 (ex 206 - 01 Jul 2021)

    Dim Rl          As Long                 ' last used row in column A
    Dim Arr         As Variant              ' read data from the worksheet
    Dim R           As Long                 ' loop counter: sheet rows
    Dim Ra          As Long                 ' array row number

    'Macro code that you want to run.
    With Worksheets("Sheet1")               ' Change name to suit
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        Arr = .Range(.Cells(NwsFirstRow, 1), .Cells(Rl, NwsMin2)).Value

        For R = NwsFirstRow To Rl
            Ra = R - NwsFirstRow + 1
            RecordMinMax Arr(Ra, NwsAvg1), Arr(Ra, NwsMax1), .Cells(R, NwsMax1), True
            RecordMinMax Arr(Ra, NwsAvg1), Arr(Ra, NwsMin1), .Cells(R, NwsMin1), False
            RecordMinMax Arr(Ra, NwsAvg2), Arr(Ra, NwsMax2), .Cells(R, NwsMax2), True
            RecordMinMax Arr(Ra, NwsAvg2), Arr(Ra, NwsMin2), .Cells(R, NwsMin2), False
        Next R
    End With

    ' enable this line of you want to savwe the change:-
    'ThisWorkbook.Save

    'Calls the timer macro so it can be run again at the next interval.
    SetTimer
End Sub

Private Sub RecordMinMax(ByVal NewVal As Variant, _
                         OldVal As Variant, _
                         Target As Range, _
                         IsMax As Boolean)
    ' 267 (ex 206 - 01 Jul 2021)

    With Target
        If Not IsEmpty(OldVal) Then
            If IsMax Then
                NewVal = WorksheetFunction.Max(NewVal, OldVal)
            Else
                NewVal = WorksheetFunction.Min(NewVal, OldVal)
            End If
        End If
        If NewVal <> OldVal Then .Value = NewVal
    End With
End Sub

All of the above is demonstrated in the attached workbook.

Discuss

Discussion

Hi Variatus,
Thanks for your quick response. 
Can you please guide me how to add one more criteria of high low coding to the existing excel sheet.
You can refer to my main message.
Regards
SunilA (rep: 56) Jun 17, '21 at 12:44 pm
Hi Variatus,
Thank you for your response.
I want the High in Cells E5 [& down] and the Low in cells F5 [& down]
I also want another High in cells G5 [& down] and the Low in cells H5 [& down].
Hence, I am looking for two sets of High low results.
Please refer to excel sheet highlighted in yellow.
Trust you understand the requirement.
Please help me for the solution.
Regards
SunilA (rep: 56) Jun 18, '21 at 12:37 am
Hi Variatus,
Thank you for your message.
Sorry for inconvenience for unable to make you understand. 
I have given an example of above to make you understand of the requirement.
In short, I am looking for VBA coding for two different sets of High low of two different Heading of which one set is already provided by you in the workbook.. Further, I am looking for addition of coding for another set of high low in same workbook which I am failing to figure out to incorporate.
Please create coding for two sets of high low in one workbook.
You can select the cells as per your wish.
Just I am looking for two sets of high low in one workbook. 
If you want I can again send the new excel sheet with exact requirement.
I am trying to make you understand the same requirement from my initial main message.
Hence, seek your extended help for the solution. 
Regards
SunilA (rep: 56) Jun 18, '21 at 1:12 pm
Hi Variatus,
I am really sorry for the inconvenience caused for the misunderstanding.
Thanks for keeping patience.
I understand the error.
Sorry for the communication gap from my side.
Can I reframe the question in proper manner in this thread message or should I send you a fresh new question.
Please advise. 
Regards
SunilA (rep: 56) Jun 19, '21 at 3:33 am
Thanks Variatus. Will rephrase the question with new attached excel sheet.
Regards
SunilA (rep: 56) Jun 19, '21 at 10:07 pm
Hi Variatus,
Thank you for your patience.
I have revised question to the main message and have attached revised excel sheet for your ready reference.
Request you for your extend help for said subject.
Regards
SunilA (rep: 56) Jun 20, '21 at 1:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login