Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VBA - With each table on Activesheet

  1. #1
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - With each table on Activesheet

    Hi

    I am trying to set something up in VBA where it would cycle through each table on the Activesheet and if the 3rd column header says "Start Date" then add 7 for each element on the 3rd column of that table.

    How would this be written in VBA?
    Last edited by Finalfight40; Oct 9th, 2019 at 06:47 AM.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,188
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Perhaps something like this.
    Code:
    Sub Add7ToStartDate()
    Dim tbl As ListObject
    Dim col As ListColumn
    Dim rng As Range
    Dim arrData As Variant
    Dim idx As Long
    
        For Each tbl In ActiveSheet.ListObjects
        
            If tbl.ListColumns.Count > 2 Then
            
                If tbl.ListColumns(3).Name = "Start Date" Then
                
                    Set rng = tbl.ListColumns(3).DataBodyRange
                    
                    arrData = rng.Value
                    
                    For idx = LBound(arrData) To UBound(arrData)
                    
                        arrData(idx, 1) = arrData(idx, 1) + 7
                    Next idx
                    
                    rng.Value = arrData
                    
                End If
                
            End If
        Next tbl
        
    End Sub
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - With each table on Activesheet

    o Norie.

    Thank you so much this works perfectly.

    I haven't had any experience with tables in VBA so i really appreciate it.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Another option without cycling through each row value in the relevant tables I think still does what you want.

    Code:
    Sub Add_7_To_Start_Date()
      Dim LO As ListObject
      
      With Cells(Rows.Count, Columns.Count)
        .Value = 7
        .Copy
        For Each LO In ActiveSheet.ListObjects
          If LO.ListColumns.Count > 2 Then
            If LO.ListColumns(3).Name = "Start Date" Then LO.ListColumns(3).DataBodyRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
          End If
        Next LO
        .ClearContents
      End With
    End Sub
    Last edited by Peter_SSs; Oct 9th, 2019 at 07:22 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Quote Originally Posted by Peter_SSs View Post
    Another option without cycling through each row value in the relevant tables I think still does what you want.

    Code:
    Sub Add_7_To_Start_Date()
      Dim LO As ListObject
      
      With Cells(Rows.Count, Columns.Count)
        .Value = 7
        .Copy
        For Each LO In ActiveSheet.ListObjects
          If LO.ListColumns.Count > 2 Then
            If LO.ListColumns(3).Name = "Start Date" Then LO.ListColumns(3).DataBodyRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
          End If
        Next LO
        .ClearContents
      End With
    End Sub

    Hi Peter

    For one of my tables i got an error which was :

    Paste method of range class failed and i have changed to red above where this error was received.

    Also i am going to explain a little more in my next post. I am not doing it in this 1 as i replied to you and don't want Norie or anyone else to skip over it.Also i am going to explain a little more in my next post. I am not doing it in this 1 as i replied to you and don't want Norie or anyone else to skip over it.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  6. #6
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Hi Norie and all

    I have had an run time error '91'.

    I believe this is where the tables are currently empty.

    I want to expand and mention that these tables can have nothing in them. Also the table might currently only have 1 element in it. The final thing to mention is that the tables might be populated with 100 rows but have some elements in this column which are blank which i do not want to change.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Quote Originally Posted by Finalfight40 View Post
    I believe this is where the tables are currently empty.

    I want to expand and mention that these tables can have nothing in them. Also the table might currently only have 1 element in it. The final thing to mention is that the tables might be populated with 100 rows but have some elements in this column which are blank which i do not want to change.
    These are all relevant facts.
    Does this do any better?

    Code:
    Sub Add_7_To_Start_Date()
      Dim LO As ListObject
      
      For Each LO In ActiveSheet.ListObjects
        If LO.ListColumns.Count > 2 Then
          If LO.ListColumns(3).Name = "Start Date" Then
            With LO.ListColumns(3).DataBodyRange
              .Value = Evaluate(Replace("if(#="""","""",#+7)", "#", .Address))
            End With
          End If
        End If
      Next LO
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Quote Originally Posted by Peter_SSs View Post
    These are all relevant facts.
    Does this do any better?

    Code:
    Sub Add_7_To_Start_Date()
      Dim LO As ListObject
      
      For Each LO In ActiveSheet.ListObjects
        If LO.ListColumns.Count > 2 Then
          If LO.ListColumns(3).Name = "Start Date" Then
            With LO.ListColumns(3).DataBodyRange
              .Value = Evaluate(Replace("if(#="""","""",#+7)", "#", .Address))
            End With
          End If
        End If
      Next LO
    End Sub
    Thank you Peter. This works perfectly for me at the moment, i will let you know if i run into any issues.

    I was unaware the 1 element and no elements were going to be relevant.

    As for the blanks i was hoping to try to adapt what someone provided so that i could learn to adapt what was provided. This i managed but when i got the error above i thought now i better provide everything just to be sure.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Quote Originally Posted by Finalfight40 View Post
    Thank you Peter. This works perfectly for me at the moment, i will let you know if i run into any issues.
    You're welcome. Hope it is robust for you.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - With each table on Activesheet

    Quote Originally Posted by Peter_SSs View Post
    You're welcome. Hope it is robust for you.
    Hi Peter

    i have just found that on some sheets i am receiving a run time error '91' and the line that is being highlighted is:

    Code:
    .Value = Evaluate(Replace("if(#="""","""",#+7)", "#", .Address))
    Do you know what might be causing such a thing?
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •