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

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

Activecell.formular1c1
Windows calendar show week number - Guide
Transfer data from one excel worksheet to another automatically - Guide
Pc restarts automatically - Guide
Download automatically while roaming - Guide
How to open a web page automatically in full screen mode in html - Guide

3 responses

Answer 1 / 3

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...........

Answer 2 / 3

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 20, 2010 at 10:06 AM

try this

=TRUNC(TRUNC((A1-DATE(YEAR(A1),1,0))+6)/7)

Answer 3 / 3

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..

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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)"

SARC
Aug 20, 2010 at 11:32 PM

Pasted this as code

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

nothing happened

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

i got the formula
=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

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

Similar discussions

help me to add particular cells

My computer turns off after 30 sec to 1 min

Similar discussions

Subscribe To Our Newsletter!