I have a macro that I generated several years ago with help of some on here, at the time the purpose of the macro was to extract all negative values from a couple of columns, and put them into a graph, highlighting the most negative value.

The numbers were generated from a machine that indents a material. This records the force with positive values meaning the machine is pushing and negative values meaning the machine is trying to pull away (so the adhesive force of the substrate).

Now the new kit will indent multiple times, so there will be a run of positive numbers, a run of negative numbers and this will repeat 4-5 times.

The previous macro extracts ALL the negative values and graphs them, only pulling out the MAX negative value, however it would be useful to isolate all of these “Adhesive events” separately, for example:

Eventually the ‘Force’ column turns positive (not shown in image as it’s quite a lot of readings) then it will repeat and turn negative again, I need to extract each of these negative runs separately, my previous macro would only summarise all negative values and not separate out each of the ‘runs’

I presume there will be a way to select negative numbers in VBA until you hit a positive, then graph that (or extract lowest value) and carry on until the next range of negatives,

Sub CopyAndGraphFinal()

‘ CopyAndGraph Macro

‘

Dim Actwks As Worksheet

Set Actwks = ActiveSheet

Application.ScreenUpdating = False

‘Dont update screen while running (lags a bit)

For Each Actwks In Worksheets

Actwks.Activate

‘Do for every sheet in the Book

Range("A5427").Select

Selection.End(xlUp).Select

Range(Selection, Selection.End(xlUp)).Select

‘Select last A Column

Selection.Copy

Range("H4").Select

ActiveSheet.Paste

‘Paste in H4

Range("B5569").Select

Selection.End(xlUp).Select

Range(Selection, Selection.End(xlUp)).Select

Application.CutCopyMode = False

Selection.Copy

‘Select and Paste B Column

Range("I4").Select

ActiveSheet.Paste

Range("H4:I2002").Select

Range("I4").Activate

Range("H3:I3").Select

Application.CutCopyMode = False

Selection.AutoFilter

ActiveSheet.Range("H3:I3002").AutoFilter Field:=1, Criteria1:=">0", _

    Operator:=xlAnd

ActiveSheet.Range("H3:I3002").AutoFilter Field:=2, Criteria1:="<0", _

    Operator:=xlAnd

‘Filter for >0 and <0

Range("J5:J2500").Formula = "=(RC[-1]+R[1]C[-1])/2*(R[1]C[-2]-RC[-2])"



ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select

ActiveChart.SetSourceData Source:=Range("H:I")



Range("L1").Select

ActiveCell.FormulaR1C1 = "Total Adhesive Energy"

Range("L2").Select

ActiveCell.FormulaR1C1 = "Peak Force"

Range("M1").Select

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,C[-3])"

Range("M2").Select

ActiveCell.FormulaR1C1 = "=MIN(C[-4])"

Range("M3").Select



Next Actwks

Application.ScreenUpdating = True

Any help would be greatly appreciated!