Inserting columns with dates not working as desired 1

[ColdDay]

I am wanting to insert columns and missing dates in row 1. Example: Starting in B1 and through to E1 (4 cells) I have Jan 1, Jan 2, Jan 5 and Jan 6. I want to insert columns and dates Jan 3 (D1) and Jan 4 (E1). This will put the 5th and 6th in cells F1 and G1 respectively. Column A is a list of names and the related rows are hours.

In this example, when I run the following code, “i” value stars with 7, tmp value starts at 0, then on the first pass through tmp it is 41,279.

Here is the code as a whole, with the problem lines noted at the end.

Public Sub InsertMissingDatesByColumn()
Const Date_Row As String = "1" '<=Row WITH DATES
Dim i As Long
Dim LastColumn As Long
Dim tmp As Long

Worksheets("Sheet1").Select
With ActiveSheet.UsedRange
LastColumn = .Columns.Count
Range("B1").Select

For i = LastColumn To 1 Step -1

If .Cells(Date_Row, i).Value <> .Cells(Date_Row, i + 1).Value And _
.Cells(Date_Row, i).Value < .Cells(Date_Row, i + 1).Value - 1 Then

tmp = .Cells(Date_Row, i + 1).Value
.Columns(i + 1).Resize(tmp - .Cells(Date_Row, i).Value - 1).Insert
.Cells(Date_Row, i).AutoFill .Cells(Date_Row, i).Resize(tmp - .Cells(Date_Row, i).Value)

End If
Next i
End With
End Sub
‘---------

‘after the following line runs, cells are placed in D1 (the date row) and D2
'(the first row containing a name and hours) and the previous info in D1 and D2
'is moved to the right one cell. Info in D3, D4, etc and below is not moved or changed.

.Columns(i + 1).Resize(tmp - .Cells(Date_Row, i).Value - 1).Insert

‘after the following line runs, the dates for the 3rd and 4th are inserted
'into cells C2 and C3 replacing the hours that were in them. The hours are
'not moved to the right in these cells, they are replaced by the dates.

.Cells(Date_Row, i).AutoFill .Cells(Date_Row, i).Resize(tmp - .Cells(Date_Row, i).Value)

‘--------------------------

Where am I going wrong?

TIA.

Bill

 

[SkipVought]

Hi,

Seems like a Rube Goldberg solution (Google it if you don't know what that is)

First off date_row ought to be long not string.

Let's start from the top.
Where did your data come from, that it is missing dates?
Will the values for these new dates be actual meaningful values?
Please post a sample of before & after table data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ColdDay]

Skip, I’ll try to keep this short (which can be a challenge for myself) but informative.

The data I am wanting to manipulate is coming from a proc that I wrote (with I believe your help) some six or seven years ago, maybe longer. For all this time, the missing dates (for weekends, holidays and snow days which do not have any hours to report) did not need to be displayed on the printed report that was sent to the county. Since there were no hours for these dates, the original data from the payroll program does not include these dates. No work = No hours.

Starting in Jan/13, the county devised their own spreadsheet that we must enter the data into and they have included all dates of the month (1 to 31). Using Jan as an example, the first will be an empty column (New Years Day) underneath the date of Jan 1. Jan 2, 3 and 4, will have hours listed, the 5 and 6 are the weekend and are on the sheet but with no hours and starting on Monday the 7 thru the 11, hours will be listed again in the data, with the 12 and 13 as weekends having no data under the date. The same till the end of the month. (I know how to get the Jan first listed if there are no hours.)

What I am trying to do is not change any of my original program b/c there are some other reporting requirements that we use that the county does not need and I do not want to change how this info is calculated.

What I was hoping to do is run my original program (create Sheet A with the missing dates as I do now), copy and paste this finished spreadsheet of info to a new sheet (Sheet B), and then programmatically insert the columns for the missing dates. (There would be no hours listed for the inserted date columns.). I would then do one big copy and paste of all the info (Dates with hours and dates w/o hours) into the counties sheet.

