Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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 "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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 "Mike Fogleman" wrote: 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 "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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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. "Mike Fogleman" wrote: 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 "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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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 "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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row with Value
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |