I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x

Mju,

Please answer the following (which I think will resolve your own problem):

What does Name, Size, Color, Logo, and S_Number include? Are these all
numerical values?

What value represents z1? What is z1's data type?

Typically, you loop through array elements via a For Each Next or For Next
loop. For example, you can create a loop like the following:

Dim lngCnt As Long
For lngCnt = LBound(CHC) To UBound(CHC)
MsgBox "CHC(" & lngCnt & ") - " & CHC(lngCnt)
Next

Best,

Matthew Herbert

"mju" wrote:

I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x

Thanks Matthew for your response.
Name, Size, Color, Logo, and S_Number ---They can be number or alpha or
Alpha-numeric except S-Number is also 12 digits numeric
This is the full code:

H = 7
If Z = "Unit Needed" Then
z1 = 6
Else
z1 = 7
End If

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x


"Matthew Herbert" wrote:

Mju,

Please answer the following (which I think will resolve your own problem):

What does Name, Size, Color, Logo, and S_Number include? Are these all
numerical values?

What value represents z1? What is z1's data type?

Typically, you loop through array elements via a For Each Next or For Next
loop. For example, you can create a loop like the following:

Dim lngCnt As Long
For lngCnt = LBound(CHC) To UBound(CHC)
MsgBox "CHC(" & lngCnt & ") - " & CHC(lngCnt)
Next

Best,

Matthew Herbert

"mju" wrote:

I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x

Mju,

Your code will always error out. Why? Because CHC is an array that holds 5
elements and because your "For x = 1 To z1" loop will execute 6 or 7 times,
which is clearly more than 5. Also, the Array function returns a zero-based
array, so your 0 element (i.e. Name) is never evaluated in the "If CHC(x) =
0" statment. So, your 0 element is never evaluated and x = 5 or more will
return an error.

If you want the code to continue execution, regardless of the error, then
you can simply insert an "On Error Resume Next" statement at the start of
your code. This error statement will note the line an error ocurrs and then
skip that line, moving to the next available code line below the line where
the error ocurred.

Best,

Matt

"mju" wrote:

Thanks Matthew for your response.
Name, Size, Color, Logo, and S_Number ---They can be number or alpha or
Alpha-numeric except S-Number is also 12 digits numeric
This is the full code:

H = 7
If Z = "Unit Needed" Then
z1 = 6
Else
z1 = 7
End If

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x


"Matthew Herbert" wrote:

Mju,

Please answer the following (which I think will resolve your own problem):

What does Name, Size, Color, Logo, and S_Number include? Are these all
numerical values?

What value represents z1? What is z1's data type?

Typically, you loop through array elements via a For Each Next or For Next
loop. For example, you can create a loop like the following:

Dim lngCnt As Long
For lngCnt = LBound(CHC) To UBound(CHC)
MsgBox "CHC(" & lngCnt & ") - " & CHC(lngCnt)
Next

Best,

Matthew Herbert

"mju" wrote:

I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x

Sorry my fault. There are actually 7 items in the array.

I want it to skip adding the S-number to the worksheet if it runs out of
memory.

I tried the On Error Resume Next right before the statement but it is not
working.
The macro does continue to run but my header then becomes messed up.


"Matthew Herbert" wrote:

Mju,

Your code will always error out. Why? Because CHC is an array that holds 5
elements and because your "For x = 1 To z1" loop will execute 6 or 7 times,
which is clearly more than 5. Also, the Array function returns a zero-based
array, so your 0 element (i.e. Name) is never evaluated in the "If CHC(x) =
0" statment. So, your 0 element is never evaluated and x = 5 or more will
return an error.

If you want the code to continue execution, regardless of the error, then
you can simply insert an "On Error Resume Next" statement at the start of
your code. This error statement will note the line an error ocurrs and then
skip that line, moving to the next available code line below the line where
the error ocurred.

Best,

Matt

"mju" wrote:

Thanks Matthew for your response.
Name, Size, Color, Logo, and S_Number ---They can be number or alpha or
Alpha-numeric except S-Number is also 12 digits numeric
This is the full code:

H = 7
If Z = "Unit Needed" Then
z1 = 6
Else
z1 = 7
End If

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x


