PDA

View Full Version : Solved: .FormulaR1C1 = "=" & Formula_A


white_flag
01-26-2011, 07:15 AM
hello

I have this code:


Dim a1 As Long, b2, c3, d4
d4 = Application.Evaluate(Replace(Me.Controls("TextBox51").Text, ",",
For i = 1 To 4
If Me.Controls("CheckBox" & i) = True Then
If Me.Controls("TextBox" & i & "a").Text = vbNullString Then Me.Controls("TextBox" & i & "a").Text = 0
If Me.Controls("TextBox" & i & "b").Text = vbNullString Then Me.Controls("TextBox" & i & "b").Text = 0
If Me.Controls("TextBox" & i & "c").Text = vbNullString Then Me.Controls("TextBox" & i & "c").Text = 0
'Formula_A = "(" & Formula_A & "+" & Replace((Application.Evaluate(Replace(Me.Controls("TextBox" & i & "c").Text, ",", ".") & "*(1+" & Replace(Me.Controls("TextBox" & i & "a").Text, ",", ".") & "/100)+(" & Replace(Me.Controls("TextBox" & i & "b").Text, ",", ".") & "/60*" & Replace(Me.Controls("TextBox51").Text, ",", ".") & ")")), ".", ",") & ")"
Me.Controls("CheckBox" & i).Caption = Replace((Application.Evaluate(Replace(Me.Controls("TextBox" & i & "c").Text, ",", ".") & "*(1+" & Replace(Me.Controls("TextBox" & i & "a").Text, ",", ".") & "/100)+(" & Replace(Me.Controls("TextBox" & i & "b").Text, ",", ".") & "/60*" & Replace(Me.Controls("TextBox51").Text, ",", ".") & ")")), ".", ",") & " €/m²"
a1 = Application.Evaluate(Replace(Me.Controls("TextBox" & i & "a").Text, ",", "."))
b2 = Application.Evaluate(Replace(Me.Controls("TextBox" & i & "b").Text, ",", "."))
c3 = Application.Evaluate(Replace(Me.Controls("TextBox" & i & "c").Text, ",", "."))

Formula_A = "H" & i + 13 & "*(1+F" & i + 13 & "/100)+(" & "G" & i + 13 & "/60*C1)"


Set rTopLeft = Sheets(strSheetName).Range("A28")
With rTopLeft.Offset(n, m)
.FormulaR1C1 = "=" & Formula_A



but I receive in line this syntax:

='H14'*(1+'F14'/100)+('G14'/60*$A:$A)

insted of

=H14*(1+F14/100)+(G14/60*C1)

what need to be change?

thx
white_flag
01-26-2011, 07:27 AM
or how can be mixed R1C1 notation with A1 notation
GTO
01-26-2011, 07:31 AM
Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=524078
white_flag
01-26-2011, 07:57 AM
correct :) sorry
white_flag
01-26-2011, 08:24 AM
I simplify:

Formula_A = "(" & Formula_A & "+" & "r" & i + 13 & "c10" & ")"