Application-defined or object-defined error - Error in replicating code

[AndreB]

Dear Gurus,
I'm having troubles in replicating a set of code in the same Sub.
Basically (with the support of this forum) I've been able to complete a code section which is entitled to add some columns to a table on the basis of a difference in months.
Now I'm trying to make the same operation on another sheet/ table and I will need to make the same at least on some other worksheets.
But, I'm getting an "Application-defined or object-defined error".

Here below the code:



Private Sub InitializeProject_Click()


Dim ws1, ws3, ws4 As Worksheet
Dim dur, dur2 As Variant
Dim i, j As Long
Dim table3, table4 As ListObject
Dim stdate, stdate2 As Date
Dim colCount, colCount1 As Integer


Set ws1 = Worksheets(1)
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value


'Tab Effort


Set ws3 = Worksheets(3)
Set table3 = ws3.ListObjects("EffortResources")


With table3
For i = 1 To dur
.ListColumns.Add
colCount = .ListColumns.Count
.ListColumns(colCount).Range(1).Select
Selection.NumberFormat = "mmm\-yyyy"
Selection.Value = DateAdd("m", i - 1, stdate)
Next i
End With


'Tab Other Costs


Set ws4 = Worksheets("Other Costs")
Set table4 = ws4.ListObjects("Costs")


With table4
For j = 1 To dur
.ListColumns.Add
colCount1 = .ListColumns.Count
.ListColumns(colCount1).Range(1).Select
Selection.NumberFormat = "mmm\-yyyy"
Selection.Value = DateAdd("m", j - 1, stdate)
Next j
End With


End Sub


Could you please advise?

Thanks in advance,
A.

[snb]

Please post a sample worikbook.

[AndreB]

Here it is

[Paul_Hossler]

Seems to work ... now

Your replicate had a number if things that were not updated

Other suggestions included

(You could have avoided the issue by modularizing and having one sub with the table logic and just calling it with the right parameters for each use)



Option Explicit ' I like to use this, others may not


'personal style, but I like to keep event handlers small and call a standard module


Private Sub InitializeProject_Click()
Call InitProject
End Sub








Option Explicit ' I like to use this, others may not


Sub InitProject()


' Dim ws1 without the 'As Worksheet' assumes Variant type. I like to specifically Type variables, others may not
Dim ws1 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim dur As Variant, dur2 As Variant
Dim i As Long, j As Long
Dim table3 As ListObject, table4 As ListObject
Dim stdate As Date, stdate2 As Date
Dim colCount As Integer, colCount1 As Integer


Set ws1 = Worksheets("Project Data") ' used ws name not index
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value


'Tab Effort


Set ws3 = Worksheets("Effort") ' used ws name not index
Set table3 = ws3.ListObjects("EffortResources")


With table3
For i = 1 To dur
.ListColumns.Add
colCount = .ListColumns.Count
.DataBodyRange.Columns(colCount).NumberFormat = "mmm\-yyyy"
.DataBodyRange.Columns(colCount).Value = DateAdd("m", i - 1, stdate)
Next i
End With


'Tab Other Costs


Set ws4 = Worksheets("Other Costs") ' used ws name not index
Set table4 = ws4.ListObjects("Costs")


With table4
For j = 1 To dur
.ListColumns.Add
colCount1 = .ListColumns.Count 'you used colCount1 here, but not below
.DataBodyRange.Columns(colCount1).NumberFormat = "mmm\-yyyy"
.DataBodyRange.Columns(colCount1).Value = DateAdd("m", j - 1, stdate) ' i or j ??? j seems to work better
Next j
End With


End Sub

[AndreB]

Dear Paul,
thanks for your support.

Indeed now the code is not retrieving errors, but the outcome is not what expected.
In fact with those modifications, I have now three columns more in each sheet, but the column headers are not populated with month/year dates, while each row of the two tables are.

Here below an example of what I'm getting:

Tab Resource
Table EffortResources

Resource | .... | Column 1 | Column 2 | Column 3
| | Jan-2021| Feb-2021 | Mar-2021
| | Jan-2021| Feb-2021 | Mar-2021
........ .... ...... ...... ......
| | Jan-2021 | Feb-2021 | Mar-2021

I guess it is exactly caused by the last two lines in the loop for i and j...

Thanks in advance for your review,
AB

[Paul_Hossler]

