Change Price of a Product on Active Cell

[fktripodi]

It would appreciated if someone could create a Macro to work in the following way
1. I need to Select any "product" from a number of products on sheet "A"
2. Run Macro
3. dialogue box appears prompting for a $ Amount to be inserted
4. Once price is inserted, it needs to find that Selected product on Sheet B in a certain columb, go back 7 cells and insert that price into that cell,
5. and then come back to sheet A Selecting original cell

Hope this makes sense

 

[John Davis]

If I understand correctly. This assumes that the certain column is Column H on Sheet B.

Sub fktripodi()
Dim rcell As Range
Dim ws As Worksheet
Dim x As String

Set ws = ActiveSheet

Set rcell = ActiveCell

x = InputBox("Please Enter the Price")

Sheets("Sheet B").Activate

Range("H1").Select

Do Until ActiveCell.Value = rcell.Value

    ActiveCell.Offset(1).Select
    
Loop

ActiveCell.Offset(, -7).Value = x

ws.Activate

rcell.Select


End Sub

 

[fktripodi]

As mentioned before John, this has been working excellent. Could it be slightly enhanced where by in the prompt box, it shows the current cell value, proir to it being changed, with text "Current Price $65.00"

If I understand correctly. This assumes that the certain column is Column H on Sheet B.

Sub fktripodi()
Dim rcell As Range
Dim ws As Worksheet
Dim x As String

Set ws = ActiveSheet

Set rcell = ActiveCell

x = InputBox("Please Enter the Price")

Sheets("Sheet B").Activate

Range("H1").Select

Do Until ActiveCell.Value = rcell.Value

    ActiveCell.Offset(1).Select
    
Loop

ActiveCell.Offset(, -7).Value = x

ws.Activate

rcell.Select


End Sub

 

[John Davis]

Maybe:

Sub fktripodi()
Dim rcell As Range
Dim ws As Worksheet
Dim x As String

Sheets("Sheet2").Activate

Range("H1").Select

Set ws = ActiveSheet

Set rcell = ActiveCell

x = InputBox("Current Price $" & rcell.Value & " Please Enter the new Price")


Do Until ActiveCell.Value = rcell.Value

    ActiveCell.Offset(1).Select
    
Loop

ActiveCell.Offset(, -7).Value = x

ws.Activate

rcell.Select


End Sub