"Matthew Herbert" wrote:

Mju,

Please answer the following (which I think will resolve your own problem):

What does Name, Size, Color, Logo, and S_Number include? Are these all
numerical values?

What value represents z1? What is z1's data type?

Typically, you loop through array elements via a For Each Next or For Next
loop. For example, you can create a loop like the following:

Dim lngCnt As Long
For lngCnt = LBound(CHC) To UBound(CHC)
MsgBox "CHC(" & lngCnt & ") - " & CHC(lngCnt)
Next

Best,

Matthew Herbert

"mju" wrote:

I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x

Mju,

I guess that I'm not completely following you, or you're not completely
following me. If you know the size of the array and you know the position of
S_Number, then simply skip S_Number in the loop. As I mentioned before, an
array loop can look like the following:

Dim lngCnt As Long
For lngCnt = LBound(HCH) To UBound(HCH)
'Code goes here
Next lngCnt

If S_Number is the last element of CHC, then you can code your loop as
follows:
For lngCnt = LBound(CHC) To UBound(CHC) - 1

The -1 will skip the last element of the array.

An alternative would be to add an If Then Statement to skip HCH. If you
know the data that HCH contains, then write the If Then Statement to allow
all code EXCEPT for S_Number to execute within the If Then Statement. For
example, if all the other data is NOT 12 units long and S_Number is ALWAYS 12
units long then you could write the following:

If Len(CHC(x)) < 12 Then

A different approach could be using the MATCH function, which will either
return an error or the indexed position of the item found within the array.
(Note that MATCH returns the index position as 1-based NOT 0-based).

Best,

Matthew Herbert


"mju" wrote:

Sorry my fault. There are actually 7 items in the array.

I want it to skip adding the S-number to the worksheet if it runs out of
memory.

I tried the On Error Resume Next right before the statement but it is not
working.
The macro does continue to run but my header then becomes messed up.


"Matthew Herbert" wrote:

Mju,

Your code will always error out. Why? Because CHC is an array that holds 5
elements and because your "For x = 1 To z1" loop will execute 6 or 7 times,
which is clearly more than 5. Also, the Array function returns a zero-based
array, so your 0 element (i.e. Name) is never evaluated in the "If CHC(x) =
0" statment. So, your 0 element is never evaluated and x = 5 or more will
return an error.

If you want the code to continue execution, regardless of the error, then
you can simply insert an "On Error Resume Next" statement at the start of
your code. This error statement will note the line an error ocurrs and then
skip that line, moving to the next available code line below the line where
the error ocurred.

Best,

Matt

"mju" wrote:

Thanks Matthew for your response.
Name, Size, Color, Logo, and S_Number ---They can be number or alpha or
Alpha-numeric except S-Number is also 12 digits numeric
This is the full code:

H = 7
If Z = "Unit Needed" Then
z1 = 6
Else
z1 = 7
End If

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x


"Matthew Herbert" wrote:

Mju,

Please answer the following (which I think will resolve your own problem):

What does Name, Size, Color, Logo, and S_Number include? Are these all
numerical values?

What value represents z1? What is z1's data type?

Typically, you loop through array elements via a For Each Next or For Next
loop. For example, you can create a loop like the following:

Dim lngCnt As Long
For lngCnt = LBound(CHC) To UBound(CHC)
MsgBox "CHC(" & lngCnt & ") - " & CHC(lngCnt)
Next

Best,

Matthew Herbert

"mju" wrote:

I am working on an excel pivot table macro. How do I get the macro to do a
measurement to drop an item from an array whenever it runs out of memory. I
got this error messageïƒ* (Excel cannot complete this task with available
resources. Choose less data or close other application) and move to the next
item. The error always occurs in S_Number. How do I get it to drop S_Number
from the pivot table and continue with the rest of the macro?
The current macro includes it thus messing up my formatting.


Below is a sample of my code:

CHC = Array(Name, Size, Color, Logo, S_Number)

For x = 1 To z1
If CHC(x) = 0 Then GoTo Nex
H = H + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
ST = FieldCodes(x + 5)
Nex:
Next x