The counties sheet then totals the daily hours rows across in column AG and then a grand total of these in cell AG24 on page one. NOTE: The “X’s” are needed on my original sheet, but when they are copied over to the counties sheet I will run a proc to clear them out.

This is a partial month of what I start with on my sheetA. I made up the data below so in my example Jan 1 has hours, but in reality it would not.

A B C D E
Name 01/01/13 01/02/13 01/05/13 01/06/13
Bob 6 6 X X
Dennis 1 2 5 6
Steve 2 X 5 2

I want to insert the missing dates (3rd and 4th) so I have this. The 3rd and 4th have no info (including no zero or X), but the columns have been inserted.

A B C D E F G
Name 01/01/13 01/02/13 01/03/13 01/04/13 01/05/13 01/06/13
Bob 6 6 X X
Dennis 1 2 5 6
Steve 2 X 5 2

But I’m getting this:

A B C D E F G
Name 01/01/13 01/02/13 01/05/13 01/06/13
Bob 6 01/03/13 X X
Dennis 1 01/04/13 5 6
Steve 2 X 5 2


If things did not format properly in the above, D1 and 2 are empty, col G is empty, C2 and 3 have dates but should not, D3 and 4 have hours but should not, Columns D and E should be Jan third and fourth with no hours, Cols F and G should be the fifth and sixth with the hours that are in D3 and 4 and E3 and 4.

One other hitch is that page one of the counties sheet has room for only 23 workers. Then it totals those 23 plus any others that were continued on page two. In other words, cell AG24 has the total for those on page one plus those on page two.

Thanks.

 

[ColdDay]

I see that things did not format and display as desired.

 

[SkipVought]

Your data would be a LOT more useable if you source data were in ONE table, structured like...
[tt]
Name DateWorked Hrs
[/tt]
Then you have a query that formats a weekly report just like the county poobahs want.

Your proc may need to be reworked. You REALLY need data that looks like the example I proposed, as a source.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ColdDay]

