Related:

- Activecell.formular1c1 examples
- Formular1c1 sum - Best answers
- Formular1c1 example - Best answers
- Transfer data from one excel worksheet to another automatically - How-To - Excel
- Pc restarts automatically - How-To - Windows
- Log me into facebook automatically - How-To - Facebook
- My pc restarts automatically again again ✓ - Forum - Hardware
- How to stop idm from automatically downloading - How-To - Download manager

## 3 replies

SARC

- Posts
- 29
- Registration date
- Saturday August 7, 2010
- Status
- Member
- Last seen
- August 31, 2010

any help pls...........

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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

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
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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