Search for a text in a column when found copy cell above it
Closed
recyhood
Posts
2
Registration date
Friday January 4, 2013
Status
Member
Last seen
January 5, 2013
-
Jan 4, 2013 at 05:24 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 5, 2013 at 01:28 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 5, 2013 at 01:28 PM
Related:
- Search for a text in a column when found copy cell above it
- How to search for a word within a website - Guide
- How to search for a word in a pdf - Guide
- How to delete a column in word - Guide
- How to search a youtube channel - Guide
- How to search nearby friends in facebook - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 4, 2013 at 03:45 PM
Jan 4, 2013 at 03:45 PM
try this
Sub Macro3()
Dim maxRows As Long
Dim processRow As Long
Dim targetRow As Long
Dim sheetName As String
Dim searchItem As String
sheetName = ActiveSheet.Name
With Sheets(sheetName)
searchItem = "Ttstm063"
'find max rows in the target sheet
maxRows = getItemLocation("*", .Cells)
lProcessRow = 1
'find where the item is located with in range specified
targetRow = getItemLocation(searchItem, .Range(.Cells(lProcessRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
'LOOP WHILE WE ARE FINDING THE ITEM
Do While (targetRow > 0)
If (targetRow > 0) Then
Application.CutCopyMode = False
.Cells(targetRow - 2, "A").Copy
.Cells(targetRow - 2, "c").PasteSpecial
Application.CutCopyMode = False
'MOVE ONE ROW AFTER CURRENT FIND
lProcessRow = targetRow + 1
'EXHAUSTED ROWS
If lProcessRow > maxRows Then Exit Sub
'FIND NEXT MATCH
targetRow = getItemLocation(searchItem, .Range(.Cells(lProcessRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
End If
Loop
End With
End Sub
recyhood
Posts
2
Registration date
Friday January 4, 2013
Status
Member
Last seen
January 5, 2013
Jan 5, 2013 at 12:55 PM
Jan 5, 2013 at 12:55 PM
Hi rizvisa1
Thank you for your response, however ,
at this line maxRows = getItemLocation("*", .Cells)
I get error compile error: Sub or Function not defined therefore I cannot test it.
Recy
Thank you for your response, however ,
at this line maxRows = getItemLocation("*", .Cells)
I get error compile error: Sub or Function not defined therefore I cannot test it.
Recy
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 5, 2013 at 01:26 PM
Jan 5, 2013 at 01:26 PM
Oh I thought I had edited thread to add that . This is the full code
Sub Macro3()
Dim maxRows As Long
Dim processRow As Long
Dim targetRow As Long
Dim sheetName As String
Dim searchItem As String
sheetName = ActiveSheet.Name
With Sheets(sheetName)
searchItem = "Ttstm063"
'find max rows in the target sheet
maxRows = getItemLocation("*", .Cells)
lProcessRow = 1
'find where the item is located with in range specified
targetRow = getItemLocation(searchItem, .Range(.Cells(lProcessRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
'LOOP WHILE WE ARE FINDING THE ITEM
Do While (targetRow > 0)
If (targetRow > 0) Then
Application.CutCopyMode = False
.Cells(targetRow - 2, "A").Copy
.Cells(targetRow - 2, "c").PasteSpecial
Application.CutCopyMode = False
'MOVE ONE ROW AFTER CURRENT FIND
lProcessRow = targetRow + 1
'EXHAUSTED ROWS
If lProcessRow > maxRows Then Exit Sub
'FIND NEXT MATCH
targetRow = getItemLocation(searchItem, .Range(.Cells(lProcessRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
End If
Loop
End With
End Sub
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 5, 2013 at 01:28 PM
Jan 5, 2013 at 01:28 PM
some thing is wrong with this web site. it is deleting the msg.
You can find that missing function from this
https://ccm.net/forum/affich-606042-excel-vba2010-select-cells-between-2-keywords
You can find that missing function from this
https://ccm.net/forum/affich-606042-excel-vba2010-select-cells-between-2-keywords