Data log - sorting for two parameters and divide up in intervals. Plot specific log files

[Jharming]

Hi,

First time here and just started to look into Excel and VBA due to new challenges.

The deal is:

I have a data series measuring over time. (x-axis)
To this series I have 20 something temperatures loggers. Two of those temperature loggers measuring parameters I want to sort for.
e.g.

I measure over 5 hours.
The two parameters i set I can be changed during this time period.

First hour Tm=50, Ta=70
Second hour Tm=70, Ta=70
Third hour Tm=90, Ta=70
Fourth hour Tm=90, Ta=50
Fifth hour Tm=70, Ta=40

The logger logs 20 other temperature measurements.

What I want to do is to have a chart where I can choose which loggers I want to see and which set points.

To do this I have written a basic VBA code where I simply hide/unhide the rows and columns I want to see by using TRUE/FALSE checkbox.

To make this work I need to look into the data first and determine manually when Tm, Ta or both changes and write down the specific row.
I have written in the name for each checkbox Tm and Ta. Furthermore I have written for each other logger a checkbox that hide/unhide the specific logger.



I am sure this can be done much more convenient by inspecting the Tm and Ta using an algorithm and thereby extract the rows and values (average) for Tm and Ta and set them in the checkboxes.

 

[pbornemeier]

Thge formula you pasted did not look correct. The semicolons should be commas and to round the value to 1 decimal place the second parameter in the ROUND function should be 1, not -1. You said it was for row 982, but if the formula is in column X that would give a circular reference error. I assume it was really for cell X988

Logical True evaluates to 1, so your formula for X988 could be:

=IF(AND(SUM(X970:X987)=0,OR(ROUND(B988,1)<>ROUND(B987,1),ROUND(C988,1)<>ROUND(C987,1))),TRUE,"")

If you changed it to

=IF(AND(SUM(X970:X987)=0,OR(ROUND(B988,1)<>ROUND(B987,1),ROUND(C988,1)<>ROUND(C987,1))),ROW(),0)

Then column X would contain the row numbers and your code could check each cell in column X for a non-zero value and load that value into an array for further processing.

 

[Jharming]

The reason to semicolon is that here in Denmark we have comma to divide the numbers and not (.).

 

[pbornemeier]

I did not realize that you were using a different set of regional settings than mine. I though your paste got corrupted somehow. How about the -1 for the second round arguments?

 

[Jharming]

I want to round to nearest 10, 20, 30 etc.

I know I wrote to the nearest tenth but I did not meant 9,8 but 10

 

[pbornemeier]

Did you get the row numbers into a form that your code can use?

 

[Jharming]

Did you get the row numbers into a form that your code can use?

Now I got the row numbers.

I have a sheet where I want to use these numbers. The sheet I have called "checkbox data"

Column A = Box no.
Column B = TRUE/FALSE
Column C = Row begin
Column D = Row End

So here I need to pick up the row numbers from the sorted data and put them in Column C and Column D.

I am not exactly sure how to write the code that pickup the row numbers and put them in a list.

 

[Jharming]

Now I got the row numbers.

I have a sheet where I want to use these numbers. The sheet I have called "checkbox data"

Column A = Box no.
Column B = TRUE/FALSE
Column C = Row begin
Column D = Row End

So here I need to pick up the row numbers from the sorted data and put them in Column C and Column D.

I am not exactly sure how to write the code that pickup the row numbers and put them in a list.

Now I got the row numbers.

I have a sheet where I want to use these numbers. The sheet I have called "checkbox data"

Column A = Box no.
Column B = TRUE/FALSE
Column C = Row begin
Column D = Row End

So here I need to pick up the row numbers from the sorted data and put them in Column C and Column D.

I am not exactly sure how to write the code that pickup the row numbers and put them in a list.

I used this code:

ok I found out how to do it.

INDEX(Temperature!$X$18:$X$1007,MATCH(TRUE,Temperature!$X$18:$X$1007>checkboxdata!C1,0),0)

Then I have for next number c2=d1+1

 

[Jharming]

Now when I have the row number in the list I wanted, then I want to include this in the macro I have made

Sub Hour2()
If Sheets("checkboxdata").Range("b2").Value = "True" Then
Call Unhide2
ElseIf Sheets("checkboxdata").Range("b2").Value = "False" Then
Call Hide2
Else
Return
End If
End Sub


Sub Hide2()
Sheets("Temperatures in graph").Rows("66:132").EntireRow.Hidden = True
End Sub


Sub Unhide2()
Sheets("Temperatures in graph").Rows("66:133").EntireRow.Hidden = False
End Sub

instead of writting the row numbers I want to pick up the value from a cell. Here I have used something like this

Sheets("Temperatures in graph").Rows("Sheets("checkboxdata").Range("c1").Value:Sheets("checkboxdata").Range("d1").Value").EntireRow.Hidden = True

But this does not work.

 

[pbornemeier]

Try this:

Sub RowHideUnhide()

    Dim lX As Long
    Dim lLastCheckBoxDataRow As Long
    
    With Worksheets("CheckBoxData")
        lLastCheckBoxDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lX = 2 To lLastCheckBoxDataRow
            Sheets("Temperatures in graph").Range("A" & .Cells(lX, 3).Value & ":A" & .Cells(lX, 4).Value).EntireRow.Hidden = .Cells(lX, 2).Value
        Next
    End With
    
End Sub

 

[Jharming]

Try this:

Sub RowHideUnhide()

    Dim lX As Long
    Dim lLastCheckBoxDataRow As Long
    
    With Worksheets("CheckBoxData")
        lLastCheckBoxDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lX = 2 To lLastCheckBoxDataRow
            Sheets("Temperatures in graph").Range("A" & .Cells(lX, 3).Value & ":A" & .Cells(lX, 4).Value).EntireRow.Hidden = .Cells(lX, 2).Value
        Next
    End With
    
End Sub

Very compact macro. Unfortunate I cannot make it work.

Just for me to understand. First you set two variables called lX and lLastCheckBoxDataRow

The next part I dont fully understand.

First you define how many rows there are and define this as the parameter lLastCheckBoxDataRow. Here I dont understand .End(xlUp).

Now you define the interval. From lx = 2 to end (I use also the first row as data, so here I should change to 1)

In Sheets you take the value in row number, column 3 and column 4 and hide if column 2 is TRUE

EDIT:

Apparently now it works. I dont fully understand know what happend.