vba help - pass variable in R1CI formula not working

[Mallesh23]

Hi Team,

Need help in passing variable in below Recorded Code.

Recorded code :=> ActiveCell.FormulaR1C1 = "=SUM(R[-38]C+R[-36]C+R[-34]C)"


My Variable Range as follows. I have found Range via find method. need to pass these variable in above recorded formula.
rng_TelReimbursement
rng_DvrSalary
Rng_Fuel


Attempted Code

With Sheet1
        With .Range(.Cells(50, 4), .Cells(50, lc + 1))
                    .FormulaR1C1 = "=SUM(R[" & rng_TelReimbursement.row & " ]C+R[" & rng_DvrSalary.row & "]C+R[" & Rng_Fuel.row & "]C)"
            End With
End With

Thanks
mg

 

[Joe4]

Note that in your formula it is -38, -36, and -34.
You have left the negatives out of your formula. .Row will only return positive values.
And note in R1C1 format, it is all relative to the cell you are putting the formula in. So using ".Row" may not be correct, without adding some offset to that.

If you have issues figuring it out, please let us know that all those ".Row" references return, and explain to us how the exact logic of the formula, especially as it relates to the row the formula is in.

 

[RoryA]

I suspect what you want is to remove the square brackets altogether:

.FormulaR1C1 = "=SUM(R" & rng_TelReimbursement.row & "C,R" & rng_DvrSalary.row & "C,R" & Rng_Fuel.row & "C)"

Note that there is no point in using both SUM and + for that.

 

[Fluff]

How about

   With Sheet1
      With .Range(.Cells(50, 4), .Cells(50, lc + 1))
         .FormulaR1C1 = "=SUM(R" & rng_TelReimbursement.Row & " C,R" & rng_DvrSalary.Row & "C,R" & Rng_Fuel.Row & "C)"
      End With
   End With

 

[Mallesh23]

Hi Joe,Rory and Fluff.

Thanks for your help. tried your code its working.
Rory's answer select specific cells,
Fluffs answer also gives correct it select entire column.

I will use Rorys code, But thanks for sharing multiple Option.


Below is my Code gives correct output.
But in Future if expendiature Items increase I have to Change my code.

Can we Make Dynamic here using loops......Answer I want in sum formula

Like----->'=SUM(E6,E8,E10)

Sub Sum_Help()


    Dim rng_DriverSalary As Range
    Dim rng_Fuel As Range
    Dim rng_Telephone As Range
    Dim rng_Reimbursement_Payble As Range
       
    Set rng_Telephone = Sheet1.Range("D:D").Find("Telephone Reimbursement", LookIn:=xlValues, lookat:=xlPart)
    Set rng_DriverSalary = Sheet1.Range("D:D").Find("Driver's Salary", LookIn:=xlValues, lookat:=xlPart)
    Set rng_Fuel = Sheet1.Range("D:D").Find("Fuel & Maintenance", LookIn:=xlValues, lookat:=xlPart)
    Set rng_Reimbursement_Payble = Sheet1.Range("D:D").Find("Reimbursement Payble", LookIn:=xlValues, lookat:=xlPart)
    
    
    
    With Sheet1
        With .Range(.Cells(rng_Reimbursement_Payble.Row, 5), .Cells(rng_Reimbursement_Payble.Row, 6))
                    .FormulaR1C1 = "=SUM(R" & rng_Telephone.Row & "C,R" & rng_DriverSalary.Row & "C,R" & rng_Fuel.Row & "C)"
            End With
    End With

End Sub


Below is a Table, K column is the criteria. how to create below formula using loops.
Actual my Criteria is in closed workbook.

.FormulaR1C1 = "=SUM(R" & rng_Telephone.Row & "C,R" & rng_DriverSalary.Row & "C,R" & rng_Fuel.Row & "C)"

Book3
	A	B	C	D	E	F	G	H	I	J	K
1
2				Cost Head	1000	2000	3000				Expendiature List
3				Basic	22022	32717	35091				Telephone Reimbursement
4				DA	38886	20227	31564				Fuel & Maintenance
5				HRA	23044	21483	36847				Driver's Salary
6				Telephone Reimbursement	100	200	300
7				Bonus	24203	32033	20467
8				Fuel & Maintenance	300	500	800
9				Car Allowance	26432	20531	29890
10				Driver's Salary	400	300	1200
11
12
13									=SUM(E6,E8,E10)
14
15
16				Reimbursement Payble	800	1000	2300
17
Sheet1

Cell Formulas
Range	Formula
E16:F16	E16	=SUM(E$6,E$10,E$8)
G16	G16	=SUM(G6,G8,G10)

Thanks
mg

 

[Fluff]

As this is now a totally different question, you will need to start a new thread. Thanks

 

[Mallesh23]

Hi Fluf and Rory.

yes will post new thread for new request.

And Thanks for your help in passing variable in formula's.?


Thanks
mg

 

[Fluff]

Thank you.