Another Excel query

[TMA]

I have a workbook with about a hundred sheets. On each sheet, there's one cell whose contents I want to change.

The cell contains the word 'scale', which I want to change to 'nominal'. Only there's lots of cells with 'scale' in them, and I only want to change the ones which have 'hv17' in the cell directly to the left.

Is there a quick way to do this?

[JtS]

Another Excel query

There seems to be some software you can download to help

[Ibn Pickthall]

Another Excel query

Is the cell you want to change the same cell on every worksheet, ie, cell B6?

[JtS]

Another Excel query

Actually, you can hi-light all sheet, click on the first one then hold shift then click on the last one

Then do do ctrl-h (i.e. find and replace)

job done

[JtS]

Another Excel query

That works no matter where the cell is

[JtS]

Another Excel query

Oh hold up, I've just read your other bit

[TMA]

Another Excel query

Ibn: no, sadly.

JtS: yeah, it's the 'dependent' cell bit that's causing the problem. There seem to be some advanced 'find & replace' downloads, as you say, so I may have to have a look through those.

[Guest]

Another Excel query

Is HV17 always in the same column? or are they dotted about the sheets willy nilly?

[TMA]

Another Excel query

Yes, it's always in the same column. Just a different row on each sheet.

[Guest]

Another Excel query

Ok, a simple way to do it, sort the entire spreadheet by columns in which Hv17 appears, this will return your data sorted by the value in whatever column HV17 is, scroll down to the start of the HV17's and then change the first entry to the right to nominal and use the fill handle to drag down nominal to the end of the HV17's and you have the job done, and do for each sheet you have. There is probably some VBA out there that can do this if i come across it I will post it here for you. :-)

[Guest]

Another Excel query

Told you there was some VBA code out there to do it and here it is,

Sub ScaleToNominal()

For S = 1 To Worksheets.Count

For R = 1 To Worksheets(S).Cells.SpecialCells(xlLastCell).Row
For C = 1 To Worksheets(S).Cells.SpecialCells(xlLastCell).Colum n

If Worksheets(S).Cells(R, C).Value = "HV17" Then
If Worksheets(S).Cells(R, C + 1) = "Scale" Then Worksheets(S).Cells(R, C + 1) = "Nominal"
End If

Next C
Next R

Next S

End Sub

This will go through each sheet, checking each cell (up to the last row & column with a data entry).
This is case sensitive, so you will need to type the HV17, Scale & Nominal, as they appear.
You can always run the code again, changing HV17 to hv17 etc, if there are variations.

[TMA]

Another Excel query

The VBA code only went and worked! Thanks, cheeptric (and a belated welcome to WSC/OTF, as well).

[Guest]

Another Excel query

Not a problem and thank you.

[Wyatt Earp]

Another Excel query

God, VBA is a shit language, isn't it?

[Guest]

Another Excel query

Yes, especially when you fuck it up! and can't see where you have fucked up (even more so in MS Access)