Write a formula into a cell depending on another cell value

calendar_today Asked Apr 3, 2013
thumb_up 13 upvotes
history Updated April 16, 2026

Question posted 2013 · +6 upvotes

I was hoping to write a Macro that does a very repetitive task for me but entering VBA is harder than expected. I will learn how to program macros for excel when I have some time because it seem extremely useful, but I can’t spend 5 to 12 hours this week.

Maybe someone here can help!

I have a few excel files that follow this pattern:

Column C - Column D
--------------------
text     | (empty)
number   | (empty)
number   | (empty)
text     | (empty)
number   | (empty)
text     | (empty)
text     | (empty)
number   | (empty)
text     | (empty)
number   | (empty)

Where text and number alternate randomly for a few thousand cells. I need column D to hold, when column C is a number, the difference with previous number, otherwise it must stay blank:

Column C - Column D
--------------------
text     | (empty)
3        | (empty)
14       | (=C3-C2) : 11
text     | (empty)
16       | (=C5-C3) : 2
text     | (empty)
text     | (empty)
21       | (=C8-C5) : 5
22       | (=C9-C8) : 1

So the algorithm is:

var previousNumberCell
var i = 1

for all (selected) cells/rows 
 if (Row(i).column(C) holds number) {
   Row(i).column(D).value = "=C"+i+"-"C"+previousNumberCell
   previousNumberCell = i;
 }
 i++

End

I don’t care if for the first or last cell it doesn’t work.

Thank you so much for the help, or if you can point me to where I can find the answer to this.

EDIT: this is a simplified version of the problem, there are 2 things I don’t know how do well with excel macros: select a cell, and tell if cell is a number… for the record, number cells have been converted from text to number format.

Accepted answer +13 upvotes

Give this a shot:

Sub MyMacro()
Dim rng as Range
Dim cl as Range
Dim lastNum as Range
Set rng = Range(Selection.Address) 'Make sure that your active SELECTION is correct before running the macro'

If Not rng.Columns.Count = 1 Then
    MsgBox "Please select only 1 column of data at a time.",vbCritical
    Exit SUb
Else:
    For each cl in rng
        If IsNumeric(cl.Value) Then
            If lastNum Is Nothing Then
                cl.Offset(0,1).Formula = "=" & cl.Address
            Else:
                cl.Offset(0,1).Formula = "=" & cl.Address & "-" & lastNum.Address

            End If
            set lastNum = cl
        End If
    Next
End If

End Sub

Excel VBA objects referenced (5)

  • Columns.Count — Count function (Microsoft Access SQL)
  • Columns.Count — Count the number of records in a DAO Recordset
  • Range — Refer to Cells by Using a Range Object
  • Range — Delete Duplicate Entries in a Range
  • Selection — Working with the Selection Object

Top excel-vba Q&A (6)

+13 upvotes ranks this answer #39 out of 136 excel-vba solutions on this site .
excel-vba