Getting the week number automatically [Solved/Closed]
SARC29
Posts Saturday August 7, 2010Registration date
August 31, 2010
Last seen

Aug 19, 2010 at 04:31 AM  Latest reply: rizvisa14481
Posts Thursday January 28, 2010Registration dateContributorStatus
January 6, 2016
Last seen
 Aug 26, 2010 at 08:26 AM
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
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
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