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

Look in the vba help index for FINDNEXT

--
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

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

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

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

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