excel - How to clean up a VBA code -
i have following code contributor these forums wrote me, i've modified want. know can shortened, vba skills extremely basic.
the code adds summary of row.
thank you.
public sub sumcages() dim current_row, summary_row, item_total integer current_row = 45 summary_row = 44 while sheet8.cells(current_row, 7) <> "" if isnumeric(sheet8.cells(current_row, 7)) item_total = item_total + val(sheet8.cells(current_row, 7)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 8) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 8) = sheet8.cells(current_row, 7) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 8) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 11) <> "" if isnumeric(sheet8.cells(current_row, 11)) item_total = item_total + val(sheet8.cells(current_row, 11)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 12) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 12) = sheet8.cells(current_row, 11) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 12) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 15) <> "" if isnumeric(sheet8.cells(current_row, 15)) item_total = item_total + val(sheet8.cells(current_row, 15)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 16) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 16) = sheet8.cells(current_row, 15) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 16) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 19) <> "" if isnumeric(sheet8.cells(current_row, 19)) item_total = item_total + val(sheet8.cells(current_row, 19)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 20) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 20) = sheet8.cells(current_row, 19) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 20) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 23) <> "" if isnumeric(sheet8.cells(current_row, 23)) item_total = item_total + val(sheet8.cells(current_row, 23)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 24) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 24) = sheet8.cells(current_row, 23) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 24) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 27) <> "" if isnumeric(sheet8.cells(current_row, 27)) item_total = item_total + val(sheet8.cells(current_row, 27)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 28) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 28) = sheet8.cells(current_row, 27) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 28) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 31) <> "" if isnumeric(sheet8.cells(current_row, 31)) item_total = item_total + val(sheet8.cells(current_row, 31)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 32) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 32) = sheet8.cells(current_row, 31) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 32) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 35) <> "" if isnumeric(sheet8.cells(current_row, 35)) item_total = item_total + val(sheet8.cells(current_row, 35)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 36) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 36) = sheet8.cells(current_row, 35) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 36) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 39) <> "" if isnumeric(sheet8.cells(current_row, 39)) item_total = item_total + val(sheet8.cells(current_row, 39)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 40) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 40) = sheet8.cells(current_row, 39) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 40) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 43) <> "" if isnumeric(sheet8.cells(current_row, 43)) item_total = item_total + val(sheet8.cells(current_row, 43)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 44) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 44) = sheet8.cells(current_row, 43) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 44) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 47) <> "" if isnumeric(sheet8.cells(current_row, 47)) item_total = item_total + val(sheet8.cells(current_row, 47)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 48) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 48) = sheet8.cells(current_row, 47) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 48) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 51) <> "" if isnumeric(sheet8.cells(current_row, 51)) item_total = item_total + val(sheet8.cells(current_row, 51)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 52) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 52) = sheet8.cells(current_row, 51) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 52) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 55) <> "" if isnumeric(sheet8.cells(current_row, 55)) item_total = item_total + val(sheet8.cells(current_row, 55)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 56) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 56) = sheet8.cells(current_row, 55) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 56) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 59) <> "" if isnumeric(sheet8.cells(current_row, 59)) item_total = item_total + val(sheet8.cells(current_row, 59)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 60) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 60) = sheet8.cells(current_row, 59) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 60) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 63) <> "" if isnumeric(sheet8.cells(current_row, 63)) item_total = item_total + val(sheet8.cells(current_row, 63)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 64) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 64) = sheet8.cells(current_row, 63) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 64) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 67) <> "" if isnumeric(sheet8.cells(current_row, 67)) item_total = item_total + val(sheet8.cells(current_row, 67)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 68) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 68) = sheet8.cells(current_row, 67) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 68) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 71) <> "" if isnumeric(sheet8.cells(current_row, 71)) item_total = item_total + val(sheet8.cells(current_row, 71)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 72) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 72) = sheet8.cells(current_row, 71) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 72) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 75) <> "" if isnumeric(sheet8.cells(current_row, 75)) item_total = item_total + val(sheet8.cells(current_row, 75)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 76) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 76) = sheet8.cells(current_row, 75) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 76) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 79) <> "" if isnumeric(sheet8.cells(current_row, 79)) item_total = item_total + val(sheet8.cells(current_row, 79)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 80) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 80) = sheet8.cells(current_row, 79) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 80) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 83) <> "" if isnumeric(sheet8.cells(current_row, 83)) item_total = item_total + val(sheet8.cells(current_row, 83)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 84) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 84) = sheet8.cells(current_row, 83) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 84) = item_total current_row = 45 summary_row = 44 while sheet8.cells(current_row, 87) <> "" if isnumeric(sheet8.cells(current_row, 87)) item_total = item_total + val(sheet8.cells(current_row, 87)) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, 88) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, 88) = sheet8.cells(current_row, 87) ' copy label end if item_total = 0 ' reset item_total end if current_row = current_row + 1 ' advance current_row wend sheet8.cells(summary_row + 1, 88) = item_total end sub
there better ways tighten , make little more object-oriented. couldn't follow logic of changing iterator variable without trying recreate worksheet structure didn't share us, so, untested, , may need tweaking.
in case, should place start. above code perfect example of can consolidated in subroutine.
first, in main routine establish loop. looks starting @ column 7, , stepping every 4th column through 87:
sub testmain() dim long = 7 87 step 4 'iterate every 4th column 7 87 dostuff 'call subroutine, , pass column# argument next end sub now, put rest of operative code in subroutine, accepts i required argument, mycol:
sub dostuff(mycol long) ' ' subroutine performs manipulation ' dim currentrow long dim summaryrow long dim cl range currentrow = 45 summaryrow = 44 set cl = sheet8.cells(currentrow, mycol) while cl <> "" if isnumeric(cl) item_total = item_total + val(cl) else summary_row = summary_row + 1 ' advance summary_row if item_total > 0 sheet8.cells(summary_row, mycol + 1) = item_total ' display total current_row = current_row - 1 ' correct advancement else sheet8.cells(summary_row, mycol + 1) = cl ' copy label end if item_total = 0 ' reset item_total end if currentrow = currentrow + 1 ' advance current_row set cl = sheet8.cells(currentrow, mycol) wend sheet8.cells(summary_row + 1, mycol + 1) = item_total end sub
Comments
Post a Comment