Changing Content in one coulumn using given content in another

C

Colin Hayes

Hi All

I have a small puzzle.

In one column , I have a list of various components , many of which
appear multiple times. In another , I have a list of their prices.

I need a small routine which will ask via popup for the column and
identify a particular component in it.

Then a new price for the chosen components will be requested and each
changed accordingly in the second column.

For example ;

Before

a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35

After ( component a is chosen , new price is 4.85)

a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35


Other prices for other components will be unaffected.

Can someone help?



Best Wishes
 
J

JLatham

Perhaps the routine below will work for you. You'd select an item which you
want to change the price for and then run the macro (you could put a button
or shape such as the text box from the drawing toolbar on the sheet and
attach the macro to it). It will then ask you for the new price, and if you
enter zero (the default at the prompt), nothing happens, but any non-zero
value will be used as the new price for all products/items that match.

Make a copy of your workbook to test with. Open the copy and press
[Alt]+[F11] to open the VB Editor. Choose Insert --> Module and copy the
code below and paste it into the module presented to you. Edit the Const
values in the code to tailor it to your worksheet. Close the VB Editor, save
the workbook and give it a try.

Sub ChangePrices()
'after selecting a cell that
'has the name of a product/item
'to change the price of, call
'this routine to change the
'price of that item in all
'instances where it appears
'in the same column.
'
'change these Const values as needed
'
'this should be the column
'that the product/item name
'is in
Const KeyCol = "A"
'this should be the column
'that the prices are in
Const priceCol = "B"

Dim searchItem As String
Dim searchList As Range
Dim anyCell As Range
Dim promptPhrase As String
'change the type as needed
'set up as Currency, but
'could also be Single or Double
'to accomodate decimal values
Dim newPrice As Currency

'test to validate that we
'should even begin processing
If Selection.Cells.Count > 1 Then
'more than one cell selected
Exit Sub
End If
If Selection.Column <> _
Range(KeyCol & 1).Column Then
'not in the proper column
Exit Sub
End If
'set up prompt for the new price
promptPhrase = "Enter the new price for '"
promptPhrase = promptPhrase & ActiveCell.Value
promptPhrase = promptPhrase & "'." & vbCrLf & _
"Enter zero or just press [Enter] to cancel."
newPrice = _
InputBox(promptPhrase, "Enter New Price", 0)
If newPrice = 0 Then
Exit Sub ' user cancelled
End If
'capture the product/item name
searchItem = ActiveCell.Value
'set up the range to be searched
'assumes row 1 has a label
Set searchList = _
Range(KeyCol & "2:" & _
Range(KeyCol & Rows.Count).End(xlUp).Address)
'improve performance
Application.ScreenUpdating = False
'do the work
For Each anyCell In searchList
If anyCell = searchItem Then
Range(priceCol & anyCell.Row) = newPrice
End If
Next
'house cleaning
Set searchList = Nothing
'announce job completion
MsgBox "Price change for '" & _
ActiveCell.Value & "' completed."
End Sub
 
T

T. Valko

I like the detailed comments!

--
Biff
Microsoft Excel MVP


JLatham said:
Perhaps the routine below will work for you. You'd select an item which
you
want to change the price for and then run the macro (you could put a
button
or shape such as the text box from the drawing toolbar on the sheet and
attach the macro to it). It will then ask you for the new price, and if
you
enter zero (the default at the prompt), nothing happens, but any non-zero
value will be used as the new price for all products/items that match.

Make a copy of your workbook to test with. Open the copy and press
[Alt]+[F11] to open the VB Editor. Choose Insert --> Module and copy the
code below and paste it into the module presented to you. Edit the Const
values in the code to tailor it to your worksheet. Close the VB Editor,
save
the workbook and give it a try.

Sub ChangePrices()
'after selecting a cell that
'has the name of a product/item
'to change the price of, call
'this routine to change the
'price of that item in all
'instances where it appears
'in the same column.
'
'change these Const values as needed
'
'this should be the column
'that the product/item name
'is in
Const KeyCol = "A"
'this should be the column
'that the prices are in
Const priceCol = "B"

Dim searchItem As String
Dim searchList As Range
Dim anyCell As Range
Dim promptPhrase As String
'change the type as needed
'set up as Currency, but
'could also be Single or Double
'to accomodate decimal values
Dim newPrice As Currency

