Loop Through Each Cell & Accept Formula

Question

can anyone help in below VBA code , it takes data from column A & B & adds it, problem is I need to press F2 to refresh formula: example - A = 6(1+2+3) and B = 15(4+5+6), Code result = +=1+2+3+=4+5+6

I have to Manually press F2 & accept formula to calculate result, what I can add to code so it calculates automatically, below is my VBA code? Is there any way I can get formula accepted by cell, my data may have "+" or "=" symbol in between data for example: =+1+2+3=+4+5+6.

Sub datashftfinal1()
    Dim i As Integer
    Dim val1 As String
    Dim val2 As String
    Dim valF As String
    For i = 1 To 10
        If Cells(i + 1, 2).Value >= 0 Then
            val1 = Cells(i + 1, 1).FormulaR1C1
            val2 = Cells(i + 1, 2).FormulaR1C1    
            valF = "+" & val1 & "+" & val2
            Cells(i + 1, 1).FormulaR1C1 = valF
        End If
    Next i
End Sub

Sample Data

Column A1 =1+2+3
Column B1 =4+5+6

i have given formula view of data in above data sample, i am getting result as below after running code:

=1+2+3+=4+5+6

Its not calculating result instead showing formula, i need to press F2 and accept Formula, below is my Code:

enter code here
Sub Data()
Dim i As Integer
Dim val1 As String
Dim val2 As String
Dim valF As String

For i = 1 To 10
If Cells(i + 1, 2).Value >= 0 Then
val1 = Cells(i + 1, 1).FormulaR1C1
val2 = Cells(i + 1, 2).FormulaR1C1
valF = "+" & val1 & "+" & val2
Cells(i + 1, 1).FormulaR1C1 = valF
End If
Next i
End Sub

Do you mean that the formulas that you entered in the excel sheet don't calculate, or that you need to manually restart your code, in order for it to refresh new cells? — rohrl77, Jun 17 '21 at 06:40
Yes, it doesnt calculate, i event tried cell(i+1,1).calculate but it didn't work. it works when i manually go to cell press F2 and enter , it accepts formula than — Sudhanshu Bounthiyal, Jun 17 '21 at 06:44
Shouldn't `valF = "+" & val1 & "+" & val2` be `valF = "=" & val1 & "+" & val2`? (The first plus changed to a equal sign). Can you provide an example of what your formula should look like in the end? — Pᴇʜ, Jun 17 '21 at 07:18
@rohrl77 thanks for promt reply, yes i tried putting "=" but its giving run time error 1004 : Sub Try2() Dim i As Integer Dim val1 As String Dim val2 As String Dim valF As String For i = 1 To 10 If Cells(i + 1, 2).Value >= 0 Then val1 = Cells(i + 1, 1).FormulaR1C1 val2 = Cells(i + 1, 2).FormulaR1C1 valF = "=" & val1 & "+" & val2 Cells(i + 1, 1).FormulaR1C1 = valF End If Next i End Sub — Sudhanshu Bounthiyal, Jun 17 '21 at 08:10
Can you please provide example data and how the formula should look like for that example data? Please always [edit] your original question to add code or important information. Code in comments is not readable. — Pᴇʜ, Jun 17 '21 at 08:34
@Peh sorry i am new here and was not aware that codes in comments are not readable, i have tried to give a sample data and edited my post, appreciate your help. — Sudhanshu Bounthiyal, Jun 17 '21 at 14:12
I understood what you got as result is `=1+2+3+=4+5+6` but that is no valid formula. What formula did you expect to get? Something like this `=1+2+3+4+5+6`? What is your desired result? — Pᴇʜ, Jun 17 '21 at 14:41
@peh Yes my desired result is =1+2+3+4+5+6 however when i add values from both columns , both columns have "=" sign , ie: Column A = "=1+2+3" & Column B = "=4+5+6" , my desired result is to add both column Values & calculate result in column A ie: sum of =1+2+3+4+5+6 = 21 — Sudhanshu Bounthiyal, Jun 17 '21 at 17:50

score 0 · Accepted Answer · answered Jun 17 '21 at 18:21

I would do the following below. The idea is you need to strip off the equal sign of the second formula and concatenate them with a + sign.

Option Explicit

Public Sub Data()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' define your sheet here

    Dim i As Long
    For i = 1 To 10
        If ws.Cells(i + 1, 2).Value >= 0 Then
            Dim Formula1 As String
            Formula1 = ws.Cells(i + 1, 1).FormulaR1C1   ' =1+2+3

            Dim Formula2 As String
            Formula2 = ws.Cells(i + 1, 2).FormulaR1C1   ' =4+5+6

            ' strip off the equal sign of the second formula
            If Left$(Formula2, 1) = "=" Then
                Formula2 = Right$(Formula2, Len(Formula2) - 1)  ' 4+5+6
            End If

            ' put formulas together
            Dim FormulasCombined As String
            FormulasCombined = Formula1 & "+" & Formula2  ' =1+2+3+4+5+6

            ws.Cells(i + 1, 1).FormulaR1C1 = FormulasCombined
        End If
    Next i
End Sub

@Peh Thank you so much , yes your concept is right in second column "=' should be removed , thanks for putting comments in code as it made me understand it. — Sudhanshu Bounthiyal, Jun 18 '21 at 06:45

Loop Through Each Cell & Accept Formula

1 Answers1