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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 26, 2010 at 08:26 AM
rizvisa1 Posts 4478 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.........
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
Related:
- Activecell.formular1c1
- Windows calendar show week number - Guide
- Download automatically while roaming - Guide
- Why does facebook refresh itself automatically - Guide
- How to stop idm from automatically downloading - Guide
- How to automatically save photos from messenger to gallery - Guide
3 responses
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010
Aug 19, 2010 at 09:56 AM
Aug 19, 2010 at 09:56 AM
any help pls...........
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 20, 2010 at 10:06 AM
Aug 20, 2010 at 10:06 AM
try this
=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)
=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)
SARC
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010
Aug 20, 2010 at 02:33 PM
Aug 20, 2010 at 02:33 PM
Thanks this works when i type it in a cell...So would this be my VBA code :??
Thanks..
Sub week2()
ActiveCell.FormulaR1C1 = "=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)"
Range("B6").Select
End Sub
Thanks..
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 20, 2010 at 04:10 PM
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)"
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)"
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 21, 2010 at 05:47 AM
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
Posts
29
Registration date
Saturday August 7, 2010
Status
Member
Last seen
August 31, 2010
Aug 25, 2010 at 02:06 PM
Aug 25, 2010 at 02:06 PM
i got the formula
=SUM($E$3:$J$3) written in the active cell
=SUM($E$3:$J$3) written in the active cell
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 26, 2010 at 08:26 AM
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
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
