CODEWITHSUNDEEP
excelvbaexcel-formula
leonardik
leonardik

Reputation: 131

Visual Basic - for..next and formulas

I need to make Excel VBA script which inserts formula depending on cells values. My current version returns only FALSE value

For i = 1 To 10 Step 1
If Cells(i, 2).Value > Cells(i, 1).Value Then Cells(i, 3).Value = Cells(i, 3).Formula = "=cells(i,1).value+cells(i,2).value"
If Cells(i, 2).Value < Cells(i, 1).Value Then Cells(i, 3).Value = Cells(i, 3).Formula = "=cells(i,2).value-cells(i,1).value"
Next i

Upvotes: 1

Views: 87

Answers (2)

Storax
Storax

Reputation: 12167

I geuss you would like to do something like that

For i = 1 To 10 Step 1
    If Cells(i, 2).Value > Cells(i, 1).Value Then Cells(i, 3).FormulaR1C1 = "=RC[-2]+RC[-1]"
    If Cells(i, 2).Value < Cells(i, 1).Value Then Cells(i, 3).FormulaR1C1 = "=RC[-2]-RC[-1]"
Next i

Upvotes: 3

Christov
Christov

Reputation: 178

First, it returns FALSE because you compare the formula of the cell with your string. Those are not equal, hence it returns FALSE. This would be solved by leaving out the =Cells(i,3).Formula part.

Second, it is important to be aware that certain VBA functionality cannot be used in your worksheet and vice versa. When you type '=Cells' in a cell you will note there is no Worksheet function called Cells. That's why the following will return '#Field!'

 For i = 1 To 10 Step 1
    If Cells(i, 2).Value > Cells(i, 1).Value Then 
         Cells(i, 3).Formula = "=cells(i,1).value+cells(i,2).value"
    End if
    If Cells(i, 2).Value < Cells(i, 1).Value Then 
         Cells(i, 3).Formula = "=cells(i,2).value-cells(i,1).value"
    End if 
Next i

The easiest thing to do is performing the calculation within VBA: 

    For i = 1 To 10 Step 1
       If Cells(i, 2).Value > Cells(i, 1).Value Then 
             Cells(i, 3).Value = Cells(i, 1).Value + Cells(i, 2).Value
       End if

       If Cells(i, 2).Value < Cells(i, 1).Value Then 
             Cells(i, 3).Formula = Cells(i, 2).Value - Cells(i, 1).Value
       End if 

    Next i

Some recommendations:

  1. Instead of addressing a cell value multiple times, assign the value to a variable.
  2. Try avoiding writing formulas to worksheets. This will slow down your code big time. If you must, then consider storing all the formulas in an array first and only write them to the worksheet when the entire array has been filled.
  3. In this case you can avoid using VBA altogether.
  4. Consider checking if the value of the cell is numeric before performing calculations.
  5. If you insist on using a worksheet function, you could use Storax's suggestion.

Upvotes: 1

Related Questions