The data comes out of the payroll program as you suggest and I do some manipulating on it b/f I create the pivot tables. (More than one shop's info.) Let me get back into the original program and see if I can get it to do what I need b/f the PT's are created.

Thanks Skip.

 

[zelgar]

Just did something similar in another forum...

Sub fill_in_data()
'
'
Dim i, icol, diff, slope_end As Integer
' Start irow = to 2nd value of data
icol = 3
Do Until IsEmpty(Cells(1, icol))
    diff = Cells(1, icol).Value - Cells(1, icol - 1).Value
    slope_end = Cells(2, icol).Value
    If diff > 1 Then
        For i = 1 To diff - 1
            Columns(icol).Insert
            Cells(1, icol) = Cells(1, icol + 1).Value - 1
        Next i
    End If
    icol = icol + diff
Loop
'
End Sub

 

[zelgar]

oops.... the dim statement should read
' Start icol = 2nd value of data

The previous version of this macro was inserting rows of dates, not columns. Note, the code works fine even with this error.

 

[SkipVought]

FYI,

Dim i, icol, diff, slope_end As Integer

This statement declares i, icol & diff as Variants by default.

Only slope_end is declared as integer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

The macro probably should be modified to be the following to be as clean & accurate as possible to the following:

Sub fill_in_data()
'
Dim i As Integer
Dim icol as Integer
Dim diff as Integer
' Start icol = to 2nd value of data 
icol = 3
Do Until IsEmpty(Cells(1, icol))
    diff = Cells(1, icol).Value - Cells(1, icol - 1).Value
    If diff > 1 Then
        For i = 1 To diff - 1
            Columns(icol).Insert
            Cells(1, icol) = Cells(1, icol + 1).Value - 1
        Next i
    End If
    icol = icol + diff
Loop
'
End Sub

The slope_end varible isn't necessary in this maco.

 

[ColdDay]

I went back and looked at the original data coming from the payroll program as well as the current proc used to report with to the county. The only way to deal with the original data is to manually enter the desired dates, but then when the current proc is run it will skew the report info we need for in house reporting. I looked at the current proc and to make changes in it would require a major overhaul (at least for me) and I'm not up to that. I believe that my best path is to get the data we've been getting and then massaging it just a bit more to please the county. With that said, here is what I have been working on with some luck, but not enough.

Data: A1 = Name, B1 = 41275, C1 = 41276, D1 = 41277, E1 = 41280, F1 = 41281

Need to enter two columns and dates: 41278 and 41279.

Public Sub InsertMissingDatesColumn()
Const Date_Row As Long = "1"    '<=ROW WITH DATES
Dim i As Long
Dim LastColumn As Long
Dim tmp As Long 'used in proc
Dim tmp2 As Long 'only to see value in cell

Worksheets("Sheet1").Select
With ActiveSheet
Range("A1").Select  'value in A1 is "Name"
LastColumn = .Cells(Date_Row, .Columns.Count).End(xlToRight).Column

For i = LastColumn To 1 Step -1 '<<Step???

'On subsequent run throughs, get type mismatch b/c cell A1 = Name and _
    it is a string, not a number.
If .Cells(Date_Row, i).Value <> .Cells(Date_Row, i - 1).Value And _
         .Cells(Date_Row, i).Value > .Cells(Date_Row, i - 1).Value + 1 Then
     
'at this point, "i" = 5 (col E) tmp2 not used in proc. for info only
     tmp = .Cells(Date_Row, i - 1).Value  'col 4.  value = 41277
     tmp2 = .Cells(Date_Row, i).Value     'col 5   value = 41280
'Stop
      
'bad coding using 25000.  plus it inserts only one column
    .Columns(i).Resize((.Cells(Date_Row, i).Value + 25000) - tmp).Columns.Insert
'at this point,"i" = 5 (col E) which is now empty. info moved in col F.

'Stop
    'Tried the following:
    '.Cells(Date_Row, i) = .Cells(Date_Row, i - 1).Value + 1
    
    'by using the above line, "41278" has been entered into E1, which is good, but _
        as it continues to loop thru will get type mismatch noted above _
        b/c  "i" will equal 2 for the 2nd col and (i - 1).value (which is cell A1) _
        will equal "Name". "Name" is col heading. _
        Still need another col inserted at col F with the value "41279".
    
      
'tried using the following. get error "Application defined or object defined error".
    ' "i" = 5, tmp = 41277
    .Cells(Date_Row, i).AutoFill .Cells(Date_Row, i).Resize(Cells(Date_Row, i).Value) - tmp
'------------------------------------------
    End If
    Next i
End With
End Sub

TIA for the help.

 

[PHV]

At least I'd replace this:
For i = LastColumn To 1 Step -1
with this:
For i = LastColumn To 3 Step -1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

PHV,

That fixes it with the exception of looping through a second time or more when more than one date is missing. Any thoughts on the AutoFill?

Thanks a bunch!

 

[zelgar]

Did you try my macro, it does work.

For your macro, try changing this line
.Cells(Date_Row, i) = .Cells(Date_Row, i - 1).Value + 1
to
.Cells(Date_Row, i) = .Cells(Date_Row, i + 1).Value - 1

 

[zelgar]

Also, with your code, since you're addiding columns you'll need to add i = i+1 after you insert the date. If you don't do this, you'll still have skipped dates if there is more than one date missing in your original data.
(e.g., in your post showing D1 = 41277, E1 = 41280, when i=5, it would create a new column E and insert 41279. You want your code to then look the values in D1 = 41277 and the new E1 = 41279 to create a new column E and insert 41278. In order for it to look at col E, i=5.)

 

[ColdDay]

Zelgar,

Don't know how or why, but I missed your macro. It does work! Thanks!

 

[zelgar]

No problem, I think you're macro would work with just a few more tweaks (see the ones I listed above). They both worked the same way of inserting a column, but I was going from Start to End and you were going from End to Start. The biggest difference is my macro would insert the dates of a group of missing days together, while yours would do them one at a time.