• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Capture high and low values

T

Techtrend

Member
Hello Team

Need to Capture the high and low values in D and E col

Details are mentioned in the attached document.

Thanks in Advance
Naren
 

Attachments

  • NIfty-HIGH-LOW.xls
    19.5 KB · Views: 7
Need more info.

Does the value in B2 & C2 keep changing. Or are new values added below?

If B2 & C2 are changed, it's not possible via formula and would require VBA solution.
 
1] To capture the highest value with dynamic range from col B, formula in D2 :

=MAX(B2:INDEX(B:B,MATCH(9.9E+307,B:B)))

2] To capture the lowest value with dynamic range from col C, formula in E2 :

=MIN(C2:INDEX(C:C,MATCH(9.9E+307,C:C)))

Edit : Maybe someone will suggust using :

=MAX(B:B)

and

=MIN(C:C)

But, it will involve 1 million cell calculation and affect the computer performance causing slow-down.

Regards
Bosco
 
Chihiro said:
Need more info.

Does the value in B2 & C2 keep changing. Or are new values added below?

If B2 & C2 are changed, it's not possible via formula and would require VBA solution.
Click to expand...


Hi

Yes,the value in B2 & C2 keep changing,
Can you please help me with Macros,
every 15 minutes the data is copied from D2:E2
One blow the other.
After the first 15 minutes the data from B2 : C 2 is copied to D3:E3
and goes on like that.

The data keeps changing from 9-3.30.
We can do a manual start and stop for this to work.

Thanks
Naren
 
bosco_yip said:
1] To capture the highest value with dynamic range from col B, formula in D2 :

=MAX(B2:INDEX(B:B,MATCH(9.9E+307,B:B)))

2] To capture the lowest value with dynamic range from col C, formula in E2 :

=MIN(C2:INDEX(C:C,MATCH(9.9E+307,C:C)))

Edit : Maybe someone will suggust using :

=MAX(B:B)

and

=MIN(C:C)

But, it will involve 1 million cell calculation and affect the computer performance causing slow-down.

Regards
Bosco
Click to expand...

Mr.Bosco

The value in B2 and C2 keeps changing ,
The max and min will not work as the data keeps changing in the same place.

Both the formulas i tried it doesnt work.

regards
naren
 
Wait... data is copied from D2:E2?

Isn't that where you want to store the high & low?

It would be better, if you can upload sample with demonstration of how numbers interact with each other.

I'm having hard time visualizing how your application works.
 
Chihiro said:
Wait... data is copied from D2:E2?

Isn't that where you want to store the high & low?

It would be better, if you can upload sample with demonstration of how numbers interact with each other.

I'm having hard time visualizing how your application works.
Click to expand...
n the sample,


Hello

Please check the attached document have give sample for macros,
In case if u still need clarification do let me know.

Thanks
Naren
 

Attachments

  • NIfty-HIGH-LOW-1.xlsx
    11.2 KB · Views: 4
One more clarification. Is there code that changes B2:C2 value?

If so, post code. It would help in suggesting appropriate code/variable to use.
 
I know it's not done via formula. I assume there is code that does the job. I need to know what variables are used to write info and what triggers the code to run and how often?

This will impact subsequent code to write info to D & E column.
 
Chihiro said:
I know it's not done via formula. I assume there is code that does the job. I need to know what variables are used to write info and what triggers the code to run and how often?

This will impact subsequent code to write info to D & E column.
Click to expand...

Please check the high low formula used,
the values in b2 and c2 will not come for you as the data comes from the terminal which when we keep it open keeps getting updated.

The value keeps changing in real time
every sec the value gets updated and keeps changing In B2 and C2
 

Attachments

  • NIfty-HIGH-LOW-1.xlsx
    11.7 KB · Views: 11
So it's calculated value via RTD function then.

This will be bit more complicated than when the values are pulled via code. I'm going into a meeting now. I'll have sample code for you tomorrow.
 
This is probably the simplest way to accomplish what you are looking for.
Note that TimeValue only takes text string.

Code: 
Sub IntiProcess()
Dim cel As Range
Dim exTime As String

For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("F2:F27")
    exTime = cel.Text
    Application.OnTime TimeValue(exTime), "'RecValue """ & exTime & """'"
Next
End Sub
Sub RecValue(exTime As String)
Dim cel As Range
With ThisWorkbook.Worksheets("Sheet1")
For Each cel In .Range("F2:F27")
    If exTime = cel.Text Then
        cel.Offset(, -2).Value = .Range("B2").Value
        cel.Offset(, -1).Value = .Range("C2").Value
    End If
Next
End With
End Sub
 
Chihiro said:
This is probably the simplest way to accomplish what you are looking for.
Note that TimeValue only takes text string.

Code: 
Sub IntiProcess()
Dim cel As Range
Dim exTime As String

For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("F2:F27")
    exTime = cel.Text
    Application.OnTime TimeValue(exTime), "'RecValue """ & exTime & """'"
Next
End Sub
Sub RecValue(exTime As String)
Dim cel As Range
With ThisWorkbook.Worksheets("Sheet1")
For Each cel In .Range("F2:F27")
    If exTime = cel.Text Then
        cel.Offset(, -2).Value = .Range("B2").Value
        cel.Offset(, -1).Value = .Range("C2").Value
    End If
Next
End With
End Sub
Click to expand...


HI

I am Nill in Macros,can you please ADD this in my excel and share the excel please.

Sorry for the in convenience.

Naren
 
Hit Alt+F11 to launch VBE (editor).

Right click in Project pane. Insert->Module
Copy and paste the code I gave you.

On the sheet insert command button. Assign Macro and assign "IntiProcess" to it.

Without trial and error, you'll never learn.
 
Chihiro said:
Hit Alt+F11 to launch VBE (editor).

Right click in Project pane. Insert->Module
Copy and paste the code I gave you.

On the sheet insert command button. Assign Macro and assign "IntiProcess" to it.

Without trial and error, you'll never learn.
Click to expand...

Hi

Please see if i have done it right.
It doesnt work might be some small hicups to complete it.
Can you please help me on the same.

Narendra
 

Attachments

  • NIfty-HIGH-LOW-Macro.xlsm
    16.1 KB · Views: 3
You must log in or register to reply here.
Back
Top