Write a formula into a cell depending on another cell value

excel, excel-2007, vba

Solution

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

Problem

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.

Original source