Macro to change cell, based on unique cell to the left???

[natheplas]

Hi All,

I have a workbook which contains 100s of worksheets.
In column A of each worksheet contains unique product ids. Then to the right of those product ids are numbers (column b).
Sometimes we need to change what numbers are to the right of each product id.
A2 = BLX6497 B2 = 20
At the moment I am manually going through each worksheet and changing the value, which can take hours.
Can anyone please help me in writing a macro, so that you can click on it. Write or select the unique product ID, then type a number and it goes through each worksheet and alters the cell to the right of the product id, in column B.
The issue I have is whilst all product ids are in column A and the quantities are in column B, some worksheet have more product ids than others, so they are not perfectly in line (cell a6 on worksheet 1 might be a different product id to cell a6 on worksheet 2).

Please help, let me know if there isn't enough detail for this or any questions.
Thank you!

 

[Yongle]

Select the cell containing the ID (or indeed any cell if you want to type in the ID) and run this macro
- the first cell matching that ID in column A in every sheet is found and column B amended
- assumes ID only appears once on every sheet - is that correct?

Sub ID_Qty()
    Dim qty, ID, ID2
    Dim ws As Worksheet
'get ID
    ID = ActiveCell.Value
    ID2 = InputBox(ID & vbCr & vbCr & "OK to accept or enter ID", "")
    If ID2 <> vbNullString Then ID = ID2
'get qty
    qty = InputBox(ID & vbCr & vbCr & "Enter QUANTITY", "")
    For Each ws In ThisWorkbook.Sheets
        On Error Resume Next
        ws.Cells(Application.Match(ID, ws.Range("A:A"), 0), 2) = qty
    Next
End Sub

If you prefer to select a different way, let me know

 

[natheplas]

Thank you for your help, the macro seems to work, but then when you check the values, none of it has changed. Is there anything I need to do to the code to make it work for my workbook?
Yes the product id only appears once during each work sheet.
Thanks

 

[Yongle]

It is not finding a match
- we need to tell VBA more accurately what to look for

Is the ID numeric, alpha, alphanumeric?
If numeric are there any leading zeros?

 

[natheplas]

Hi Yongle, apologies for the slow reply, been tied up on something else.

Okay, the ID is alphanumeric and can have symbols in it too. Something even like Y4.6NS/T293.

Never leading zeros. Do you think it's doable?

Thank you,

 

[Yongle]

I tested the vba with your ID and it did exactly what I expected.

Y4.6NS/T293 in these cells
cell A1 on sheet1
cell A5 on sheet2
cell A7 on sheet3

- selected sheet1 cell A1
- ran the macro, accepting the ID as correct
- and entered quantity 1000 in 2nd box
- cells B1, B5 and B7 in the respective sheets were updated with value = 1000

- I inserted ID "XXX" ,"xxx" and "XXx" in A2, A6 and A8 respectively, ran the macro, typed xxx into the first box and 5000 as quantity
- B2, B6 and B8 all returned 5000

Is that not what you want it to do?

 

[natheplas]

Im sorry, would you let me know how you insert the VBA. Im not sure Im doing it right....

Ive gone with alt+f11 and then insert module.

Do I need to change anything within the code?

 

[Yongle]

That sounds perfect
So now we have to work out why it does not work for you

Are your sheets protected?