excel - Code meant to copy rows from one sheet to another if there are specific strings in 2 columns keeps crashing -
whenever run code crashes. please help!
this code supposed do. unknown amount of filled rows, if column g of worksheet "current pm" contains either "as-001", "ee-001", "mm-001", "os-001", "co-001", "do-001", "fo-001", "fd-001", "to-001", "ip-001" , column h contains "pdr" copy row "current pm" sheet "print_current pms" sheet.
sub sort4printing() dim integer, j integer = 2: j = 2 while isempty(worksheets("current pm").cells(i, 1)) = false if worksheets("current pm").cells(i, 8) = "pdr" if worksheets("current pm").cells(i, 7) = "as-001" or worksheets("current pm").cells(i, 7) = "ee-001" or worksheets("current pm").cells(i, 7) = "mm-001" or worksheets("current pm").cells(i, 7) = "os-001" or worksheets("current pm").cells(i, 7) = "fo-001" or worksheets("current pm").cells(i, 7) = "fd-001" or worksheets("current pm").cells(i, 7) = "to-001" or worksheets("current pm").cells(i, 7) = "ip-001" worksheets("print_current pms").cells(j, 1) = worksheets("current pm").cells(i, 1) worksheets("print_current pms").cells(j, 2) = worksheets("current pm").cells(i, 2) worksheets("print_current pms").cells(j, 3) = worksheets("current pm").cells(i, 12) worksheets("print_current pms").cells(j, 4) = worksheets("current pm").cells(i, 4) worksheets("print_current pms").cells(j, 5) = worksheets("current pm").cells(i, 5) worksheets("print_current pms").cells(j, 6) = worksheets("current pm").cells(i, 6) worksheets("print_current pms").cells(j, 7) = worksheets("current pm").cells(i, 7) worksheets("print_current pms").cells(j, 8) = worksheets("current pm").cells(i, 8) worksheets("print_current pms").cells(j, 9) = worksheets("current pm").cells(i, 10) i=i+1: j=j+1 end if end if wend end sub
while, don't know exactly why crashes, can offer clean-up , techniques cut down on processing of code.
- no need iterate through 2 variables, since both same counter
- dimension counting variable type
long
, if there more 32,767 rows integer variable fall due it's byte limitation. - using
with
block when working objects, instead of referencing object - using
select case
statement multiple conditions on same object (i believe) take less processing multipleif or
conditions. it's a lot more reader friendly.
see refactored code:
sub sort4printing() dim long = 2 worksheets("current pm") while not isempty(.cells(i, 1)) if .cells(i, 8) = "pdr" select case .cells(i, 7) case = "as-001", "ee-001", "mm-001", "os-001", "fo-001", "fd-001", "to-001", "ip-001" worksheets("print_current pms").cells(i, 1) = .cells(i, 1) worksheets("print_current pms").cells(i, 2) = .cells(i, 2) worksheets("print_current pms").cells(i, 3) = .cells(i, 12) worksheets("print_current pms").cells(i, 4) = .cells(i, 4) worksheets("print_current pms").cells(i, 5) = .cells(i, 5) worksheets("print_current pms").cells(i, 6) = .cells(i, 6) worksheets("print_current pms").cells(i, 7) = .cells(i, 7) worksheets("print_current pms").cells(i, 8) = .cells(i, 8) worksheets("print_current pms").cells(i, 9) = .cells(i, 10) end select end if = + 1 wend end end sub
Comments
Post a Comment