'test to validate that we
'should even begin processing
If Selection.Cells.Count > 1 Then
'more than one cell selected
Exit Sub
End If
If Selection.Column <> _
Range(KeyCol & 1).Column Then
'not in the proper column
Exit Sub
End If
'set up prompt for the new price
promptPhrase = "Enter the new price for '"
promptPhrase = promptPhrase & ActiveCell.Value
promptPhrase = promptPhrase & "'." & vbCrLf & _
"Enter zero or just press [Enter] to cancel."
newPrice = _
InputBox(promptPhrase, "Enter New Price", 0)
If newPrice = 0 Then
Exit Sub ' user cancelled
End If
'capture the product/item name
searchItem = ActiveCell.Value
'set up the range to be searched
'assumes row 1 has a label
Set searchList = _
Range(KeyCol & "2:" & _
Range(KeyCol & Rows.Count).End(xlUp).Address)
'improve performance
Application.ScreenUpdating = False
'do the work
For Each anyCell In searchList
If anyCell = searchItem Then
Range(priceCol & anyCell.Row) = newPrice
End If
Next
'house cleaning
Set searchList = Nothing
'announce job completion
MsgBox "Price change for '" & _
ActiveCell.Value & "' completed."
End Sub


Colin Hayes said:
Hi All

I have a small puzzle.

In one column , I have a list of various components , many of which
appear multiple times. In another , I have a list of their prices.

I need a small routine which will ask via popup for the column and
identify a particular component in it.

Then a new price for the chosen components will be requested and each
changed accordingly in the second column.

For example ;

Before

a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35

After ( component a is chosen , new price is 4.85)

a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35


Other prices for other components will be unaffected.

Can someone help?



Best Wishes
.
Click to expand...
Click to expand...
 
J

JLatham

Thanks!
I like to dream a little and actually have hope that perhaps a little
teaching can be accomplished along with providing help or solutions. And
even if the specific OP doesn't carry on with learning VBA, someone finding
such a post as a possible solution to their own problem might be able to
figure out if it's suitable and how to modify it for their own use.
Also, if someone sees it and realizes there's a better way to accomplish a
particular part of the code, then maybe they'll mention it.

T. Valko said:
I like the detailed comments!

--
Biff
Microsoft Excel MVP


JLatham said:
Perhaps the routine below will work for you. You'd select an item which
you
want to change the price for and then run the macro (you could put a
button
or shape such as the text box from the drawing toolbar on the sheet and
attach the macro to it). It will then ask you for the new price, and if
you
enter zero (the default at the prompt), nothing happens, but any non-zero
value will be used as the new price for all products/items that match.

Make a copy of your workbook to test with. Open the copy and press
[Alt]+[F11] to open the VB Editor. Choose Insert --> Module and copy the
code below and paste it into the module presented to you. Edit the Const
values in the code to tailor it to your worksheet. Close the VB Editor,
save
the workbook and give it a try.

Sub ChangePrices()
'after selecting a cell that
'has the name of a product/item
'to change the price of, call
'this routine to change the
'price of that item in all
'instances where it appears
'in the same column.
'
'change these Const values as needed
'
'this should be the column
'that the product/item name
'is in
Const KeyCol = "A"
'this should be the column
'that the prices are in
Const priceCol = "B"

Dim searchItem As String
Dim searchList As Range
Dim anyCell As Range
Dim promptPhrase As String
'change the type as needed
'set up as Currency, but
'could also be Single or Double
'to accomodate decimal values
Dim newPrice As Currency

'test to validate that we
'should even begin processing
If Selection.Cells.Count > 1 Then
'more than one cell selected
Exit Sub
End If
If Selection.Column <> _
Range(KeyCol & 1).Column Then
'not in the proper column
Exit Sub
End If
'set up prompt for the new price
promptPhrase = "Enter the new price for '"
promptPhrase = promptPhrase & ActiveCell.Value
promptPhrase = promptPhrase & "'." & vbCrLf & _
"Enter zero or just press [Enter] to cancel."
newPrice = _
InputBox(promptPhrase, "Enter New Price", 0)
If newPrice = 0 Then
Exit Sub ' user cancelled
End If
'capture the product/item name
searchItem = ActiveCell.Value
'set up the range to be searched
'assumes row 1 has a label
Set searchList = _
Range(KeyCol & "2:" & _
Range(KeyCol & Rows.Count).End(xlUp).Address)
'improve performance
Application.ScreenUpdating = False
'do the work
For Each anyCell In searchList
If anyCell = searchItem Then
Range(priceCol & anyCell.Row) = newPrice
End If
Next
'house cleaning
Set searchList = Nothing
'announce job completion
MsgBox "Price change for '" & _
ActiveCell.Value & "' completed."
End Sub


