Solved: What is wrong with this thing??

[ctengelen]

Hi folks,

I am VERY new in VBA for Excel and I have a problem with the attached. It is a duty sheet that selects the shifts and places them from one sheet (shifts) into another sheet (duty sheet). Many folks had their hands into this project and now it has landed in my lap to see if I can get it going.

It works fine until the number 182 (2007 July 01) and than does what it wants - no rhyme or reason as far as I can see.

The VBA was created by an employee that is no longer with us. Can you help me??

Trudy:dunno

[lucas]

It has something to do with the named range sqd1_shifts
what should that named range be?

[ctengelen]

I have noted, since I deleted some rows (file was too big) it now went all hay wire. I have re-done the ranges, but that still does not seem to correct my problem. I will try to re-post the workbook.

[Norie]

Have you considered ditching the original code?

That's what I would do.

I'd sit down, work out the purpose and ultimate goal and start form scratch.

Hard work maybe but it might be worthwhile.:)

[ctengelen]

Hi Norie,

I really thought about re-doing the whole thing. I just baffles me that it works fine until a certain date (row) and then (even though we have the same ranges etc) it goes all to the toilet and picks up a different row or stalls altogether! Strange!?!?! For a VBA for Excel newbie VERY STRANGE!!! I had just hoped someone would know where the bug is sitting and laughing at me.

Trudy

[Norie]

Trudy

I realise it might be quite daunting especially for a 'newbie' but it might be better to just start from scratch.

I've not really examined the code closely but it's pretty messy.

That causes a few problems.

The main one is, what is the code meant to actually do?

The first thing to do would be to get rid of stuff like this.

Application.GoTo Reference:="DATE1"
ActiveCell.Value = A.Offset(0, -1).Value
Application.GoTo Reference:="DATE2"
ActiveCell.Value = A.Offset(0, -1).Value
Application.GoTo Reference:="DAY1"
ActiveCell.Value = A.Offset(0, 1).Value
Application.GoTo Reference:="DAY2"
ActiveCell.Value = A.Offset(0, 1).Value

Which can probably replaced with this.

Range("DATE1") = A.Offset(0, -1).Value
Range("DATE2") = A.Offset(0, -1).Value
Range("DAY1") = A.Offset(0, 1).Value
Range("DAY2") = A.Offset(0, 1).Value

[ctengelen]

Hi again Norie,

This code was created by a so called "Expert" within our organization. He is not here any longer, but his creations are! I have a good understanding of VBA for Word - but Excel is new to me. I will try my best to re-write the thing and see what happens.

Thanks for your help.

Trudy

[Norie]

Trudy

I'm sure whoever created the code had good intentions but honestly it's a mess.

It's such a mess that I'm having trouble working out what it actually does, or is meant to do.:)

That's probably me though.:bug:

If you have experience in Word VBA then I don't think it will be too hard to transfer those skills to Excel VBA.

The main difference is the Object Module.

For example in Word you'll be dealing with Documents and in Excel you'll be dealing with Worksheets/Workbooks.

PS I took over a project (it was actually just a spreadsheet really) a few years ago from a guy that was being paid ?500 a day.

This spreadsheet had code that ran all day, and I do mean all day.

Once I inherited the thing I imported the data to Access, set up a query or two and then got the same results in seconds.

Anyways, rant over.

[rbrhodes]

Hi Trudy,

Do you manually enter the Dates, then the number then the day name etc?

If you look at your first posted sheet:

B41 = 181
A41 is a date in excel format

B42= 182 BUT
A 42 is a text string _describing_ a date. It is not a date.

Everything after A41 is a textual _description_ of a date...


Col A:

I think I would put a formula in A42: =A41+1

Copy/paste A42 down as far as I wanted.

Then, with the newly copied cells selected, use Pastespecial/Values to convert the formula to dates.

