Excel Macro Question 2

[CurtR]

I have a list of 1253 Vehicle numbers in Excel. I need to end up with
Vehicle Number week # Week Start Date Week end Date
0057 1 12/30/2001 01/05/2002
0057 2 01/06/2202 01/12/2002

I have created seperate columns of data for vehicle Number another for week# and one for each of the date Columns. I hoping that there is a way to write a macro to create this table. I am estimating that I will be Close to the 65000 record mark when done.
As each vehicle number will take up 52 rows.
Can anyone get me point in a direction. I am starting here asking for help, (cause I do not have a clue)before I venture off and attempt this! I hope that I expalined this well enough?
Thanks
Curt

 

[DaleWatson123321]

Curt,

The first thing I have to question, is... Do you really need a separate column for the Week ?

i.e. Do you HAVE to have 52 records for each and every vehicle ?

If there will not necessarily be data to enter for each and every week for each and every vehicle, then I would (humbly) suggest that you should leave out the Week.

Then, in you are required to produce reports by Week, Bi-Weekly, Monthly, etc, then you can expect to be able to do so by selectively extracting the data to a separate sheet, based on "criteria" that specifies the "time frame" for a particular report.

Does this make sense ? Please advise, and if you want to head in the direction I've suggested, I can provide further help with the code required for selectively extracting data to a separate sheet.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

Curt,

I'm curious why you are creating a worksheet containing so much redundant information. Are there additional columns beyond 'Week End Date' that will be filled in? Perhaps if you explain the end use it would help.

A macro can be written to fill in what you have presented. What is the range & format of Vehicle Numbers (i.e., 0001 - 1253 inclusive)?


Regards,
M. Smith

 

[CurtR]

Dale,
They need all the info I mentioned plus some.
I will email you a same of what I was given.
Thanks
Curt

 

[SkipVought]

CurtR,

Is what you are telling us that...

for Each VIN, and

for Each Week #

there will ALWAYS be data (other than Week Start Date & Week End Date)???

ALWAYS -- there will, WITHOUT EXCEPTION, be data for EVERY VIN, EVERY single week of the year???? Skip,

 

[CurtR]

The County is trying to create a method into which everyone is to place the odometer readings for their vehicles every week, along with check boxes to mark the days the vehicle was used during the week.
They are doing this in Lotus Notes. So basicly there will be a template (Form) for Each Vehicle, and Lotus will select the "Correct Form" based on the week Number.

Each of these forms will Contain:
UNITID totalmileage MFG week# dayused MODELNO
startdate totaldays DEPT enddate UNITTYPE DIVISION tartmileage LICENSE COMMENTS endmileage MODELYR DRIVER.
(The Data for the vehilces as is housed in an Oracle Data Base. This was dumped into excel, and given to me to figure out a way to turn one line of data for each of the 1253 vehicles into 52 lines of redundant data for each vehicle, plus insert the week# and the dates of every Sunday and Saturday! Ya Gotta Love it !)

I can email a small sample for anyone Interested.
Thanks For your help
Curt

 

[SkipVought]

You ought to be storing this kind of data in a database like MS Access or Oracle. With this many rows (65,000+), databases grow, and you are pushing the limits of Excel.

What happens when the county purchases 8 more vehicles?

Actually, the normalized data you ought to be collecting in an Activity Database from what you have discribed is...

VIN
DateUsed
StartMileage
EndMileage

From that data, the info regarding a week can be derived. BUT this structure will be way more than 65,000 rows.

But if you )or your boss) insists on such an imprudent route, here's how to turn one row into 52...

Sub OneTo52()
    Dim wsVIN As Worksheet, wsActivityVN As Worksheet, rgVIN As Range, VIN As Range
    Dim lRowActivityVIN As Long, lRow As Long, iCol As Integer, iYear As Integer, dStart As Date
    'enter your sheet names here
    sName = InputBox("Sheet name for VIN List")
    Set wsVIN = Worksheets(sName)
    sName = InputBox("Sheet name for VIN Activity")
    Set wsActivityVN = Worksheets(sName)
    iYear = InputBox("What year, yyyy?")
    
    dStart = DateSerial(iYear, 1, 1)
    dStart = dStart + 7 - dStart Mod 7
    
    Set rgVIN = wsVIN.Cells(1, 1).CurrentRegion
    
