• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help 4 vba for inserting a column in existing table, rename its header and set totalrow calculation

C

cricketnz

New Member
Hi

Can somebody please help and explain why below code works on a small table (excel 2010) but not on a big table?
Code: 
Sub insCol()
Dim oSh As Worksheet
Dim oLc As ListColumn
    Set oSh = ActiveSheet
    oSh.ListObjects("Table1").ListColumns(3).Range.Select
    Set oLc = Selection.ListObject.ListColumns.Add(3)
    oLc.Name = "new header"
    oLc.TotalsCalculation = xlTotalsCalculationSum
End Sub
in the case of the big table the column gets created but the code starting at oLc.Name gets ignored.

Hope somebody can point me straight on this
cheers
 
I doubt that it is the table size that is an issue

It is more likely that a field or header already exists, or that Table 1 doesn't exist

Can you post the samples of where it works and doesn't
 
Hi Hui,

I copied the big table on to a new sheet but with fewer rows. Amazingly the code now works. However that also proves to me that the problem is not that the field or header already exists or that table 1 doesn't exist. The weird thing really is that when I step into the code the execution stops after the column is inserted. I also changed the name of the variable oLc to NewCol to no avail.
It is hard to share the sheet as the info is confidential

Cheers,

Jim
 
Hi Jim ,

There is a small gap in your testing , which you can complete by copying the same big table , without reducing the number of rows , to a new sheet.

If it still works , then you will have to admit that it has nothing to do with the size of the table.

Narayan
 
Hi Hui and Narayan,
I greatly appreciate your help
Looking at below code
Code: 
Sub InsertCol()
Dim oSh As Worksheet
Dim oLc As ListColumn
    Set oSh = ActiveSheet
    Set oLc = oSh.ListObjects("Table3").ListColumns.Add(30)
    oLc.Name = InputBox("Enter the month and year for the header of this new column", "inserting new month column") & " No of Sessions" 
    oLc.TotalsCalculation = xlTotalsCalculationSum

End Sub
All lines are on full separate lines
I notice that the column inserted is not a table column but stretches right to the bottom of the sheet, even though it's in the right place.

Narayan, you were correct that the size of the table has nothing to do with the succesfull execution of this code. I have done the test you suggested.

This still leaves me stunned though why this code is not working in the original spreadsheet and acts so strangely. Are there any things I could look out for?
Could data-validation, in the column next to it, have something to do with it?
 
Hi Jim ,

I tried your code on a table , and I get an additional column , which is a table column ; it does not stretch right to the bottom of the sheet.

There are so many possibilities , including merged cells , other tables beneath the table on which you are running this code , if there is data in the last column / last row ,... though these will not explain why the code manages to insert the column and then fails.

If you can just clear your worksheet of all data , but with everything else in it intact , and upload it , it might be easier to pinpoint the problem.

Narayan
 
Hi Narayan,

here's my file. The macro that causes the problem is called InsertCol

Many thanks in advance for looking into this
 

Attachments

  • BOP testcopy.xlsm
    88.4 KB · Views: 6
Hi Jim ,

Now things are clear ; I deleted all of the other code in your module , except for the InsertCol procedure ; I ran it without any problem.

The procedure I tried out was this :
Code: 
Option Explicit

Sub InsertCol()
    Dim oSh As Worksheet
    Dim oLc As ListObject
  
    Set oSh = ActiveSheet
    Set oLc = oSh.ListObjects("Table3")
    With oLc
         .Range.ListObject.ListColumns.Add (3)
         .ListColumns(3).Name = InputBox("Enter the month and year for the header of this new column", "inserting new month column") & " No of Sessions"
         .ListColumns(3).TotalsCalculation = xlTotalsCalculationSum
    End With
End Sub
It is the rest of the section which is causing this macro to fail. Of course , you need to change the 3 to 30 or what ever is the other location number where you want the new column to be inserted.

You need to go through the other macros in the module , and find out what is the problem.

If you want me to go through it , let me know.

Narayan
 
Hi Jim ,

Some more feedback ; the problem is with the IsFormula function ; removing this function makes the InsertCol procedure run to completion successfully.

Narayan
 
Last edited:
Hi Narayan
Many thanks for your investigative work.
Is there any way that I can change the isformula function as I need this to color all cells that have a formula in it? Or is there a better way to color all cells that have a formula in it? I presume that this function uses too much memory, correct?

Many thanks anyway. You are a champion!
Cheers,
Jim
 
You must log in or register to reply here.
Back
Top