Need to modify Macro to repeat sequencial statement

**jonrack** · 06-03-2012, 10:51 AM

So in my Macro, it looks at the current date (from constant cell) then copys a Range from another sheet (also constant). I need it to paste the data in the appropriate Row depending on the date. I have it working, but was wondering if there was a way to do this without having to type a line for each day of the year. Here's what I got so far and it works, but I am sure there's an easier way to do this?

Please Login or Register  to view this content.

**zbor** · 06-03-2012, 10:51 AM

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

... and appear like this when posted

**StevenM** · 06-03-2012, 11:45 AM

Maybe something like:

Please Login or Register  to view this content.

It depends on what you need (and I'm unclear about that).

**zbor** · 06-03-2012, 11:51 AM

weeeeee, my first infraction

7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

**jonrack** · 06-03-2012, 11:59 AM

Originally Posted by StevenM

Maybe something like:

Please Login or Register  to view this content.

It depends on what you need (and I'm unclear about that).

When I change the date in Sheet "Manual Data Input" C2, it outputs data to sheet "Historical Data" in cells C1:BT1. This macro needs to copy that data and paste values somewhere on "Historical Data" C6:C371, depending on the date. There is another column on the same sheet (A6:A371) which has the dates 1/1/2012 thru 12/31/2012. I'd upload it but the file size is probably too big.
Hope that makes more sense and I appreciate any help

**zbor** · 06-03-2012, 12:11 PM

jonrack, you can always reduce file size by removing all unnecessary data from the file, leaving just part important for your request...

Do you mean this:

Please Login or Register  to view this content.

**StevenM** · 06-03-2012, 01:15 PM

It should have been +6 (instead of +5).

Please Login or Register  to view this content.

That is assuming that you are counting every day of the year (and are not excluding, for example, Saturdays and Sundays).

Otherwise, today's date - #1/1/2012# + 6 should give you the row you want.

**jonrack** · 06-03-2012, 02:38 PM

Originally Posted by StevenM

It should have been +6 (instead of +5).

Please Login or Register  to view this content.

That is assuming that you are counting every day of the year (and are not excluding, for example, Saturdays and Sundays).

Otherwise, today's date - #1/1/2012# + 6 should give you the row you want.

Hmm, didn't work. I got a "Run-time error '9' Subscript out of range" error.
But I got it working with my old code. I just wrote the code out for each day of the year and corresponding cells in excel (I did the fill down method). Then copyied it back into my macro. And it worked. Definitely a rookie move but as long as it works, right?
Thanks for the help!