macro for Percent to Dollars and/or Dollars to Percent

[jacob farino]

Hi,

I have three cells. The first is loan amount, one is % and one is Dollars.
I'd like to be able to enter the second (i.e. "3%") and have it multiply the
first to display dollars in the dollars cell:
loan amount $10,000 x 3% = $___

OR

I want to be able to enter dollars and have it divide into a percent:
loan amount $10,000 (/) $300 = 3%.

Let's label the cells A1, B1, C1.

So A1 is loan amount, B1 is %, and C1 is $$$

Obviously, without a macro, it will erase a formula if I enter it into any
of these cells. Below is a sample macro I got from a person for another
function, but I can't translate it to what I need.

HELP!!!
}jacob{


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range
Dim CommissionPercent As Range
Dim YSPDollar As Range
Dim YSPpercent As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [H7:H65000]
Set CommissionDollar = [I7:I65000]
Set CommissionPercent = [J7:J65000]
Set YSPDollar = [K7:K65000]
Set YSPpercent = [L7:L65000]

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler
If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPDollar) Is Nothing Then
YSPpercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPpercent) Is Nothing Then
YSPDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) /
Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex,
1)
End If

If Target.Value = 0 Then
YSPpercent(RowIndex, 1).ClearContents
YSPDollar(RowIndex, 1).ClearContents
ElseIf YSPDollar(RowIndex, 1) <> 0 Then
YSPpercent(RowIndex, 1) = YSPDollar(RowIndex, 1) / Target
ElseIf YSPpercent(RowIndex, 1) <> 0 Then
YSPDollar(RowIndex, 1) = Target * YSPpercent(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
'Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
'MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

MsgBox ("Data Entry Error")

Application.EnableEvents = True

End Sub

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim vResult As Variant
With Target
If .Column = 2 Or .Column = 3 Then
Application.EnableEvents = False
If .Column = 2 Then
If IsEmpty(.Value) Then
vResult = ""
ElseIf IsNumeric(.Value) Then
vResult = .Offset(0, -1).Value * .Value
Else
vResult = ""
End If
.Offset(0, 1).Value = vResult
Else
If IsEmpty(.Value) Then
vResult = ""
ElseIf IsNumeric(.Value) Then
If .Value <> 0 Then
vResult = .Offset(0, -2).Value / .Value
Else
vResult = CVErr(xlErrDiv0)
End If
Else
vResult = ""
End If
.Offset(0, -1).Value = vResult
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Jacob]

Thanks McGimpsey.
Question: Because I am a novice to VBA, how do I set the macro to only work
amongst those target 3 cells, not the entire column ? Meaning, I have tons
of other cells filled with other data, but I only want this macro to affect
the three selected cells.

Jacob

(Would you prefer me to send you the document, if I'm being unclear?)

 

[JE McGimpsey]

One way:

Replace

If .Column = 2 Or .Column = 3 Then

with

If Not Intersect(.Cells, Range("B1:C1")) Is Nothing Then