Getting the week number automatically [Solved/Closed]

SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 19, 2010 at 04:31 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Aug 26, 2010 at 08:26 AM
Hi,..

I need to get the week number automaticlly.. Lets say I enter the a date in Cell A2 then i want Cell B5 to show the respective week number. Lets say for example if today is 18/8/2010 and week should be 33 i recorded a macro and entered it into sheet1 code but nothing happened.. can some one help me correct this code.. thanks.........


Sub week2()
    ActiveCell.FormulaR1C1 = "=WEEKNUM(R[-3]C)"
    Range("B6").Select
End Sub
See more 

8 replies

SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 19, 2010 at 09:56 AM
0
Thank you
any help pls...........
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 20, 2010 at 10:06 AM
0
Thank you
try this

=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 20, 2010 at 02:33 PM
0
Thank you
Thanks this works when i type it in a cell...So would this be my VBA code :??

Sub week2()
    ActiveCell.FormulaR1C1 = "=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)"
    Range("B6").Select
End Sub


Thanks..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 20, 2010 at 04:10 PM
No

In VBA there are two ways you enter formula
.FORMULA
.FORMULAR1C1

The first one is basically as formula. It does not really take into account any change of rows or column

FORMULAR1C1 is used when you have relative positioning
R refers to row, C to column. In such formula A1, D2 etc does not work

in such formula, you have to state row and column
RC = mean same row and column where the formula is being written
R1C: means first row and same column where the formula is being written
R1C1: means first row, first column
R: means same row
C: means same column
R[1]C: means same column but one row down
R[-1]C[3] : would mean: one row up and 3 columns to right (so if this was in B3, this would be refering to E2


Now you dont change. formulaR1c1. as it indicated how you would be defining the formula

.formular1c1="=sUM(r3c5:r3c10)"
Pasted this as code

Sub week2()
    ActiveCell.FormulaR1C1 = "=sUM(r3c5:r3c10)"
    
End Sub


nothing happened
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 21, 2010 at 05:47 AM
That was an example to show you how R1C1 formula is written. If you had used this formula and ran the macro, see what formula gets written in the active cell
SARC 29 Posts Saturday August 7, 2010Registration date August 31, 2010 Last seen - Aug 25, 2010 at 02:06 PM
i got the formula
=SUM($E$3:$J$3) written in the active cell
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 26, 2010 at 08:26 AM
see the point ?
The code said
ActiveCell.FormulaR1C1 = "=SUM(R3C5:R3C10)"


and the formula that showed up is
=SUM($E$3:$J$3)

R3 = $3 . because you are saying that row would be 3. It is absolute row number

C5 = column # 5 which is E

C10 = column # 10 which is J