Hi Joel,

Thanks for getting back to me. What I need BZ to show is the cell address
of the cell showing cost centre ie if Cells A6 + A10 = cost centre

BZ6 $a$6
BZ7 $a$6
BZ8 $a$6
BZ9 $a$6
BZ10 $a$10

This is then going to be used in an indirect lookup

On Row's where "posting date" occurs, column B contains the date, currently
formatted as text. I need column CA to contain the date, same as above for
all entries until there is a date change

ie A7 = posting date B7 = 28.03.2007
A11 = posting date B11 = 29.03.2007

CA7 39169 (text field of 28.03.2007 converted to excel number)
CA8 39169
CA9 39169
CA10 39169
CA11 39170



Hope this makes sense

"Joel" wrote:

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?