|
Pivot table Macro
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 |
Pivot table Macro
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 |
Pivot table Macro
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 |
Pivot table Macro
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 |
Pivot table Macro
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 |
Pivot table Macro
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 |
Pivot table Macro
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" wrote: 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 |
Pivot table Macro
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" wrote: 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" wrote: 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 |
Pivot table Macro
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" wrote: 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" wrote: 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" wrote: 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 |
Pivot table Macro
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Pivot table Macro
Thanks alot.
Please check your mailbox. i sent you an email yesterday "Matthew Herbert" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 03:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com