VBA to find row where either Column 'Value 2' or 'Value 3' goes from 0 to 1 AND...

Sample.png

I have a generic version of a spreadsheet I'm working on where I'm trying to automate some calculations but am getting stuck on this part.

Looking to find each case in columns C ('Value 2') and D ('Value 3') where the value changes from 0 to 1 (binary values only here). There will be multiple instances where 0 to 1 occurs and I need to evaluate each one.

From that I need to pull the data from the corresponding row for 'TIME' and 'Value 1'.

For 'Value 4' and 'Value 5' I need to pull the max value for the last full second before either 'Value 2' or 'Value 3' go from 0 to 1.

Manually I have done this countless times but do not know where to start in VBA for this.

INDEX/MATCH are functions I've used before to find correlating values but I'm struggling to make that connection based on the required 0 to 1 binary value change and then collecting the max value for 'Value 4' and 'Value 5' for the last second before 'Value 2' or 'Value 3' go from 0 to 1.

Any ideas?

Probably easier to provide you with ideas if we could have a little more information.
Could you post a sample workbook (not a picture of one) that shows us what your expected results would look like, and if not immediately obvious from the sample data, some description on how you arrived at that output?

It's always easier to help when we can see the issue in context.

BSB

Book 5.xlsxBook 5.xlsx

Hi BSB,

Thanks for the response. Attached is a sample of the spreadsheet.

What I'm looking for is a way to determine when either columns 'Value 2' or 'Value 3' switch from 0 to 1.

This data is collected 8 times a set.

Then find the corresponding 'TIME' column.

Then find the max value for the previous two seconds for column 'Value 1'

Also, need the previous value for columns 'Value 4' and 'Value 5' (note this data is collected only once a second).

Is there vba to compare each row in a column to each other and is that the best way to approach this to get the first part done?

You've still not shown us what your expected results look like with this sample data, so we don't know what they should look like or where they should go...

BSB

Book 5.xlsxBook 5.xlsx


Sorry for the delay in responding, was on a work trip and just got back last night.

Here is a format for what I'd look to do if I was able to step my way through coding to get the values.

One thing I did do is this:

Sub Find_Value2 ()

No_of_rows = Cells(Rows.Count, 4).End(xlUp).Row
temp = 0
For i = 3 To No_of_rows
If Cells(i, 4) = 0 and Cells(i + 1, 4) = 1 Then
temp = temp + 1
End If

Next
Cells (3, 11) = temp

End Sub


What this did is tell me how many times the value changes from 0 to 1 but from there I'm not sure how to get the remaining associated values.

Any tips on where to go with this?