Solved

Add new column based on precentage of another column

Posted on 2011-02-21
12
283 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:techpr0
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 8

Expert Comment

by:ConUladh
ID: 34946069
You could still use a formula and turn off automatic calculation (Formulas -> Calculation options)
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34946095
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34946101
@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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:techpr0
ID: 34946106
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 Comment

by:techpr0
ID: 34946135
I basically want it to prompt me for following when complete
what column to use?
what column to add/create?
what is the precentage you want to use?

From there it will do the work.

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34946354
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 Comment

by:techpr0
ID: 34946757
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

Open in new window

0
 

Author Comment

by:techpr0
ID: 34946926
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
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 34946988
Replace this:

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

with this:

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

:)
0
 

Author Closing Comment

by:techpr0
ID: 34947033
That did the job
0
 

Author Comment

by:techpr0
ID: 34947037
Final Answer
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

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34948454
Thanks for posting the final solution...always good to see the results :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question