Pivot table Macro

[mju]

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(FieldCodes(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]

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]

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(FieldCodes(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]

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]

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]

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]

Thanks alot Matthew for your time.

yes, S_number is always 12 digits but I dont want to totally skip it.
Sometimes my header works fine with S-Number included. I think that it all
depends on the size of the data. It should skip it if is too large to fit the
pivot tables.

 

[Matthew Herbert]

Mju,

Well, either there is another way to test your data for sufficiency to be
insert into your table, or alter your On Error Statement. Did you look at
the help for On Error Statements? "On Error Resume Next" is only one flavor
of error handling. You can use the same logic in your If Then GoTo Nex
statment as in an "On Error GoTo Nex" statement. This may also provide some
help.

What is the data contained in S_Number that disqualifies it from being in
your pivot table?

Best,

Matt

 

[mju]

S-Number contains EAN numbers.

I have tried count unique. it did not work. i have tried the ones below that
u gave me and they did not work.
I got this error message:
Pivot-table report will not fit on the sheet. Do you want to show as much as
possible?

As per my research. This error occurs when the unique items are more than
32,000.

I only have 156 unique items.
Thanks alot

 

[Matthew Herbert]

Mju,

If you feel like sending me the spreadsheet, then I can take a look at it to
see exactly how the error is being generated and to determine if I can
develop a solution. If you choose to send it to me, then email meh2030 at
hotmail dot com and include something like "Mju - PivotTable VBA Help" so
that I don't send the email to trash.

Best,

Matt

 

[mju]

Thanks alot.

Please check your mailbox. i sent you an email yesterday