Excel VBA looping issue -
i writing macro starts entry of valid date. after date entry, want macro search worksheet rows have date. then, want of respective credits , debits totaled rows, , want totals placed worksheet. code wrote this.
the problem eventually, code loop infinitely within "search =, if, , end if." if place "else: next i" before end if, error, else without prompt.
any suggestions?
private sub commandbutton3_click() dim datecheck string dim lastrow long dim l integer dim long dim shipday date dim search string dim usatotal long dim usacredit long dim usadebit long datecheck = inputbox("what date ship day 1?", "ship day entry") if isdate(datecheck) shipday = datevalue(datecheck) else: msgbox ("invalid date") exit sub end if ' prompts user ship day 1, , checks if actual date l = 0 30 ' execute code 1 month worth of dates shipday = shipday + l msgbox shipday = 1 10000 ' check every row of worksheet search = worksheets("sheet1").cells(i, 12).value ' variable use instr check "can" if ((instr(1, search, "can", vbbinarycompare) = 0) _ , (worksheets("sheet1").cells(i, 8) = shipday) _ , (worksheets("sheet1").cells(i, 6).text = "invoice")) usadebit = worksheets("sheet1").cells(i, 22).value ' account debits usacredit = worksheets("sheet1").cells(i, 24).value ' account credits usatotal = usatotal + usacredit - usadebit ' calculate contribution ' tried placing else: next i, gives me else without prompts. end if next msgbox (usatotal) worksheets("june canada").cells(l + 10, 4).value = usatotal / 1000 'enter value sheet usatotal = 0 ' reset usatotal value next l
use used range instead of 10000
dim lrow long while lrow <= ws.usedrange.rows.count 'do stuff here. lrow = lrow + 1 ws.range("a" & lrow).activate loop so code this
private sub commandbutton3_click() dim datecheck string dim lastrow long dim l integer dim long dim shipday date dim search string dim usatotal long dim usacredit long dim usadebit long datecheck = inputbox("what date ship day 1?", "ship day entry") if isdate(datecheck) shipday = datevalue(datecheck) else: msgbox ("invalid date") exit sub end if ' prompts user ship day 1, , checks if actual date l = 0 30 ' execute code 1 month worth of dates shipday = shipday + l msgbox shipday = 1 while <= ws.usedrange.rows.count search = worksheets("sheet1").cells(i, 12).value ' variable use instr check "can" if ((instr(1, search, "can", vbbinarycompare) = 0) _ , (worksheets("sheet1").cells(i, 8) = shipday) _ , (worksheets("sheet1").cells(i, 6).text = "invoice")) usadebit = worksheets("sheet1").cells(i, 22).value ' account debits usacredit = worksheets("sheet1").cells(i, 24).value ' account credits usatotal = usatotal + usacredit - usadebit ' calculate contribution ' tried placing else: next i, gives me else without prompts. elseif instr(something, something) end if = + 1 ws.range("a" & i).activate loop msgbox (usatotal) worksheets("june canada").cells(l + 10, 4).value = usatotal / 1000 'enter value sheet usatotal = 0 ' reset usatotal value next l 
Comments
Post a Comment