Or use Filldown handle: Filldown is the little black box in the bottom right corner of a cell that appears when a cell is selected. Select A42, click on the little black box and drag down and the cell is automatically copied/pasted. Then with the cells still selected, click Copy or Ctrl+C. Then click Edit/Patespecial/Values. This converts the formula to actual dates.

Col B

Starting in A42:

Formula would be =A41 +1.

Copied/Paste or FillDown down as needed .

Then Copy /Pastespecial to convert formula to values

As for Col C. In C6 I would put formula =A6

Format cell as Custom "dddd"

Copy formula down as far as needed.

Optional: select cells and use Pastespecial/Values again


The code is spaghetti at best. I'll look at it cause I'm a curious...fellow.

Speaking of curious are you City or federal?

Cheers,

dr

[Paul_Hossler]

July 1, 2007 (entry 182) only looked like a date, but it was a string formated to look like a date.

Filling the last real date (entry 181) down to the end used real dates.

I could run the macro without errors, but I agree with the others that the code structure could be improved, but this seems to work

Here's a "real date" file

Paul

[ctengelen]

Hello Dr,

When I click on the Macro button on Duty Sheet, I fill in the number corresponding with the date I want - the macro then is to copy the date on the bottom (row 48) of the Duty Sheet. Then go to the designated Ranges on the Shifts Sheet, copy and special paste them into the designated Ranges in the Duty Sheet. This Project had so many hands into it that it is hard to say which way dates and times etc were entered. I will try your suggestions.
Thanks again.
Trudy

[ctengelen]

Hi Paul,

The macro seems to run alright until I ask it to go to the number 175 in the B column and it happily picks up information about the number 145 row, but picks up the right date! This is soooooo strange! I did some changes to it and come up with different problems every time. My very first EXCEL VBA and such a problem child.

I think the best way to tackle this is to start from scratch - type in the information fresh and then do my ranges, macros etc. This way I know how all the cells are formatted. This will take some time, but with the auto-fill should not be too bad.

Thanks for all of your help. I will let you know if I am successful or utterly fail.....?!?

Your humble Newbie
Trudy

[ctengelen]

Hello again Dr,

Well, this is Edmonton City Police.....I am now trying to re-do the while thing as any change I make to the original seems to throw one thing or another right out of whack. Excel is all about logic - there is nothing logic about this project.

Trudy

[Norie]

Trudy

Not what you probably want to hear, but I think the data structure could do with a bit of a fixing too.:)

PS If you are going to start over please post here with any specific questions, we'll be glad to help.

[rbrhodes]

Hi Trudy,

I always go head first into a good problem. The problem here being that I had no idea how this sub was supposed to work! Having figured that out, I asked myself what would I do with it given the data layout? Here's an example of what I came up with.

2 Notes:

Sheet "Shifts" AA6 to AJ6 are now blank. They had the First Names in them which I moved up one row to match everything else.

Enf Squad 1 only had 9 Columns and Squads 2 & 3 had 10 Columns each so I left a free Col (Col Z) at the endof Squad1 listing just in case Squad 1 was supposed to have 10 personnel as well.

The macro I wrote is heavily commented, so you should be able to see what it does Essentially it repeats it self, a lot. That's good!

Enjoy VBA, good luck and HTH!

Cheers,

dr

[ctengelen]

:bow:
Good Morning Dr,

Thank you, thank you, thank you.....you are the best. I am sooooo glad that you commented every step so I can see what it does...and it does it so well!!!! :beerchug: and I can learn from it! Looking at your code it makes much more sense and I can even follow right along.

Well, I suppose it is time to wrap my head around VBA for Excel, wonderful stuff.

On :cloud9:
Trudy

[ctengelen]

To one and All that are here to help us not so smart in VBA folks!

I want to thank all of you and now that the pressure is off - I will re-create the project from scratch, using what I just learned.

My simple motto in my life is: If I have not learned something new in a weeks time, it was a wasted week. This VBAEXPRESS forums are the BEST for a newbie or for some that just need a leg up.

Thanks again kind folks.

Trudy