I got rid of the .Select because the correct WS needs to be active (and there's usually no reason to .Select an object). That was the source of the run time error I think

Here's one other way



Option Explicit


Sub InitProject_ver2()


With Worksheets("Project Data")
Call AddColumns(Worksheets("Effort").ListObjects(1), .Range("C8"), .Range("C6"))
Call AddColumns(Worksheets("Other Costs").ListObjects(1), .Range("C8"), .Range("C6"))
End With
End Sub






Private Sub AddColumns(LO As ListObject, N As Long, S As Date)
Dim i As Long


With LO
For i = 1 To N
.ListColumns.Add
With .ListColumns(.ListColumns.Count).Range(1)
.NumberFormat = "mmm-yyyy"
.Value = DateAdd("m", i - 1, S)
End With
Next i
End With
End Sub

[AndreB]

Thanks a lot Paul,
it works.

Other way I found to get it was:



Private Sub InitializeProject_Click()


Dim ws1 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim dur As Variant, dur2 As Variant
Dim i As Long, j As Long
Dim table3 As ListObject, table4 As ListObject
Dim stdate As Date, stdate2 As Date
Dim colCount As Integer, colCount1 As Integer




Set ws1 = Worksheets("Project Data")
dur = ws1.Range("C8").Value
stdate = ws1.Range("C6").Value




'Tab Effort


Set ws3 = Worksheets("Effort - Baseline")
Set table3 = ws3.ListObjects("EffortResources")




With table3
For i = 1 To dur
.ListColumns.Add
colCount = .ListColumns.Count
.HeaderRowRange(colCount).NumberFormat = "mmm\-yyyy"
.HeaderRowRange(colCount).Value = DateAdd("m", i - 1, stdate)
Next i
End With


Set ws2 = Worksheets(2)


ws1.Range("B12:B31").Copy
ws3.Range("B5").PasteSpecial Paste:=xlPasteAll, Transpose:=False
Application.CutCopyMode = False


ws3.UsedRange.Columns.AutoFit


'Tab Other Costs


Set ws4 = Worksheets("Other Costs - Baseline")
Set table4 = ws4.ListObjects("Costs")




With table4
For j = 1 To dur
.ListColumns.Add
colCount1 = .ListColumns.Count
.HeaderRowRange(colCount1).NumberFormat = "mmm\-yyyy"
.HeaderRowRange(colCount1).Value = DateAdd("m", j - 1, stdate)
Next j
End With


Set ws2 = Worksheets(2)


ws4.UsedRange.Columns.AutoFit




Please let me know in case you see any error in it.

Thanks again,
A.

[snb]

Always use A1 in every Worksheet.
Start a Listonject in A1.

You could use VBA and Excel's builtin options.
In the macromodule of sheet 'Project Data'


Sub M_snb()
sp = Evaluate("transpose(eomonth(C6,row(1:" & [C8] & ")-1))")

With Sheet3.ListObjects(1).HeaderRowRange
.Offset(, .Cells.Count).Resize(, 1).Resize(, UBound(sp)) = sp
End With
With Sheet5.ListObjects(1).HeaderRowRange
.Offset(, .Cells.Count).Resize(, 1).Resize(, UBound(sp)) = sp
End With
End Sub

NB. Only 3 effective lines of code.
Remove 'Option explicit'

[AndreB]

Clear - thank you both for your support

[Paul_Hossler]

@AndreB --

Nothing jumps out at me that's really wrong

1. You do not need to escape the hyphen in Format mmm-yyyy

2. There is lot of almost duplicated code between the two blocks. A called sub would be more maintainable (post #6)

3. It seems to be a better use of the properties that ListObject give you, e.g. HeaderRow range, instead of .Select-ing objects

4. You could use .ListColumns.Count directly instead of storing it in colCount and ColCount1. Not wrong, just IMHO a little cleaner

5. I agree with snb about starting in A1, but disagree about not using Option Explicit


FWIW, my personal / preferred programming / coding style is much worder than that of some others. I find that for me it's much more readable to have 10 less complicated lines, instead 3 very complex lines with lots of nested parens, If's, etc.

CPU cycle-wise and wall-clock wise it almost always works out in the end.



However, if you can use an intrinsic Excel / VBA function, it's almost always better; less to maintain and better performance

I learned that in

http://www.vbaexpress.com/forum/showthread.php?69162-Linear-Interpolation

where I did something manually with loops that VBA's .DataSeries method would do directly, faster, with less code, and without my silly error