'this assumes that your first row will contain headings
    lRowActivityVIN = 2
    For Each VIN In rgVIN
        iCol = 1
        For lRow = 1 To 52
            For iCol = 1 To 3
                Select Case iCol
                    Case 1
                        wsActivityVN.Cells(lRow + lRowActivityVIN - 1, iCol).Value = VIN.Value
                    Case 2
                        wsActivityVN.Cells(lRow + lRowActivityVIN - 1, iCol).Value = lRow
                    Case 3
                        wsActivityVN.Cells(lRow + lRowActivityVIN - 1, iCol).Value = dStart + (lRow - 1) * 7
                End Select
            Next
        Next
        lRowActivityVIN = lRowActivityVIN + 52
    Next
    
End Sub

Hope this helps Skip,

 

[CurtR]

Trust Me ! this is the boss, Cause he just start learning notes and the best he can do at this point is calling for this dataset. All of this data already resides in an Oracle Database with a Hansen Front end.
I started this VB stuff This AM
Like I told Dale, I'll Stick to Select Statements, Nested If Statements and the Like!
I did this, this am trying to get started in piecing this stuff togther. Figuring once I got the 52 rows of redundancy that I could figure out to insert the Week# and Dates from there.
Sub Insert_Blank_Rows()

'Sets x to 1 and adds 1 to x until x=52. Loops 52 times
For x = 1 To 52 Step 1
'Select last row in worksheet.
Selection.End(xlDown).Select

Do Until ActiveCell.Row = 1
'Insert blank row.
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert shift:=xlDown
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop
Next
End Sub
Which I still have problems with Case it Don't Stop, just a big ol'infinite LooP. Though it did do everything but stop!

Thanks
For All of your help Guys!
Curt

 

[SkipVought]

Curt Curt Curt,

Did you check out my code? No infinite loop, guy! Skip,

 

[CurtR]

Skip,
Just ran your code, Very nice ! ,even prompts for date!
But when I ran it ,I get all of the vehicle Info in one Column and Week#'s in the Next, Then The Dates in the Next
It reads across the Columns, instead of down

I get Vin# Week# Date
Make Week# Date
Year Week# Date

Instead of
Vin# Week# SundayDate SaturdayDate Make Year etc.
Vin# Week# SundayDate SaturdayDate Make Year etc.
Vin# Week# SundayDate SaturdayDate Make Year etc.

Man, you guys deserve a lot of credit, I can follow alot of this, but at Least what I get to write is in English ! )

Thanks Skip
Curt

 

[SkipVought]

Ahhhh,
Your LIST if VIN's contains other stuff beside VIN. Why?

The data you are referring to...
Make
Year

are NOT ACTIVITY DATA: that data is VEHICLE DATA. Does not belong in an ACTIVITY database -- that data is in your Oracle db. Skip,

 

[CurtR]

Not Sure why all the static data is going into this. Not my call (
I'm Just the Grunt at the Bottom of hill that is suppose to catch all this Uh.. Stuff as it comes rolling Down!

 

[SkipVought]

Then you will have to ...

1. Change setting the rgVIN object as follows...

    With wsVIN.Cells(1, 1).CurrentRegion
        Set rgVIN = wsVIN.Range(wsVIN.Cells(1, 1), wsVIN.Cells(.Rows.Count, 1))
    End With

2. Change loading the cells with the appropriate data. It depends what columns are in the source data and where the data goes in Activity. I need to know more!

In my company, a programmer can challenge dumb stuff that users think that they need on technical grounds as long as the results can be achieved. It's called, "you're good at what YOU do and I'm good at what I do", ie the techie stuff. "I won't tell you how to do your job, so don't tell me how to do mine!" Skip,

 

[CurtR]

Yeah, I did that, What Can I say not all stories have Happy Endings! You want to talk about hard headed people!
I while being lost, and playing with the code you sent. Like I said I can follow some of it. But not all of it.
if you'd like to see what I am looking at send me and email and I will send out a small sampling .
I'm at croberts@mc.rochester.lib.ny.us

 

[CurtR]

I want to stop back and Thank Skip, and Dale For their Help
Dale Really went way beyond " HELP " and came up with a solution to the problem that did what we needed plus alot more.
Thanks Guys!
Curt