Find Row with Value

[Karen McKenzie]

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?

 

[Mike Fogleman]

Clarify what you want in column BZ. If A2 and A6 are "cost centre" then
BZ2:BZ5 cell formulas would be "=$A$2". Then BZ6: BZwhatever would be
"=$A$6". All cells in BZ would display cost centre, but from the appropriate
cell in column A. Is this what you intend?

Mike F

 

[Joel]

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]

Thanks Mike, I appreciate you getting back to me.

Column BZ would contain $a$2, $a$6..... ie the address of the cell
containing cost centre not =$a$2. I'm then going to use that as part of an
indirect formula to lookup the relevant data

Hope that makes sense

 

[Karen McKenzie]

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

 

[Karen McKenzie]

Hi Mike,

What I've previously sent doesn't make sense. Looking at it again. What I
want in BZ is the row number of the occurance of "cost centre". This row
number would remain the same on all lines until the next occurance of cost
centre.

 

[Karen McKenzie]

Hi Joel,

Thinking about this more, what I sent already doesn't make sense, it is only
the row number that would be required, not the full cell reference

Regards
Karen

 

[Mike Fogleman]

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