# Add new column based on precentage of another column

I would like to automate adding a column that gets 35% of another column.

I am trying to do this in vb code where i can create a macro instead of making a formula because I do not want the columns to depend on each other in case i delete column c later.

Given:
A               B                C
124         Pulley
135         Tank            37.50
256         Pump           11.13
654         Tube

Output: Takes column C and gets 35% of the number in column D
A                B                C                D
124         Pulley                                  0
135         Tank            37.50         13.13
256         Pump           11.13         3.90
354         Tube                                    0

###### Who is Participating?

Commented:
Replace this:

v(i, iDestinationColumn) = v(i, iSecondColumn) * iFirstColumn

with this:

v(i, iDestinationColumn) = v(i, iSecondColumn) * iFirstColumn / 100

:)
0

Commented:
You could still use a formula and turn off automatic calculation (Formulas -> Calculation options)
0

Microsoft MVP ExcelCommented:
Hello,

How will a macro know which number to use in the calculation if you delete column C? If you don't want a formula in the cell but write the value in the cell instead, copy the formula, then use Paste Special - Values to paste just the values into the cell.

No macro required to do that.

cheers, teylyn
0

Microsoft MVP ExcelCommented:
@ConUladh, turning off automatic calculation will mean that nothing in the spreadsheet will be calculated, not just the formula in D. Probably not the desired effect. If you want to calculate anything else, you'll need to turn calculation back on and then D will calculate again, anyway.
0

Author Commented:
I am trying to get something close to this solution

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26832673.html

This way I can prompt it and it'll be less time since they get vendor spreadsheets in everyday and they have to prepare them for an import.
0

Author Commented:
I basically want it to prompt me for following when complete
what column to use?
what is the precentage you want to use?

From there it will do the work.

0

Commented:
Well, obviously you can add this piece near the beginning:

Dim dblPercentage As Double

dblPercentage = Val(InputBox("What Percentage?", "Pick", 0))
If dblPercentage = 0 Then
Exit Sub
End If

--
Can you figure out the part where you use the dblPercentage variable to add to the proper cell?
0

Author Commented:
I pasted below what I was able to come up with. The only thing i don't know is what to put for this part where i put "this should be" because the final value would not be text.

If v(i, iSecondColumn) = "" Then
v(i, iDestinationColumn) = "This should be 0"
Else
v(i, iDestinationColumn) = "This should be (column * .35)"
End If

``````Sub x()

Dim v, i As Long

Dim iFirstColumn As Integer
Dim iSecondColumn As Integer
Dim iDestinationColumn As Integer

iDestinationColumn = 5

iFirstColumn = Val(InputBox("what percentage to use (ex. 35)?", "Pick", 35))
If iFirstColumn = 0 Then
Exit Sub
End If

iSecondColumn = Val(InputBox("What Column is the price in?", "Pick", 4))
If iSecondColumn = 0 Then
Exit Sub
End If

iDestinationColumn = Val(InputBox("Select Destination column to pick?", "Pick", 4))
If iDestinationColumn = 0 Then
Exit Sub
End If

With Range("A1").CurrentRegion
v = .Resize(, .Columns.Count + 1).Value
.ClearContents
End With

For i = LBound(v, 1) + 1 To UBound(v, 1)
If v(i, iSecondColumn) = "" Then
v(i, iDestinationColumn) = "This should be 0"
Else
v(i, iDestinationColumn) = "This should be (column * .35)"
End If
Next i

Range("A1").Resize(UBound(v, 1), UBound(v, 2)) = v

End Sub
``````
0

Author Commented:
I was able to get it to multiply by using this, but i need 35 to be .35 when multiplying

If v(i, iSecondColumn) = "" Then
v(i, iDestinationColumn) = 0
Else
v(i, iDestinationColumn) = v(i, iSecondColumn) * iFirstColumn
End If
0

Author Commented:
That did the job
0

Author Commented:
``````Sub Merge()
'
' Merge Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Dim v, i As Long

Dim iFirstColumn As Integer
Dim iSecondColumn As Integer
Dim iDestinationColumn As Integer

iDestinationColumn = 5

iFirstColumn = Val(InputBox("what percentage to use (ex. 35)?", "Pick", 35))
If iFirstColumn = 0 Then
Exit Sub
End If

iSecondColumn = Val(InputBox("What Column is the price in?", "Pick", 3))
If iSecondColumn = 0 Then
Exit Sub
End If

iDestinationColumn = Val(InputBox("Select Destination column to pick?", "Pick", 5))
If iDestinationColumn = 0 Then
Exit Sub
End If

With Range("A1").CurrentRegion
v = .Resize(, .Columns.Count + 1).Value
.ClearContents
End With

For i = LBound(v, 1) + 1 To UBound(v, 1)
If v(i, iSecondColumn) = "" Then
v(i, iDestinationColumn) = 0
Else
v(i, iDestinationColumn) = v(i, iSecondColumn) * iFirstColumn / 100
End If
Next i

Range("A1").Resize(UBound(v, 1), UBound(v, 2)) = v

End Sub
``````
0

Commented:
Thanks for posting the final solution...always good to see the results :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.