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

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" wrote:


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
.

I like the detailed comments!

--
Biff
Microsoft Excel MVP


"JLatham" wrote in message
...
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" wrote:


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
.

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" wrote:

I like the detailed comments!

--
Biff
Microsoft Excel MVP


"JLatham" wrote in message
...
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" wrote:


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
.



.

Hi

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

I'm grateful for your time and expertise.



Best Wishes




In article , JLatham
writes
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" wrote:


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
.