#1
|
|||
|
|||
Need a Macro to go to column named price and ADD 16% to each field
Can someone help give me a general overview of what i need to do to create a macro that would with a push of a button allow me to go to a column named PRICE in my spreadsheet no matter where it is as it may be in different locatations each time i do this and ADD 16% to each field I have created macros before and and have a general understanding but not totally sure what the the proper steps are. |
#2
|
|||
|
|||
You need a couple things to happen. The first is finding the column that has the word price in it. That is done with
Code:
PriceColumn = range("1:1").find("PRICE").column Code:
LastRow = cells(50000, PriceColumn).end(xlup).row Code:
Option Explicit Sub SumPriceColumn() 'Looks for a column named price and adds 16% to each row Dim CheckRow As Long, LastRow As Long, PriceColumn As Long, RowIssue As String On Error Resume Next PriceColumn = Range("1:1").Find("PRICE", LookAt:=xlWhole, MatchCase:=False).Column 'Tell user if price column was not found If PriceColumn = 0 Then MsgBox "There was no column in row 1 with the word Price" End End If On Error GoTo 0 'Fix error handling back to normal 'Find the final row LastRow = Cells(50000, PriceColumn).End(xlUp).Row 'Use a loop to add 16% to all values On Error GoTo NotaNumber For CheckRow = 2 To LastRow Cells(CheckRow, PriceColumn).Value = Cells(CheckRow, PriceColumn).Value * 1.16 Next CheckRow If RowIssue = "" Then MsgBox "Complete" Exit Sub Else MsgBox "The following rows: " & RowIssue & " were not numbers and could not have 16% added" _ & vbLf & "They have been highlighted red and skipped." Exit Sub End If NotaNumber: Cells(CheckRow, PriceColumn).Interior.Color = vbRed RowIssue = RowIssue & CheckRow & ", " Resume Next End Sub |
#3
|
|||
|
|||
Thanks so much i will let you know how it works out
|
#4
|
|||
|
|||
well am not as smart as I thought. 2 questions can you produce a vba app that would automate this and what would be your fees to create an app that will find the column named price and then add a specified percentage to each amount in that col. so i should be able to say what ever percentage, like 15% and then it would automatically add it to each field under the price column for example
%=.15 BEFORE price $10 $200 AFTER price $11.5 $230 |
#5
|
|||
|
|||
fees? There are no fees this is all volunteer work and I am happy to do it. The modified code below can be placed in a module of your excel sheet and then you can call it whenever. This code will ask you for the percentage and then do the same thing as the last code.
Code:
Option Explicit Sub SumPriceColumn() 'Looks for a column named price and adds a specified percent to each row Dim CheckRow As Long, LastRow As Long, PriceColumn As Long, RowIssue As String Dim PricePercent As Single, PercentString As String, PercentConfirm As String On Error Resume Next PriceColumn = Range("1:1").Find("PRICE", LookAt:=xlWhole, MatchCase:=False).Column 'Tell user if price column was not found If PriceColumn = 0 Then MsgBox "There was no column in row 1 with the word Price" End End If On Error GoTo 0 'Fix error handling back to normal 'Find the final row LastRow = Cells(50000, PriceColumn).End(xlUp).Row PercentString = InputBox("What percent would you like to add to the " & _ "values in column " & Chr(64 + PriceColumn) & ".") If PercentString = "" Then MsgBox "Nothing entered program is ending." End End If 'convert the string to single On Error Resume Next PricePercent = CSng("1." & PercentString) If Err.Number <> 0 Then MsgBox "Cannot convert " & PercentString & " into a decimal. " & _ vbLf & "Please try again using a whole number." End End If PercentConfirm = MsgBox("This will add " & PercentString & "% to all values in " _ & "column " & Chr(64 + PriceColumn) & ". Do you want to proceed?", vbYesNoCancel) If PercentConfirm = vbNo Or PercentConfirm = vbCancel Then End 'Use a loop to add specified percent to all values On Error GoTo NotaNumber For CheckRow = 2 To LastRow Cells(CheckRow, PriceColumn).Value = Round(Cells(CheckRow, PriceColumn).Value * PricePercent, 2) Next CheckRow If RowIssue = "" Then MsgBox "Complete" Exit Sub Else MsgBox "The following rows: " & RowIssue & " were not numbers and could not have " & _ PercentString & "% added" & vbLf & "They have been highlighted red and skipped." Exit Sub End If NotaNumber: Cells(CheckRow, PriceColumn).Interior.Color = vbRed RowIssue = RowIssue & CheckRow & ", " Resume Next End Sub |
Tags |
macros |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel: Item and Price | wchristner | Excel | 2 | 07-21-2014 06:23 AM |
Macro for Column to Rows Data | brunssl2 | Excel Programming | 3 | 04-28-2014 07:07 AM |
Macro help - search for value, paste a value in another column | IRollman | Excel Programming | 1 | 01-14-2014 01:05 PM |
How to line up a merged field to a column heading | j_Southern | Mail Merge | 2 | 10-08-2012 12:28 PM |
Yearly price escalator? | markg2 | Excel | 1 | 04-15-2012 01:47 AM |