changing values acroos worksheets

[mike]

Hello and good morning to all!

I got a problem...due to lack of experiance i didnt do
this ina better way.
I have a workbook with 33wrkshts. each worksheets in the
A2 column has a list of plant with the cost for each in
the A3 column. each sheet is a different lists (random).
one worksheets is a individual area.
so..here smy deal. i have a plantlist in a worksheet
alone and use it as a refernce for a userform.
now i recently discovered that the plant lists cost has
changed for many of the plants. Is there a way i can go
thru and change the values in A3 for the neccessary
plants.
If i had done a vlookup the first time around, i 'd be
okay.
but for now , is there a macro i need to create for this
or is there a function i do not know of to do this?

thanks again to all who helps?

 

[mike]

update...my plantlist is 63Rows. if it helps any.

 

[Tom Ogilvy]

Assume you have a list of plants and their costs on a sheet named Data

Column A lists the plants as they would appear on the 33 worksheets (in
column B of those sheets) and column B (of Data) the new cost

Assume on the 33 worksheets, the plants are listed in column B with the cost
in column C (you stated in columns A2 and A3, but I assume you meant in
column B and column C, since A2 and A3 are meaningless in terms of column
locations)

Assume the 33 sheets are the last 33 sheets in the workbook

Sub ChangeCosts()
Dim rngData As Range
Dim firstSheet As Long
Dim i As Long, sh As Worksheet
Dim rng As Range, Cell As Range
Dim res As Variant
rngData = Worksheets("Data").Range("A1:B500")
firstSheet = Sheets.Count - 33 + 1

For i = firstSheet To Sheets.Count
Set sh = Sheets(i)
Set rng = sh.Range(sh.Cells(1, 2), sh.Cells(Rows.Count, 2).End(xlUp))
For Each Cell In rng
res = Application.VLookup(Cell.Value, rngData, 2, False)
If Not IsError(res) Then
Cell.Offset(0, 1).Value = res
Cell.Offset(0, 1).Interior.ColorIndex = 5 ' color cell, optional
End If
Next Cell
Next i
End Sub


Obviously test this on a copy of your workbook.

 

[mike]

tom,

the plant do not appear as exactly as they do on the
sheets. each sheet has a selection picked form the
list..would that matter?
the columns areas areas they are respectively to the
columns.
"B" and "C"..

thanks

 

[Tom Ogilvy]

If the name of a plant is

Buttercup, Yellow, medium

on both the sheet you want to change and the sheet where you will lookup the
price is what I mean by are the same on both sheets. Note that the list of
plants on one sheet does not have to match the list of plants on the other
lookup sheet (that would be pointless). Note that I am using Vlookup same
as you suggested you should have done originally. So if you now entered
Vlookup on one of the 33 worksheets in column C, referring to the plant for
that row in column B (as the lookup value) and that would work, then the
code should work.

 

[mlm]

YEs.. thank you it worked..

-----Original Message-----
If the name of a plant is

Buttercup, Yellow, medium

on both the sheet you want to change and the sheet where you will lookup the
price is what I mean by are the same on both sheets. Note that the list of
plants on one sheet does not have to match the list of plants on the other
lookup sheet (that would be pointless). Note that I am using Vlookup same
as you suggested you should have done originally. So if you now entered
Vlookup on one of the 33 worksheets in column C, referring to the plant for
that row in column B (as the lookup value) and that would work, then the
code should work.

--
Regards,
Tom Ogilvy





.

 

[mike]

okay, i see what this does but i am a bit lost on the
process of it. I put this code in the This workbook
module?
and then how do i actually tell it what i want to change?
or actually i am leaning to thinking it changes evrything
in the list on hte worksheet to have a vlooup value??
right? is that what it is doing? so all i have to do it
change the plant list whenever prices change?

maybei am thinking too hard again.
mike, thanks