GLOBAL REPLACE

I can't really determine which columns your data
is in so you'll have to figure out how to modify
this formula, but assuming that DATE is in column
D, the cells in column C and D directly beneath
each ACCOUNT and DATE is blank and your 1st data
row is 1:1, then you could enter the formula,
=IF(ISNUMBER(D1),A2,""), into cell C1 and copy
to the end of your data set.

Also...

I neglected to say that after you copy this formula
down your data set you probably should do a
Paste Special... Values to preserve the results.

-- And --

If you're having trouble tweaking this formula to
conform to your data layout you should select
the 1st cell in column C where you entered this
formula, =IF(ISNUMBER(D1),A2,""), and then
click on the formula bar. Excel will outline the
cell references used by this formula with a color
that matches the corresponding cell reference in
the formula. If the 1st date value isn't outlined
then double-click the cell reference of the ISNUMBER()
function and then click on the 1st date value. Do
the same for the cell reference associated with the
value in the 1st column and 2nd row of each data
block.

MARK
THE CELL WHICH I NEED A GLOBAL REPLACE IS IN ROW 1 COLUMN C
THAT COLUMN ALWAYS HAS TEXT IN THAT CELL WHICH IS 000-00666-1-1.
IT WILL BE REPLACED WITH THE ROW AFTER THAT IN THE FIRST COLUMN WHICH IS ALSO TEXT.

MARK
THE CELL WHICH I NEED A GLOBAL REPLACE IS IN COLUMN C
THAT COLUMN ALWAYS HAS TEXT IN THAT CELL WHICH IS 000-00666-1-1.
IT WILL BE REPLACED WITH WHAT EVER IS IN THE ROW AFTER THAT IN THE FIRST COLUMN WHICH IS ALSO TEXT.

THANK YOU FOR YOUR ANTICIPAED COOPERATION IN THIS MATTER.
YANECKC

Hi YANECKC

I believe a macro would be far easier to do this task in a jiff!

This code assumes you text to replace is in Column C and the replacement text is the next row down, column A. To use it push Alt+F11, go to Insert>Module and paste in the code:

Sub FindAndReplace()
Dim i As Integer, iCount As Integer
Application.ScreenUpdating = False
Cells(1, 3).Select'Cell C1
i = WorksheetFunction.CountIf _
(Columns(3), "000-00666-1-1")

Do Until iCount = i
Columns(3).Find(What:="000-00666-1-1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
ActiveCell.Replace What:="000-00666-1-1", Replacement:=ActiveCell.Offset(1, -2), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
iCount = iCount + 1
Loop
Application.ScreenUpdating = True
End Sub

If Column C is not correct then modify these bits:

Cells(1, 3).Select
Columns(3)
ActiveCell.Offset(1, -2)

Push Alt+Q then push Alt+F8 and select "FindAndReplace" then click "Run"

Any problems at all let me know.

Dave

OzGrid Business Applications

Hi YANECKC, I had posted this before but you may have overlooked it. So here it is again.

I believe a macro would be far easier to do this task in a jiff!

This code assumes you text to replace is in Column C and the replacement text is the next row down, column A. To use it push Alt+F11, go to Insert>Module and paste in the code:

Sub FindAndReplace()
Dim i As Integer, iCount As Integer
Application.ScreenUpdating = False
Cells(1, 3).Select'Cell C1
i = WorksheetFunction.CountIf _
(Columns(3), "000-00666-1-1")

Do Until iCount = i
Columns(3).Find(What:="000-00666-1-1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
ActiveCell.Replace What:="000-00666-1-1", Replacement:=ActiveCell.Offset(1, -2), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
iCount = iCount + 1
Loop
Application.ScreenUpdating = True
End Sub

If Column C is not correct then modify these bits:

Cells(1, 3).Select
Columns(3)
ActiveCell.Offset(1, -2)

Push Alt+Q then push Alt+F8 and select "FindAndReplace" then click "Run"

Any problems at all let me know.

Dave

OzGrid Business Applications

GREAT JOB DAVE!
THE MACRO IS THE WAY TO GO!

ONE MORE QUESTION.
CAN I CREATE A MACRO TO DELETE A ROW IF
COLUMN D = ACCT OR SEQ OR LAD OR MKT

ONCE AGAIN THANKS FOR YOUR HELP!

Yes you can! The "Filter Delete" attached to "CommandButton1" is the best option if your data is oganised in list form.

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
.AutoFilterMode = False
.Range("A1:E1").AutoFilter
.Range("A1:E1").AutoFilter Field:=4, Criteria1:="=ACCT" _
, Operator:=xlOr, Criteria2:="=SEQ"
.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Range("A1:E1").AutoFilter Field:=4, Criteria1:="=LAD" _
, Operator:=xlOr, Criteria2:="=MKT"
.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub




This one will also work, but is slower.

Private Sub CommandButton2_Click()
Dim LookIn As Range, DeleteRow As Range
Set LookIn = Columns("D:D").SpecialCells(xlCellTypeConstants)

For Each DeleteRow In LookIn
Select Case DeleteRow.Value
Case "ACCT", "SEQ", "LAD", "MKT"
DeleteRow.EntireRow.Delete
End Select
Next
End Sub

Dave

OzGrid Business Applications