Your description wasn't completely clear. Try to do the bestt I could. I
didn't know if formula in column BZ should be on the rows that contained
"cost centre" or only the rows inbetween. also wasn't suure what you meant
by "change the format so it becomes the number that date represents".

Sub ChangeWorksheet()

'theis is code to check column A

'rows.count is a constant which is the last row of sheet 65,536
'End(xlUp) says to go up rows until a cell containing data is found
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Set MyRange to all cells in column A containing data
Set ColARange = Range(Cells(1, 1), Cells(LastRow, 1))


CostCentreFound = False
PostingDateFound = False
'Now get each cell in column A
For Each cell In ColARange

Select Case cell.Value

Case "cost centre"

If CostCentreFound = False Then
CostCentreFound = True
Else
CostCentreFound = False
Range("BZ" & cell.Row).Formula = "=A" & cell.Row & ""
End If
Case "posting date"
PostingDateFound = True
End Select

If CostCentreFound = True Then

Range("BZ" & cell.Row).Formula = "=A" & cell.Row & ""

End If

If PostingDateFound = True Then

' not sure what you want
Range("CA" & cell.Row).NumberFormat = "0"

End If



Next cell


End Sub


"Karen McKenzie" wrote:

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?