Search for value move to next column code not working
Please assist.
I would like the code to search column a for any instance of total remove it and place in the next column b Dim lastrow As Long Dim strTotal As String Dim Rng As Range Application.ScreenUpdating = False strTotal = "total" With ActiveSheet lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With Set Rng = Range("a:a").Find(What:=strTotal, _ After:=Range("a" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) For I = lastrow To 2 Step -1 If Cells(I, 1).Value = Rng Then Cells(I, 2).Value = Cells(I, 1).Value End If Next I Application.ScreenUpdating = True Thanks |
Search for value move to next column code not working
While you are at it, also look for CUT as in
Cells(I, 1).cut = Cells(I, 2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Gwen" wrote in message ... Please assist. I would like the code to search column a for any instance of total remove it and place in the next column b Dim lastrow As Long Dim strTotal As String Dim Rng As Range Application.ScreenUpdating = False strTotal = "total" With ActiveSheet lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With Set Rng = Range("a:a").Find(What:=strTotal, _ After:=Range("a" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) For I = lastrow To 2 Step -1 If Cells(I, 1).Value = Rng Then Cells(I, 2).Value = Cells(I, 1).Value End If Next I Application.ScreenUpdating = True Thanks |
Search for value move to next column code not working
So if I understand you what you want to do is to find all instances of Total
in column A and move that to column B? Give this a try... Sub MoveTotal() Dim rngToSearch As Range Dim rngFound As Range Dim strWhat As String strWhat = "Total" Set rngToSearch = ActiveSheet.Columns("A") Set rngFound = rngToSearch.Find(What:=strWhat, _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ MatchCase:=False) Do While Not rngFound Is Nothing rngFound.Offset(0, 1).Value = rngFound.Value rngFound.ClearContents Set rngFound = rngToSearch.FindNext(rngFound) Loop End Sub -- HTH... Jim Thomlinson "Gwen" wrote: Please assist. I would like the code to search column a for any instance of total remove it and place in the next column b Dim lastrow As Long Dim strTotal As String Dim Rng As Range Application.ScreenUpdating = False strTotal = "total" With ActiveSheet lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With Set Rng = Range("a:a").Find(What:=strTotal, _ After:=Range("a" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) For I = lastrow To 2 Step -1 If Cells(I, 1).Value = Rng Then Cells(I, 2).Value = Cells(I, 1).Value End If Next I Application.ScreenUpdating = True Thanks |
Search for value move to next column code not working
Replace this:
If Cells(I, 1).Value = Rng Then Cells(I, 2).Value = Cells(I, 1).Value End If With this: If Cells(I, 1).Value = Rng Then Cells(I, 1).Cut Cells(I, 2) End If -- Dan |
Search for value move to next column code not working
It worked perfectly.
Thank you "Jim Thomlinson" wrote: So if I understand you what you want to do is to find all instances of Total in column A and move that to column B? Give this a try... Sub MoveTotal() Dim rngToSearch As Range Dim rngFound As Range Dim strWhat As String strWhat = "Total" Set rngToSearch = ActiveSheet.Columns("A") Set rngFound = rngToSearch.Find(What:=strWhat, _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ MatchCase:=False) Do While Not rngFound Is Nothing rngFound.Offset(0, 1).Value = rngFound.Value rngFound.ClearContents Set rngFound = rngToSearch.FindNext(rngFound) Loop End Sub -- HTH... Jim Thomlinson "Gwen" wrote: Please assist. I would like the code to search column a for any instance of total remove it and place in the next column b Dim lastrow As Long Dim strTotal As String Dim Rng As Range Application.ScreenUpdating = False strTotal = "total" With ActiveSheet lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With Set Rng = Range("a:a").Find(What:=strTotal, _ After:=Range("a" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) For I = lastrow To 2 Step -1 If Cells(I, 1).Value = Rng Then Cells(I, 2).Value = Cells(I, 1).Value End If Next I Application.ScreenUpdating = True Thanks |
All times are GMT +1. The time now is 05:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com