Out of Memory Message - Code Problem?

P

Paige

I have a large workbook; on Sheet1 is an inventory containing 10K lines of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2 and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts formulas in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete this
task with available resources.", and I can't figure out why. I've changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so the
formulas wouldn't have to refer to a different sheet), dropped all the other
code from the workbook to see if that made a difference, tested it on another
PC (and in another workbook), and stepped through using watch to look at the
variables. It doesn't always crash at the same place in the 'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've removed
some of the formulas that are in it, just to shorten it for easier reading.
Could someone look at it and see if they can figure what may be wrong here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula = "=IF(ISERROR(IF(RC[-1]>0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]>0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<>0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS, FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearContents
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
N

NickHK

Paige,
can't really say about your coding/system, but you could call
Application.MemoryFree
Application.MemoryUsed
in suitable places to see when large changes occur.

NickHK

Paige said:
I have a large workbook; on Sheet1 is an inventory containing 10K lines of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols
M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2 and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts formulas
in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out
in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete
this
task with available resources.", and I can't figure out why. I've changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so the
formulas wouldn't have to refer to a different sheet), dropped all the
other
code from the workbook to see if that made a difference, tested it on
another
PC (and in another workbook), and stepped through using watch to look at
the
variables. It doesn't always crash at the same place in the
'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've
removed
some of the formulas that are in it, just to shorten it for easier
reading.
Could someone look at it and see if they can figure what may be wrong
here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula =
"=IF(ISERROR(IF(RC[-1]>0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]>0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<>0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS,
FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearContents
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Click to expand...
 
R

RB Smissaert

Thanks for the tip, never knew that one.
It doesn't show in the intelli-sense.

RBS

NickHK said:
Paige,
can't really say about your coding/system, but you could call
Application.MemoryFree
Application.MemoryUsed
in suitable places to see when large changes occur.

NickHK

Paige said:
I have a large workbook; on Sheet1 is an inventory containing 10K lines of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols
M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2
and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts
formulas in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out
in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete
this
task with available resources.", and I can't figure out why. I've
changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so
the
formulas wouldn't have to refer to a different sheet), dropped all the
other
code from the workbook to see if that made a difference, tested it on
another
PC (and in another workbook), and stepped through using watch to look at
the
variables. It doesn't always crash at the same place in the
'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while
it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've
removed
some of the formulas that are in it, just to shorten it for easier
reading.
Could someone look at it and see if they can figure what may be wrong
here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula =
"=IF(ISERROR(IF(RC[-1]>0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]>0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<>0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS,
FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS
ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearContents
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
R

RB Smissaert

Interesting, but it doesn't seem to work well, which is confirmed here:
http://www.decisionmodels.com/memlimitsb.htm

RBS

NickHK said:
Paige,
can't really say about your coding/system, but you could call
Application.MemoryFree
Application.MemoryUsed
in suitable places to see when large changes occur.

NickHK

Paige said:
I have a large workbook; on Sheet1 is an inventory containing 10K lines of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols
M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2
and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts
formulas in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out
in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete
this
task with available resources.", and I can't figure out why. I've
changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so
the
formulas wouldn't have to refer to a different sheet), dropped all the
other
code from the workbook to see if that made a difference, tested it on
another
PC (and in another workbook), and stepped through using watch to look at
the
variables. It doesn't always crash at the same place in the
'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while
it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've
removed
some of the formulas that are in it, just to shorten it for easier
reading.
Could someone look at it and see if they can figure what may be wrong
here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula =
"=IF(ISERROR(IF(RC[-1]>0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]>0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<>0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS,
FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS
ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearContents
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
N

NickHK

