Search for value move to next column code not working

[Guest]

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

 

[Don Guillett]

Look in the vba help index for FINDNEXT

 

[Don Guillett]

While you are at it, also look for CUT as in

Cells(I, 1).cut = Cells(I, 2)

 

[Guest]

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

 

[Dan R.]

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

 

[Guest]

It worked perfectly.
Thank you

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