This modified version from Joel seems to do what you want:

Sub ChangeWorksheet()
Dim LastRow As Long, RowNum As Long
Dim ColARange As Range, c As Range
Dim CostCentreFound As Boolean, PostingDateFound As Boolean
Dim MyDate As String

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set ColARange = Range(Cells(1, 1), Cells(LastRow, 1))

CostCentreFound = False
PostingDateFound = False

For Each c In ColARange
Select Case c.Value
Case "cost centre"
PostingDateFound = False
If CostCentreFound = False Then
CostCentreFound = True
RowNum = c.Row
Else
CostCentreFound = False
RowNum = c.Row
Range("BZ" & c.Row).Value = RowNum
End If
Case "posting date"
PostingDateFound = True
End Select

If CostCentreFound = True Then
Range("BZ" & c.Row).Value = RowNum
End If

If PostingDateFound = True Then
Range("BZ" & c.Row).Value = RowNum
MyDate = c.Offset(0, 1).Value
Range("CA" & c.Row).Value = DateValue(MyDate)
Range("CA" & c.Row).NumberFormat = "0"
End If
Next c
End Sub

Mike F
"Karen McKenzie" wrote in message
...
I'm hoping someone can help me.
I have a file which will have data pasted into it each day. The number of
rows and columns will vary each day. In order to manipulate this data I
need
guidance as to how to do the following:

When "cost centre" is found in column A, put cell reference of this entry
into all rows in column BZ until the next entry of "cost centre" is found.
Continue to do this till end of sheet.

When "posting date" is found in column A, paste cell value of columnn B in
that row into column CA and change the format so it becomes the number
that
date represents. Continue to do this till end of sheet

Can anyone help?