I've never really examined how accurate these numbers are or how quickly
they get updated.
Some quick test now show that a rough correlation between these numbers (or
using the worksheet versions "=INFO("memused") and that used according to
Task Manager. Your link indicates that the numbers are not really to be
trusted though; I would believe that.
It does indicate though that a .Save free up a lot memory, presumably by
clearing the Undo buffer.
That may be enough to allow the OP to complete the routine.

NickHK

RB Smissaert said:
Interesting, but it doesn't seem to work well, which is confirmed here:
http://www.decisionmodels.com/memlimitsb.htm

RBS

NickHK said:
Paige,
can't really say about your coding/system, but you could call
Application.MemoryFree
Application.MemoryUsed
in suitable places to see when large changes occur.

NickHK

Paige said:
I have a large workbook; on Sheet1 is an inventory containing 10K lines
of
data, in Cols A-L. My code ('SizeCP') first inserts formulas (in Cols
M-AU)
for each of these lines. It then calls 'SizeCS', which goes to Sheet2
and
inserts formulas in Col A-AU, for as many rows as there are in Sheet1.
Lastly, it calls 'SizeSchedule', which goes to Sheet3 and inserts
formulas in
Cols A-S, for as many rows as there are in Sheet1. The system bombs out
in
the middle part of the 'SizeSchedule' macro with "Excel cannot complete
this
task with available resources.", and I can't figure out why. I've
changed
calculation to manual, tried putting all the Sheet3 data on Sheet1 (so
the
formulas wouldn't have to refer to a different sheet), dropped all the
other
code from the workbook to see if that made a difference, tested it on
another
PC (and in another workbook), and stepped through using watch to look at
the
variables. It doesn't always crash at the same place in the
'SizeInventory'
macro, but it usually happens around Columns D/E; every once in a while
it
will run thru okay, but 99.9% of the time it doesn't. I have plenty of
memory in my PC, and no other apps running. Below is my code; I've
removed
some of the formulas that are in it, just to shorten it for easier
reading.
Could someone look at it and see if they can figure what may be wrong
here;
I'm just at my wits end, having worked on this for 5 days now.

Option Explicit
Sub SizeInventoryBox()
Call SizeCP
Call SizeCS
Call SizeSchedule
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCP()
Dim LastRow As Range
Dim FirstRow As Range
Dim wks As Worksheet
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Select
With wks
Set wks = ActiveSheet
Set FirstRow = Range("M65536").End(xlUp).Offset(1, 0)
Set LastRow = Range("G65536").End(xlUp).Offset(0, 6)
End With
Range(FirstRow, LastRow).Formula =
"=IF(ISERROR(IF(RC[-1]>0,(VLOOKUP(RC
[-1],SC,2,FALSE)),"""")),"""",IF(RC[-1]>0,(VLOOKUP(RC[-1],SC,2,FALSE)),""""))"
Range(FirstRow.Offset(0, 16), LastRow.Offset(0, 16)).Formula =
"=IF(RC[-12]="""","""",IF(RC[-1]<>0,RC[-1],CPDISCOUNT))"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS M-AU
Set FirstRow = Nothing
Set LastRow = Nothing
Set wks = Nothing
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeCS()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet2").Unprotect
Worksheets("Sheet2").Select
Range(Range("AS65536").End(xlUp).Offset(1, 0),
Range("G65536").End(xlUp).Offset(0, 38)).Formula =
"=IF(RC[-30]=5,""Sheet1"","""")"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS
AND
OFFSET REFERENCES, FOR COLUMNS A-AU
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub SizeSchedule()
Dim LasRowCP As Long
Dim LastRowCPP As Long
LastRowCP = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowCPP = Worksheets("Sheet1").usedrange.Rows.Count + 10
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Sheet3").Unprotect
Worksheets("Sheet3").Select
Range("A13" & ":" & "A" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C7)"
Range("B13" & ":" & "B" & LastRowCPP).Formula =
"=IF(OR('Sheet1'!R[-10]C31=0,'Sheet1'!R[-10]C31=""""),"""",'Sheet1'!R[-10]C14)"
'NOTE--THE ABOVE 'FORMULA SYNTAX' IS REPEATED, WITH DIFFERENT FORMULAS,
FOR
COLUMNS A-S; IT USUALLY CRASHES AROUND COLS D OR E, BUT SOMETIMES GETS
ALL
THE WAY THRU R BEFORE CRASHING
'ERASE ANYTHING LEFT OVER FROM A PREVIOUS SIZING
Range(Cells(LastRowCP + 11, 1),
Range("A65536").End(xlDown)).EntireRow.ClearContents
LastRowCP = 0
LastRowCPP = 0
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Object variable or with Block variable not set 9
MACRO Effeciency 3
Code does not error but no copy either 4
Macro autofills formula when a cell value is changed 4
vlookup issue 2
Unprotect sheet when Saving As 2
Compare 2 excel sheets -Error Checking of entered names 1
Need another eye on this Sub() 8

Top