Change Cell Data of a Required Column

  • Thread starter Thread starter jonathan
  • Start date Start date
J

jonathan

Hi Y'all,

I have a huge ODBC report which is taking longer to prepare and I think some
of the "manual functions" of changing cell contents based on its current
content could be done by a macro............

My Dilema is I want the macro to look at the contents of all cells in a
column and where content equals XYZ the macro changes the contents to XXX
otherwise the cell contents are changed to AAA.

As I don't know how long the table will be this macro needs to only check
the data in cells that have been refreshed from the ODBC.

I have tried using "If" statemts and "Do........ Loops" but seems to be
getting nowhere.

Any code pointers would be really appreciated


TY

Jonathan
 
The following code is not meant to work exactly for your
needs (I don't know exactly what your needs are), however
I hope it will give you ideas you haven't thought of to
accomplish your task. Open a blank new worksheet and put
some values in a coulmn along with the XYZ string and
step through it. You will have to comment out
Application.ScreenUpdate = False to see changes occur.

Marty
***************************************
Sub FindReplace()
'When checking the value of a cell the text in quotes
'is case sensitive. Meaning there is a difference
'between "xyz" and "XYZ"

Application.ScreenUpdating = False 'keeps the video from _
updating so the macro runs faster

Range("A1").Activate 'Change A1 to the coulmn of choice

'*********Sort to get all the XYZ's in a
group***********
Selection.Sort Key1:=Range("A1"),
Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Do
If ActiveCell.Value = "xyz" Then '
ActiveCell.Value = "XXX"
ActiveCell.Offset(1).Activate
Else
ActiveCell.Value = "AAA"
ActiveCell.Select
Selection.Copy 'copies the AAA
ActiveCell.Offset(1).Activate
'The next line selects all the cells from the
point of
'the cursor to the first null cell in the coulmn
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste 'Pastes the AAA
GoTo Line1
End If
Loop Until ActiveCell.Value = ""
Line1:
End Sub
 
Thanks for this Marty I'll give it a go - at least it has given me loads of
ideas for other problems if not for this

Jonathan
 
Back
Top