Colin Hayes said:
Hi All

I have a small puzzle.

In one column , I have a list of various components , many of which
appear multiple times. In another , I have a list of their prices.

I need a small routine which will ask via popup for the column and
identify a particular component in it.

Then a new price for the chosen components will be requested and each
changed accordingly in the second column.

For example ;

Before

a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35

After ( component a is chosen , new price is 4.85)

a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35


Other prices for other components will be unaffected.

Can someone help?



Best Wishes
.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Colin Hayes

Hi

OK Thanks for your help with this. It worked perfectly , first time.

I'm grateful for your time and expertise.



Best Wishes




JLatham said:
Perhaps the routine below will work for you. You'd select an item which you
want to change the price for and then run the macro (you could put a button
or shape such as the text box from the drawing toolbar on the sheet and
attach the macro to it). It will then ask you for the new price, and if you
enter zero (the default at the prompt), nothing happens, but any non-zero
value will be used as the new price for all products/items that match.

Make a copy of your workbook to test with. Open the copy and press
[Alt]+[F11] to open the VB Editor. Choose Insert --> Module and copy the
code below and paste it into the module presented to you. Edit the Const
values in the code to tailor it to your worksheet. Close the VB Editor, save
the workbook and give it a try.

Sub ChangePrices()
'after selecting a cell that
'has the name of a product/item
'to change the price of, call
'this routine to change the
'price of that item in all
'instances where it appears
'in the same column.
'
'change these Const values as needed
'
'this should be the column
'that the product/item name
'is in
Const KeyCol = "A"
'this should be the column
'that the prices are in
Const priceCol = "B"

Dim searchItem As String
Dim searchList As Range
Dim anyCell As Range
Dim promptPhrase As String
'change the type as needed
'set up as Currency, but
'could also be Single or Double
'to accomodate decimal values
Dim newPrice As Currency

'test to validate that we
'should even begin processing
If Selection.Cells.Count > 1 Then
'more than one cell selected
Exit Sub
End If
If Selection.Column <> _
Range(KeyCol & 1).Column Then
'not in the proper column
Exit Sub
End If
'set up prompt for the new price
promptPhrase = "Enter the new price for '"
promptPhrase = promptPhrase & ActiveCell.Value
promptPhrase = promptPhrase & "'." & vbCrLf & _
"Enter zero or just press [Enter] to cancel."
newPrice = _
InputBox(promptPhrase, "Enter New Price", 0)
If newPrice = 0 Then
Exit Sub ' user cancelled
End If
'capture the product/item name
searchItem = ActiveCell.Value
'set up the range to be searched
'assumes row 1 has a label
Set searchList = _
Range(KeyCol & "2:" & _
Range(KeyCol & Rows.Count).End(xlUp).Address)
'improve performance
Application.ScreenUpdating = False
'do the work
For Each anyCell In searchList
If anyCell = searchItem Then
Range(priceCol & anyCell.Row) = newPrice
End If
Next
'house cleaning
Set searchList = Nothing
'announce job completion
MsgBox "Price change for '" & _
ActiveCell.Value & "' completed."
End Sub


Colin Hayes said:
Hi All

I have a small puzzle.

In one column , I have a list of various components , many of which
appear multiple times. In another , I have a list of their prices.

I need a small routine which will ask via popup for the column and
identify a particular component in it.

Then a new price for the chosen components will be requested and each
changed accordingly in the second column.

For example ;

Before

a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35
a 2.50
b 3.60
c 11.56
d 6.35

After ( component a is chosen , new price is 4.85)

a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35
a 4.85
b 3.60
c 11.56
d 6.35


Other prices for other components will be unaffected.

Can someone help?



Best Wishes
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find last date of sold stock and sold price. 2
Formula for looking up and copying from another worksheet? 1
How do I retrieve Cost from a grid using text drop down lists? 3
Word VBA - Automatic change content of a cell in table using dropdown list 5
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Find text in N columns and one Row 1
Parsing Information from a Cell 3
Finding dynamic maxima 9

Top