Insert Formula Into Cell Based on Value of Another Cell?

AlexR688

New Member
Joined
Aug 26, 2013
Messages
10
Hi everyone,


I'm quite new to VBA on Excel and haven't quite been able to figure out how to get my macro working yet.


I have data that looks like this:


DateAmount 1TypeAmount 2Amount 3Macro Output
01/01/2013-100A-80-20
01/02/2013-150B
-30
03/05/2014-200



<tbody>
</tbody>


What I would like the macro to do is to look in the Type column (i.e. column C) and to go through each cell in the column looking for whether the cell contains the string "A", "B" or is empty. However, this process should only continue until the cell to its left (in column B) is empty.


Depending on which string the cell in column C contains (or if it is empty), I want to then have a formula entered into the Macro Output column (i.e. column F) on the same row. This formula is as follows:


If cell contains "A" then Macro Output cell = (Amount 3/Amount 2)
If cell contains "B" then Macro Output cell = (Amount 3/Amount 1)
If cell is empty then Macro Output cell = (Amount 3/Amount 1).
If cell is not empty and does not contain "A" or "B" then Macro Output cell = "UNDEFINED"

What I have written at the moment is this:


Code:
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Option Explicit[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Sub MyMacro()[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Dim cell As String[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]Workbooks(“MyWorkbook).Sheets("Sheet1").Cells(2, 2).Select[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Do Until ActiveCell.Value = Empty[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    ActiveCell.Offset(0, 1).Select[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]    Select Case ActiveCell[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]            Case cell = “A”[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]                ActiveCell.Offset(0, 3).FormulaR1C1 = "=RC[-1]/RC[-2]”[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]            Case cell = "Non IRE"[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]                ActiveCell.Offset(0, 3).FormulaR1C1 = "=RC[-1]/RC[-4]”[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]            Case cell = Empty[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]                ActiveCell.Offset(0, 3).FormulaR1C1 = “=RC[-1]/RC[-4]” [/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]            Case Else[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]                ActiveCell.Offset(0, 3) = "UNDEFINED"[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]    End Select[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]    ActiveCell.Offset(1, -4).Select[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]    Loop[/FONT][/COLOR]

[COLOR=#232323][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#232323][FONT=Verdana]
[/FONT][/COLOR]


At the moment this only gives me a #DIV/0! error in the second cell of column F (Macro Output column), but it is the closest I have come so far. (My previous attempts just gave blank cells in that column).

Any help on this would be greatly appreciated.

Thanks in advance,

Alex
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
AlexR688,

Instead of a macro, how about one formula?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Macro Output</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/01/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-100</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0.25</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/02/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-150</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0.2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">03/05/2014</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-200</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">UNDEFINED</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C2="A",E2/D2,IF(<font color="Purple">C2="B",E2/B2,IF(<font color="Teal">C2="",E2/B2,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C2="A",E2/D2,IF(<font color="Green">C2="B",E2/B2,IF(<font color="Purple">C2="",E2/B2,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C3="A",E3/D3,IF(<font color="Purple">C3="B",E3/B3,IF(<font color="Teal">C3="",E3/B3,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C3="A",E3/D3,IF(<font color="Green">C3="B",E3/B3,IF(<font color="Purple">C3="",E3/B3,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C4="A",E4/D4,IF(<font color="Purple">C4="B",E4/B4,IF(<font color="Teal">C4="",E4/B4,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C4="A",E4/D4,IF(<font color="Green">C4="B",E4/B4,IF(<font color="Purple">C4="",E4/B4,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C5="A",E5/D5,IF(<font color="Purple">C5="B",E5/B5,IF(<font color="Teal">C5="",E5/B5,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C5="A",E5/D5,IF(<font color="Green">C5="B",E5/B5,IF(<font color="Purple">C5="",E5/B5,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C6="A",E6/D6,IF(<font color="Purple">C6="B",E6/B6,IF(<font color="Teal">C6="",E6/B6,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C6="A",E6/D6,IF(<font color="Green">C6="B",E6/B6,IF(<font color="Purple">C6="",E6/B6,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

The formula in cell F2 copied down:
=IF(ISERROR(IF(C2="A",E2/D2,IF(C2="B",E2/B2,IF(C2="",E2/B2,"UNDEFINED")))),"",IF(C2="A",E2/D2,IF(C2="B",E2/B2,IF(C2="",E2/B2,"UNDEFINED"))))
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
I hate to admit it (because I'm better at vba than at formulas...) but if you can get away with a formula solution that's most often the way to go.

That being said, if you're intent on a vba solution then perhaps this can help.
Code:
Sub MacroOutputDemo()
Dim LstRw As Long, Rng As Range
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
For Each Rng In Range("C2:C" & LstRw)
  Select Case Rng.Value
    Case "A"
      Rng(, 4).Formula = "=" & Rng(, 3).Address(0, 0) & "/" & Rng(, 2).Address(0, 0)
    Case "B"
      Rng(, 4).Formula = "=" & Rng(, 3).Address(0, 0) & "/" & Rng.Offset(, -1).Address(0, 0)
    Case ""
      Rng(, 4).Formula = "=" & Rng(, 3).Address(0, 0) & "/" & Rng.Offset(, -1).Address(0, 0)
    End Select
Next Rng
End Sub

Hope - one or the other - helps.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
AlexR688,

Sample raw data:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Macro Output</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/01/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-100</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/02/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-150</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">03/05/2014</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-200</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">J</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Type</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Amount 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">Macro Output</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/01/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-100</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0.25</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">01/02/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-150</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0.2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">03/05/2014</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">-200</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;">UNDEFINED</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #232323;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">J</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">UNDEFINED</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C2="A",E2/D2,IF(<font color="Purple">C2="B",E2/B2,IF(<font color="Teal">C2="",E2/B2,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C2="A",E2/D2,IF(<font color="Green">C2="B",E2/B2,IF(<font color="Purple">C2="",E2/B2,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C3="A",E3/D3,IF(<font color="Purple">C3="B",E3/B3,IF(<font color="Teal">C3="",E3/B3,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C3="A",E3/D3,IF(<font color="Green">C3="B",E3/B3,IF(<font color="Purple">C3="",E3/B3,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C4="A",E4/D4,IF(<font color="Purple">C4="B",E4/B4,IF(<font color="Teal">C4="",E4/B4,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C4="A",E4/D4,IF(<font color="Green">C4="B",E4/B4,IF(<font color="Purple">C4="",E4/B4,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C5="A",E5/D5,IF(<font color="Purple">C5="B",E5/B5,IF(<font color="Teal">C5="",E5/B5,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C5="A",E5/D5,IF(<font color="Green">C5="B",E5/B5,IF(<font color="Purple">C5="",E5/B5,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C6="A",E6/D6,IF(<font color="Purple">C6="B",E6/B6,IF(<font color="Teal">C6="",E6/B6,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C6="A",E6/D6,IF(<font color="Green">C6="B",E6/B6,IF(<font color="Purple">C6="",E6/B6,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">IF(<font color="Green">C7="A",E7/D7,IF(<font color="Purple">C7="B",E7/B7,IF(<font color="Teal">C7="",E7/B7,"UNDEFINED"</font>)</font>)</font>)</font>),"",IF(<font color="Red">C7="A",E7/D7,IF(<font color="Green">C7="B",E7/B7,IF(<font color="Purple">C7="",E7/B7,"UNDEFINED"</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub InsertFormula()
' hiker95, 08/26/2013
' http://www.mrexcel.com/forum/excel-questions/722698-insert-formula-into-cell-based-value-another-cell.html
With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
  .Formula = "=IF(ISERROR(IF(C2=""A"",E2/D2,IF(C2=""B"",E2/B2,IF(C2="""",E2/B2,""UNDEFINED"")))),"""",IF(C2=""A"",E2/D2,IF(C2=""B"",E2/B2,IF(C2="""",E2/B2,""UNDEFINED""))))"
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertFormula macro.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

HalfAce,

Nicely done - one for the archives. Thanks.
 

AlexR688

New Member
Joined
Aug 26, 2013
Messages
10
HalfAce and hiker95,

Thanks very much for your solutions, both worked a treat. Always good to know more than one way to approach a problem.

Will now go and research ISERROR and .Address!
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
AlexR688,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.
 

Forum statistics

Threads
1,136,845
Messages
5,678,091
Members
419,741
Latest member
BKN336

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top