Excel XP- Current Week Number 1

[enbw]

Hi,

I have large spreadsheet which contains a list of works orders with promised week per row. I want to program a macro which will select and copy certain rows. Basically any row that has a week number previous to the current week.

Is there a macro function for the current week number in the format of wknumber/year > 4204

Thanks in advance.

 

[MeGustaXL]

Hi there enbw!

Assuming you've got a date in A1, like, "13-10-2004", put this in B1:

=WEEKNUM(A1,2)&RIGHT(YEAR(A1),2)

and the result should be 4204 - is that any good?

Chris

Varium et mutabile semper Excel

 

[TonyJollans]

Hi enbw,

The WEEKNUM function, in the Analysis ToolPak, should help you:

[blue][tt] =WEEKNUM(TODAY())[/tt][/blue]

.. will give you the week number. To append the year, format the date as year only ..

[blue][tt] =WEEKNUM(TODAY())&TEXT(TODAY(),"YY")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[GlennUK]

Can you explain what is in the data currently :

a list of works orders with promised week per row

If you tell us how the "promised week" is stored, someone will be bound to come up with an idea or two.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MeGustaXL]

Tony,

HWGA with that multi-pelted feline!

Chris

Varium et mutabile semper Excel

 

[enbw]

Hi Chris,

Thanks but its not quite what I am looking for. Perphaps, I should explain myself better.

Basically, I have got the spreadsheet with all this data.

What I want the macro to do, is based on todays date derive the weeknumber in the format above and pass this to variable. I will then use this variable to sort and cut and paste the rows under a criteria.

So something like ;

Dim wknum as integer;
Wknum = WEEKNUM(todays date)

I can't see Weeknum function in vba but it must be there somewhere cos I can use the function on the spreadsheet.

Thanks for the prompt reply.

Brychan

 

[enbw]

Sorry,

I while I wrote my reply, the other replies were going on.

I think there is enough for me to be getting on with.

Just for reference the data is stored like this

WOrks Order Number Promised Week
1111 0404
1212 5004

thanks again

 

[TonyJollans]

Chris,

Just how many skins has that beast got?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[enbw]

Ok,

I didn't get very far, could anybody help with assigning the variable to =WEEKNUM(TODAY())&TEXT(TODAY(),"YY")

in vba.

I'm not very good at this...

Thanks

 

[GlennUK]

Hi enbw,

surely you mean this to be a spreadsheet formula, and not vba???????



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[enbw]

Glenn,

I know it is a worksheet function. But I to replicate it in a macro which is going to sort through the data.

Ideally I would like to get the variable to equal the number 4204, in the case of today.

I will then use this number to sort on and filter.

It is for a user. So rather than updating the macro every week it will always pick the current week in the above format.

Thanks.

Brychan

 

[GlennUK]

You might be better off starting a new thread in the VBA forum.

If you are going to use this number to sort and filter on, then you are going to be putting it in cells in the worksheet. Put the formula in the cells instead of calculating in the macro and putting the result in. If you want the macro to put the formula in for you, then to get the syntax right use macto record, and type the formula in a cell, and switch off the macro recorder and see what VBA has been recorded.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

Hi enbw,

It's different in VBA. You wouldn't want it to be too easy now, would you?

[li]VBA does not have the TODAY function; it has DATE instead[/li]
[li]The "Analysis ToolPak - VBA" Add-In does have the WEEKNUM function but the syntax for running it is awkward. A better way is to use the Format "ww" option which combines nicely with the year, giving you ...[/li]

[blue][i]YourVar[/i] = Format(Date, "wwyy")[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.