excel - How to fix an 'Out of memory" eroor -
my following code poppes runtime error7: "out of memory". dont understand why. not long code. identified row @ error occurs. thoughts?
sub discrepancy_report() dim var1 long dim var2 long dim var3 long dim colrg range dim lastr long dim dr string dim r integer dim sht1 worksheet dim sht2 worksheet dim errbox integer r = 5 on error goto datasheeterror set sht1 = sheets("datasheet") on error goto discrepancysheeterror set sht2 = sheets("discrepancyreport") on error goto 0 sht2.select rows("9:999").select selection.delete shift:=xlup range("a9").select sht1.select lastr = activesheet.range("a1").offset(activesheet.rows.count - 1, 0).end(xlup).row lastr = lastr - 1 'store values in array dim tbl range dim var() variant dim c long, matchrow long set tbl = range("a3:g" & lastr) redim var(1 tbl.rows.count) c = 1 tbl.rows.count var(r) = tbl(r, 1) & tbl(r, 2) & tbl(r, 3) & tbl(r, 4) & tbl(r, 5) next 'column 1: wp set colrg = range("a3:a" & lastr) each cell in colrg if (cell.value) = 6.01 or (cell.value) = 6.03 or (cell.value) = 6.04 or (cell.value) = 6.27 else sht2.cells(r, 1).value = cell.address sht2.cells(r, 2).value = (cell.value) sht2.cells(r, 3).value = "not valid wp" r = r + 1 end if next set colrg = range("b3:b" & lastr) each cell in colrg if (cell.value) < 99999 , (cell.value) > 10000 else sht2.cells(r, 1).value = cell.address sht2.cells(r, 2).value = (cell.value) sht2.cells(r, 3).value = "this not valid pcr number" r = r + 1 end if next set colrg = range("c3:c" & lastr) each cell in colrg if (cell.value) = "stage 0 - submit pcr" _ or (cell.value) = "stage 1a - director approval" _ or (cell.value) = "stage 1b - pmo approval" _ or (cell.value) = "stage 1c - cb1 approval" _ or (cell.value) = "stage 2a - tim , request impacts" _ or (cell.value) = "stage 2b - track impacts" _ or (cell.value) = "stage 2c - consolidation" _ or (cell.value) = "stage 3a - post cb2 action closing" _ or (cell.value) = "stage 3b - cslt approval" _ or (cell.value) = "stage 3c - finance approval" _ or (cell.value) = "stage 4a - request pip" _ or (cell.value) = "stage 4b - track pip" _ or (cell.value) = "stage 5a - track pcrin" _ or (cell.value) = "stage 5b - implementation consolidation" _ or (cell.value) = "stage 6 - closed" else sht2.cells(r, 1).value = cell.address sht2.cells(r, 2).value = (cell.value) sht2.cells(r, 3).value = "this not official icms stage. ex: 'stage 5b - implementation consolidation'" r = r + 1 end if next set colrg = range("d3:d" & lastr) c = 1 each cell in colrg '## out of memory error on following line if (cell.value) = "kiled" or (cells.value) = "archived" c = c + 1 elseif tbl.cells(c, 3).value = "stage 1a - director approval" _ or tbl.cells(c, 3).value = "stage 1b - pmo approval" _ or tbl.cells(c, 3).value = "stage 1c - cb1 approval" _ or tbl.cells(c, 3).value = "stage 2a - tim , request impacts" _ or tbl.cells(c, 3).value = "stage 2b - track impacts" _ or tbl.cells(c, 3).value = "stage 2c - consolidation" _ or tbl.cells(c, 3).value = "stage 3a - post cb2 action closing" _ or tbl.cells(c, 3).value = "stage 3b - cslt approval" _ or tbl.cells(c, 3).value = "stage 3c - finance approval" cell.value = "pre-approval" c = c + 1 elseif tbl.cells(c, 3).value = "stage 4a - request pip" _ or tbl.cells(c, 3).value = "stage 4b - track pip" _ or tbl.cells(c, 3).value = "stage 5a - track pcrin" _ or tbl.cells(c, 3).value = "stage 5b - implementation consolidation" _ or tbl.cells(c, 3).value = "stage 6 - closed" cell.value = "approved" c = c + 1 else sht2.cells(r, 1).value = cell.address sht2.cells(r, 2).value = (cell.value) sht2.cells(r, 3).value = "this pcrs stage not correct status cannot determined" r = r + 1 c = c + 1 end if next exit sub datasheeterror: errbox = msgbox("there error witht main data tab. either has been" & chr(13) & "deletod or renamed." & chr(13) & chr(13) & "please ensure main tab present , named 'datasheet'", vbokonly, "data tab error") exit sub discrepancysheeterror: thisworkbook.sheets.add after:=sheets(worksheets.count), count:=1, type:=xlworksheet activesheet.name = "discrepancyreport" resume next end sub thanks!
you have "s" in second part of if statement.
change this:
(cells.value) = "archived" to this:
(cell.value) = "archived" 
Comments
Post a Comment