VBA formula question

[Roger on Excel]

I use the following type of code to place formulas in cells :

For Each cell In Range("A1:A2")

cell.Offset(, 1).FormulaR1C1 = "=RC[10]&RC[8]&RC[15]"
'Equiv
cell.Offset(, -1).FormulaR1C1 =
"=IF(ISERROR(RC[5]/r4c13),""Data?"",RC[3]/r4c13)"
Next

This works fine, however my question is whether one can perform the
calculations in the code and just place the result into the cells?

I guess this may require learning a whole new syntax for coding the
equations, but I was wondering if someone could give me some pointers?

Many Thanks,

Roger

 

[Mike Fogleman]

You might try the VBA function Evaluate. Evaluate will calculate string
expressions which are what cell formulas are, however you will need to
change to "A1" notation in your expression.
Look it up in VBA help.

Mike F

 

[J_Knowles]

Sub test1()
' replacing formula with value
For Each cell In Range("c1:c2")
cell.Offset(, 1).FormulaR1C1 = "=RC[10]&RC[8]&RC[15]"
cell.Offset(, 1).Formula = cell.Offset(, 1).Value
'Equiv
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[5]/r4c13), _
""Data?"",RC[3]/r4c13)"
cell.Offset(, -1).Formula = cell.Offset(, -1).Value
Next
End Sub

HTH,

 

[J_Knowles]

Sub test1()
' replacing formula with its results
For Each cell In Range("c3:c4")
cell.Offset(, 1).FormulaR1C1 = "=RC[10]&RC[8]&RC[15]"
cell.Offset(, 1).Formula = cell.Offset(, 1).Value
'Equiv
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[5]/r4c13), _
""Data?"",RC[3]/r4c13)"
cell.Offset(, -1).Formula = cell.Offset(, -1).Value
Next
End Sub

HTH,

 

[Rick Rothstein]

Maybe something like this..

For Each Cell In Range("A1:A2").Offset(,1)
Cell.Value = Cell.Offset(, 10) & Cell.Offset(, 8) & Cell.Offset(, 15)
Next

 

[Matthew Herbert]

Roger,

There is no need to learn "a whole new syntax" for coding functions. In
general, VBA handles procedures and functions; procedures typically execute a
set of instructions and functions return results for a set of instructions.

You are looking for a function. So, the example below is a very basic,
non-error handling illustration of a function that can be used in the
worksheet. If you place the code below into a standard module (i.e. do a
Insert|Module with VBE to get a standard module), then you can insert the
function into the spreadsheet via the Inert Function dialog (either do
Insert|Function (XL2003) or Formulas|Insert Function (XL2007) to access the
dialog box). (You can simply write "=" followed by the function name, just
like you might do =SUM(... rather than using the function dialog box). If
you open the Insert Function dialog, you'll notice that the category section
has a "User Defined" category. You should see "MyFunction" located under the
category. (The function will appear in the "User Defined" section after the
code exists in a standard module).

Function MyFunction(Rng1 As Range, Rng2 As Range) As Double
MyFunction = Application.Sum(Rng1, Rng2)
End Function

The function is named "MyFunction". The function also has two parameters -
Rng1 and Rng2. These two parameters are Range Object, so you can simply
reference other cells as the arguments. The function returns a Double as a
result. The function works by adding Rng1 to Rng2.

So, go to a blank worksheet and insert the following:
A1: 5
A2: 3
A3: =MyFunction(A1,A2)

The result in A3 will be 8.

I hope this is clear, and I hope this helps.

Best,

Matthew Herbert

 

[Sam Wilson]

Hi,

It's easy enough to do:

change:

cell.Offset(, 1).FormulaR1C1 = "=RC[10]&RC[8]&RC[15]"

to:

cell.Offset(, 1).value = cell.offset(10,0) & cell.offset(8,0) &
cell.offset(15,0)

Sam

 

[JLatham]

Yes, you can, but sometimes it takes a lot of code to emulate a worksheet
function. For example, in the case you gave, you'd have to test for 0 in M13
(R4C13) and make the decision in your code whether to put up "Data?" or the
result.

One trick you can use would be to achieve the result would be to add a line
of code right after the one where you've used your .FormulaR1C1 = ...
statement
cell.offset(,-1).Formula = cell.offset(,-1).Value
would take the result of the formula you just put into a cell and replace
the formula with that result/value. Of course the tradeoff here is that it
takes more time to process than just building the formula, or possibly more
time than having the calculation done in code.

 

[Paul C]

It is actually usually pretty easy.

You can use regular references: Range("A1")= Range ("C10")

Will put the value of cell C10 into cell A1

Relative references are a different. In VB the format Cells(R,C) can be used

Range ("A1") = Cells(Range("A1").Row,2)

Will put the value of B2 into cell A1.

This format can also be looped

For A=1 to 10
Cells(A,1)=Cells(A,2)
Next A

Would put the value of B1-B10 into cells A1-A10.

You can also use the & operator, you will need to make sure all values are
strings using the Cstr("") function like this.

For A=1 to 2
Cells(R,2)=Cstr(Cells(R,12))&Cstr(Cells(R,10))&Cstr(Cells(R,17))
Next A

Your second formula is a error in that you are putting a formula into a
offset ,-1 from column 1, but the logic would be something like this (changed
to column A)


For A=1 to 2
If Cells(4,13)=0 then
Cells(A,1)="Data?"
Else
Cells(A,1)=Cells(A,4)/Cells(4,13)
End If
Next A