Insert Formula Into Cell Based on Value of Another Cell?

A

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:


[TABLE="width: 499"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Amount 1[/TD]
[TD]Type[/TD]
[TD]Amount 2[/TD]
[TD]Amount 3[/TD]
[TD]Macro Output[/TD]
[/TR]
[TR]
[TD]01/01/2013[/TD]
[TD]-100[/TD]
[TD]A[/TD]
[TD]-80[/TD]
[TD]-20[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]01/02/2013[/TD]
[TD]-150[/TD]
[TD]B[/TD]
[TD]
[/TD]
[TD]-30[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]03/05/2014[/TD]
[TD]-200[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
AlexR688,

Instead of a macro, how about one formula?


Excel 2007
ABCDEF
1DateAmount 1TypeAmount 2Amount 3Macro Output
201/01/2013-100A-80-200.25
301/02/2013-150B-300.2
403/05/2014-2000
5CUNDEFINED
6 
7
Sheet1
Cell Formulas
RangeFormula
F2=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"))))
F3=IF(ISERROR(IF(C3="A",E3/D3,IF(C3="B",E3/B3,IF(C3="",E3/B3,"UNDEFINED")))),"",IF(C3="A",E3/D3,IF(C3="B",E3/B3,IF(C3="",E3/B3,"UNDEFINED"))))
F4=IF(ISERROR(IF(C4="A",E4/D4,IF(C4="B",E4/B4,IF(C4="",E4/B4,"UNDEFINED")))),"",IF(C4="A",E4/D4,IF(C4="B",E4/B4,IF(C4="",E4/B4,"UNDEFINED"))))
F5=IF(ISERROR(IF(C5="A",E5/D5,IF(C5="B",E5/B5,IF(C5="",E5/B5,"UNDEFINED")))),"",IF(C5="A",E5/D5,IF(C5="B",E5/B5,IF(C5="",E5/B5,"UNDEFINED"))))
F6=IF(ISERROR(IF(C6="A",E6/D6,IF(C6="B",E6/B6,IF(C6="",E6/B6,"UNDEFINED")))),"",IF(C6="A",E6/D6,IF(C6="B",E6/B6,IF(C6="",E6/B6,"UNDEFINED"))))


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"))))
 
Upvote 0
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.
 
Upvote 0
AlexR688,

Sample raw data:


Excel 2007
ABCDEF
1DateAmount 1TypeAmount 2Amount 3Macro Output
201/01/2013-100A-80-20
301/02/2013-150B-30
403/05/2014-200
5C
6
7J
8
Sheet1


After the macro:


Excel 2007
ABCDEF
1DateAmount 1TypeAmount 2Amount 3Macro Output
201/01/2013-100A-80-200.25
301/02/2013-150B-300.2
403/05/2014-2000
5CUNDEFINED
6 
7JUNDEFINED
8
Sheet1
Cell Formulas
RangeFormula
F2=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"))))
F3=IF(ISERROR(IF(C3="A",E3/D3,IF(C3="B",E3/B3,IF(C3="",E3/B3,"UNDEFINED")))),"",IF(C3="A",E3/D3,IF(C3="B",E3/B3,IF(C3="",E3/B3,"UNDEFINED"))))
F4=IF(ISERROR(IF(C4="A",E4/D4,IF(C4="B",E4/B4,IF(C4="",E4/B4,"UNDEFINED")))),"",IF(C4="A",E4/D4,IF(C4="B",E4/B4,IF(C4="",E4/B4,"UNDEFINED"))))
F5=IF(ISERROR(IF(C5="A",E5/D5,IF(C5="B",E5/B5,IF(C5="",E5/B5,"UNDEFINED")))),"",IF(C5="A",E5/D5,IF(C5="B",E5/B5,IF(C5="",E5/B5,"UNDEFINED"))))
F6=IF(ISERROR(IF(C6="A",E6/D6,IF(C6="B",E6/B6,IF(C6="",E6/B6,"UNDEFINED")))),"",IF(C6="A",E6/D6,IF(C6="B",E6/B6,IF(C6="",E6/B6,"UNDEFINED"))))
F7=IF(ISERROR(IF(C7="A",E7/D7,IF(C7="B",E7/B7,IF(C7="",E7/B7,"UNDEFINED")))),"",IF(C7="A",E7/D7,IF(C7="B",E7/B7,IF(C7="",E7/B7,"UNDEFINED"))))


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.
 
Upvote 0
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!
 
Upvote 0
AlexR688,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
438
Mplz
M
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
202
igold
igold
T
  • Question Question
Match specific rows with a column
Replies
1
Views
173
Peter_SSs
Peter_SSs
A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
464
jolivanes
jolivanes
H
  • Solved
Range.Formula does not lead to cell "selected" or "active"
Replies
5
Views
260
heathball
H

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top