excel vba - VBA Programming to recognise a pattern -

excel vba - VBA Programming to recognise a pattern -

i need help on this. okay, illustration have info in excel , part of them:

temp : (21, 22, 23, 24, 25, 26, 29, 30, 26 25, 24, 23, 22), (24, 25, 26, 30, 27, 28, 29, 25, 21, 19), (20, 22, 23, 36,30, 34, 35, 30, 25, 23), (24, 26, 30, 34, 28, 25, 20)

these temperature of liquid. temperature increasing until peak , go downwards until temperature (one cycle). increasing , goes downwards again(next cycle). in these illustration there 4 cycles. , repeats until have few cycles. every each of temperature, can calculate volume. seek write code observe these cycles because want calculate average volume for each cycle. don't have thought how start since new vba.

thank you

the screen shot above shows have placed info in column 1 of worksheet sheet1. have coloured cells help me check macro identifies cycles correctly; macro neither sets nor uses these colours.

starting in cell a2, macro searches peak value , searches trough value identifies cycle. loops identify next cycle. continues until list exhausted.

you not how want each cycle marked have chosen approach might acceptable. can see screen shot below, have copied each cycle row starting in column “g”. not know volume calculation, have placed string vol(n) place holder. need replace appropriate formula. average average temperature; comments tell how alter average volume.

i hope gets started.

option explicit sub splitbycycle() dim coldestaverage long dim coldestcrnt long dim coldesttempfirst long dim coldesttitle long dim colsrc long dim rowsrccrnt long dim rowsrcstartcycle long dim rowdestcrnt long dim volumecrnt double dim volumetotal double ' assume info starts in a2 rowsrccrnt = 2 colsrc = 1 ' output cycles starting row 2 rowdestcrnt = 2 coldesttitle = 5 coldestaverage = 6 coldesttempfirst = 7 worksheets("sheet1") .cells(rowdestcrnt - 1, coldestaverage).value = "average" while true ' record start of current cycle rowsrcstartcycle = rowsrccrnt ' search cycle peak while true rowsrccrnt = rowsrccrnt + 1 if .cells(rowsrccrnt - 1, colsrc).value > _ .cells(rowsrccrnt, colsrc).value ' lastly cell greater current cell lastly cell peak exit end if ' temperatures still rising. go on search peak loop ' search cycle end while true rowsrccrnt = rowsrccrnt + 1 if .cells(rowsrccrnt, colsrc).value = "" or _ .cells(rowsrccrnt - 1, colsrc).value < _ .cells(rowsrccrnt, colsrc).value ' either end of list of temperature has been reached or ' lastly cell less current cell. either way, ' lastly cell end of cycle exit end if ' temperatures still falling. go on search minimum loop ' rowsrcstartcycle start of current cycle ' rowsrccrnt - 1 end of current cycle ' move current cycle next destination row .cells(rowdestcrnt, coldesttitle).value = "temperatures" .range(.cells(rowsrcstartcycle, colsrc), _ .cells(rowsrccrnt - 1, colsrc)).copy .cells(rowdestcrnt, coldesttempfirst).pastespecial paste:=xlpasteall, _ operation:=xlnone, skipblanks:=false, transpose:=true rowdestcrnt = rowdestcrnt + 1 ' calculate volumes volumetotal = 0# .cells(rowdestcrnt, coldesttitle).value = "volumes" coldestcrnt = coldesttempfirst _ coldesttempfirst + rowsrccrnt - rowsrcstartcycle - 1 volumecrnt = .cells(rowdestcrnt - 1, coldestcrnt).value ' replace calculation. ###### ' replace "vol(" & .cells(rowdestcrnt - 1, coldestcrnt).value & ")" ' volumncrnt. ###### .cells(rowdestcrnt, coldestcrnt).value = _ "vol(" & .cells(rowdestcrnt - 1, coldestcrnt).value & ")" volumetotal = volumetotal + volumecrnt next ' calculate average .cells(rowdestcrnt, coldestaverage).value = _ volumetotal / (rowsrccrnt - rowsrcstartcycle) rowdestcrnt = rowdestcrnt + 2 if .cells(rowsrccrnt, colsrc).value = "" ' have reach end of list of temperatures exit end if ' rowsrccrnt first temperature of next cycle loop end end sub

excel-vba