Getting the week number automatically

Solved/Closed
SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010 - Aug 19, 2010 at 04:31 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - 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

3 replies

SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 19, 2010 at 09:56 AM
any help pls...........
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 20, 2010 at 10:06 AM
try this

=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)
0
SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 20, 2010 at 02:33 PM
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..
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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)"
0
Pasted this as code

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


nothing happened
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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
0
SARC Posts 29 Registration date Saturday August 7, 2010 Status Member Last seen August 31, 2010
Aug 25, 2010 at 02:06 PM
i got the formula
=SUM($E$3:$J$3) written in the active cell
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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
0