This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

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

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

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.

what column to use?

what column to add/create?

what is the precentage you want to use?

From there it will do the work.

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?

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
```

If v(i, iSecondColumn) = "" Then

v(i, iDestinationColumn) = 0

Else

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

End If

```
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
```

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.

All Courses

From novice to tech pro — start learning today.

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

with this:

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

:)