vba - Excel Run-time error '13': Type mismatch code issues -
i'm working on code , keep getting errors. compile, keep getting run-time errors. i'm trying compare 2 different sheets , highlight cells not match. not sure error(s) occurring. appreciated.
sub david() dim initial_po double dim initial_firmed range dim initial_agreed_ship range dim initial_actual_ship range dim initial_agreed_delivery range dim initial_actual_delivery range dim initial_requested_quantity range dim initial_actual_quantity range dim initial_qmetric double dim initial_dmetric double dim final_po double dim final_firmed range dim final_agreed_ship range dim final_actual_ship range dim final_agreed_delivery range dim final_actual_delivery range dim final_requested_quantity range dim final_actual_quantity range dim final_qmetric double dim final_dmetric double dim initial_agreed_delivery_date date dim final_agreed_delivery_date date dim initial_actual_delivery_date date dim final_actual_delivery_date date dim today date 'dim numrow integer dim long dim bulklt double = 2 3000 sheets("initial").select set initial_po = cells(i, 7) set initial_firmed = cells(i, 9) set initial_agreed_ship = cells(i, 10) set initial_actual_ship = cells(i, 11) set initial_agreed_delivery = cells(i, 13) set initial_actual_delivery = cells(i, 14) set initial_requested_quantity = cells(i, 15) set initial_actual_quantity = cells(i, 16) sheets("final").select set final_po = cells(i, 7) set final_firmed = cells(i, 9) set final_agreed_ship = cells(i, 10) set final_actual_ship = cells(i, 11) set final_agreed_delivery = cells(i, 13) set final_actual_delivery = cells(i, 14) set final_requested_quantity = cells(i, 15) set final_actual_quantity = cells(i, 15) 'initial highlighting if (initial_po = final_po) if not (initial_firmed = final_firmed) initial_firmed.interior.color = rgb(225, 225, 0) , final_firmed.interior.color = rgb(225, 225, 0) end if if not (initial_agreed_ship = final_agreed_ship) initial_agreed_ship.interior.color = rgb(225, 225, 0) , final_agreed_ship.interior.color = rgb(225, 225, 0) end if if not (initial_actual_ship = final_actual_ship) initial_actual_ship.interior.color = rgb(225, 225, 0) , final_actual_ship.interior.color = rgb(225, 225, 0) end if if not (initial_agreed_delivery = final_agreed_delivery) initial_agreed_delivery.interior.color = rgb(225, 225, 0) , final_agreed_delivery.interior.color = rgb(225, 225, 0) end if if not (initial_actual_delivery = final_actual_delivery) initial_actual_delivery.interior.color = rgb(225, 225, 0) , final_actual_delivery.interior.color = rgb(225, 225, 0) end if if not (initial_requested_quantity = final_requested_quantity) initial_requested_quantity.interior.color = rgb(225, 225, 0) , final_requested_quantity.interior.color = rgb(225, 225, 0) end if if not (initial_actual_quantity = final_actual_quantity) initial_actual_quantity.interior.color = rgb(225, 225, 0) , final_actual_quantity.interior.color = rgb(225, 225, 0) end if if not (initial_requested_quantity = initial_actual_quantity) initial_requested_quantity.interior.color = rgb(225, 225, 0) , initial_actual_quantity.interior.color = rgb(225, 225, 0) end if if not (final_requested_quantity = final_actual_quantity) final_requested_quantity.interior.color = rgb(225, 225, 0) , final_actual_quantity.interior.color = rgb(225, 225, 0) end if 'metric calculation initial_qmetric = ((initial_actual_quantity / initial_requested_quantity) * 100) final_qmetric = ((final_actual_quantity / final_requested_quantity) * 100) sheets("initial").select cells(i, 27) = initial_qmetric sheets("final").select cells(i, 27) = final_qmetric if (initial_qmetric < 90 or initial_qmetric > 110) sheets("initial").select cells(i, 27).interior.color = rgb(225, 225, 0) end if if (final_qmetric < 90 or final_qmetric > 110) sheets("final").select cells(i, 27).interior.color = rgb(225, 225, 0) end if initial_dmetric = datediff("d", initial_agreed_delivery_date, initial_actual_delivery_date) final_dmetric = datediff("d", final_agreed_delivery_date, final_actual_delivery_date) sheets("initial").select cells(i, 28) = initial_dmetric sheets("final").select cells(i, 28) = final_dmetric if (initial_dmetric > 5 or initial_dmetric < (-5)) sheets("initial").select cells(i, 28).interior.color = rgb(225, 225, 0) end if if (final_dmetric > 5 or final_dmetric < (-5)) sheets("final").select cells(i, 28).interior.color = rgb(225, 225, 0) end if 'bulk lead time bulklt = datediff("d", today, final_agreed_ship) if isempty(final_firmed) = true if (bulklt < 90) final_firmed.interior.color = rgb(225, 225, 0) end if end if else: msgbox ("po numbers in row" & & "do not match") end if next end end sub
as last error, you're dimming initial_po value type (dim initial_po double) , trying set object (set initial_po = cells(i, 7)): choose type (value or object) , act consistently
furthermore more control on code , reduce execution time referencing ranges , avoiding select() method , selection property them
finally you're duplicating lot of code, , can lead unwanted typos , loose code control
for above may want consider code:
option explicit sub david() dim initialsht worksheet: set initialsht = worksheets("initial") dim finalsht worksheet: set finalsht = worksheets("final") dim long, lastrow long lastrow = initialsht.cells(initialsht.rows.count, 7).end(xlup).row 'get "initial" last non blank row index in column 7 = 2 lastrow if initialsht.cells(i, 7) = initialsht.cells(i, 7) dochecksandformat initialsht, finalsht, else msgbox ("po numbers in row '" & & "' not match") end if next end sub sub dochecksandformat(initialsht worksheet, finalsht worksheet, long) dim initial_firmed range dim initial_agreed_ship range dim initial_actual_ship range dim initial_agreed_delivery range dim initial_actual_delivery range dim initial_requested_quantity range dim initial_actual_quantity range dim initial_qmetric double dim final_firmed range dim final_agreed_ship range dim final_actual_ship range dim final_agreed_delivery range dim final_actual_delivery range dim final_requested_quantity range dim final_actual_quantity range dim initial_agreed_delivery_date date dim final_agreed_delivery_date date dim initial_actual_delivery_date date dim final_actual_delivery_date date dim bulklt double 'initialize relevant variables init initialsht, i, initial_firmed, initial_agreed_ship, initial_actual_ship, initial_agreed_delivery, initial_actual_delivery, initial_requested_quantity, initial_actual_quantity init finalsht, i, final_firmed, final_agreed_ship, final_actual_ship, final_agreed_delivery, final_actual_delivery, final_requested_quantity, final_actual_quantity 'initial highlighting checkandcolor initial_firmed, final_firmed checkandcolor initial_agreed_ship, final_agreed_ship checkandcolor initial_actual_ship, final_actual_ship checkandcolor initial_agreed_delivery, final_agreed_delivery checkandcolor initial_actual_delivery, final_actual_delivery checkandcolor initial_requested_quantity, final_requested_quantity checkandcolor initial_actual_quantity, final_actual_quantity checkandcolor initial_requested_quantity, initial_actual_quantity checkandcolor final_requested_quantity, final_actual_quantity 'metric calculation qmetric initialsht.cells(i, 27), initial_actual_quantity.value, initial_requested_quantity.value qmetric finalsht.cells(i, 27), final_actual_quantity.value, final_requested_quantity.value dmetric initialsht.cells(i, 28), initial_agreed_delivery_date, initial_actual_delivery_date dmetric finalsht.cells(i, 28), final_agreed_delivery_date, final_actual_delivery_date 'bulk lead time bulklt = datediff("d", now, final_agreed_ship) if isempty(final_firmed) if bulklt < 90 final_firmed.interior.color = rgb(225, 225, 0) end if end sub sub init(sht worksheet, long, firmed range, agreed_ship range, actual_ship range, agreed_delivery range, actual_delivery range, requested_quantity range, actual_quantity range) sht set firmed = .cells(i, 9) set agreed_ship = .cells(i, 10) set actual_ship = .cells(i, 11) set agreed_delivery = .cells(i, 13) set actual_delivery = .cells(i, 14) set requested_quantity = .cells(i, 15) set actual_quantity = .cells(i, 16) end end sub sub checkandcolor(rng1 range, rng2 range) if not (rng1 = rng2) rng1.interior.color = rgb(225, 225, 0) , rng2.interior.color = rgb(225, 225, 0) end sub sub qmetric(rng range, actual_quantity double, requested_quantity double) dim qmetric double qmetric = (actual_quantity / requested_quantity) * 100 rng.value = qmetric if (qmetric < 90 or qmetric > 110) rng.interior.color = rgb(225, 225, 0) end sub sub dmetric(rng range, agreed_delivery_date date, actual_delivery_date date) dim dmetric double dmetric = datediff("d", agreed_delivery_date, actual_delivery_date) rng.value = dmetric if (dmetric > 5 or dmetric < -5) rng.interior.color = rgb(225, 225, 0) end sub where made little adjustments:
for example in code wrote:
set initial_actual_quantity = cells(i, 16) ... set final_actual_quantity = cells(i, 15) and assumed column 16 both sheets
Comments
Post a Comment