VBA - With each table on Activesheet

[Finalfight40]

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?

 

[Finalfight40]

Just a follow up, it looks to be happening on tables which are empty.

 

[Peter_SSs]

I have not been able to reproduce that error. Can you confirm the full error message?

.. on some sheets i am receiving a run time error '91'

This indicates that you are possibly running the code over more than one sheet. If so, perhaps you have modified the code in some way? If so, can you post the code that you are using?

One thing to note is that Evaluate can cause problems if the sheet it is supposed to be working on is not the active sheet, though I still wouldn't expect the error message that you have reported.

 

[Finalfight40]

Hi Peter

The only thing i added was something like:

Range("G1").select
Range("F1").select

at the end just so that it would run the on worksheet change event (or one of the events) afterwards.

I can repost when i get back into work.

However just as a quick test to recreate this error is to create a table and have 'Start Date' and the third table header. Don't enter anything into the table and try to run the code.

 

[Peter_SSs]

However just as a quick test to recreate this error is to create a table and have 'Start Date' and the third table header. Don't enter anything into the table and try to run the code.

I had already done that after you first reported the error. That does not error for me & just leaves the table as it was.

 

[Finalfight40]

I have created a new spreadsheet so that i can replicate the issue and share it with you. I have uploaded it to dropbox and this link is:

https://www.dropbox.com/s/w5r90t07otlfs37/Peter Tables.xlsm?dl=0

Also the full error message i was receiving was:

Run-time error '91':
Object variable or With block variable not set.

 

[Peter_SSs]

OK, thanks. I can now see what the problem is. Although I had not entered any data in my 'blank' table, my table did have a few (empty) rows. Your table has no "DataBodyRange" hence the code fails.
Try adding the blue code

If LO.ListColumns.Count > 2 And LO.ListRows.Count > 0 Then

 

[Finalfight40]

Thank you for your help and time with this Peter. It seems this has now resolved my issue.

 

[Peter_SSs]

Thank you for your help and time with this Peter. It seems this has now resolved my issue.

Cheers, glad we resolved it in the end.