This code looks like what I want to accomplish. I thought I could put
it in as a module and then run it from the macro toolbar, but I'm
obviously wrong. Sorry I'm a noob. Where do I put this?

Usage: MoveCell("myword")

Sub MoveCell(ByVal sSearchString As String)
Dim cell As Range
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function

The problem that you have is that the macro has an argument, sSearchString.

You could change it to pick up the text in the active cell, put myWord in
the cell and then run

Sub MoveCell()
Dim cell As Range
Set cell = FindCell(Activecell.Value, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
This code looks like what I want to accomplish. I thought I could put
it in as a module and then run it from the macro toolbar, but I'm
obviously wrong. Sorry I'm a noob. Where do I put this?

Usage: MoveCell("myword")

Sub MoveCell(ByVal sSearchString As String)
Dim cell As Range
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function

For that particular code, it is written to search for a cell that contains
the specified string. The problem is how to specify the string if you use a
button on a toolbar. If you were to use a button on a toolbar, you would
need to alter it to prompt for the word:


Sub MoveCell()
Dim sSearchString as String
Dim cell As Range
sSearchString = Inputbox("Enter string to search for:")
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Now you can add a button to the toolbar and assign this macro to it. Place
this macro and the function (FindCell) it calls in a general module (in the
VBE, do Insert=Module).

--
Regards,
Tom Ogilvy


" wrote:

This code looks like what I want to accomplish. I thought I could put
it in as a module and then run it from the macro toolbar, but I'm
obviously wrong. Sorry I'm a noob. Where do I put this?

Usage: MoveCell("myword")

Sub MoveCell(ByVal sSearchString As String)
Dim cell As Range
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function

MoveCell will not show up in the macros list because it requires an argument
(sSearchString) to be entered before it can be executed. If you want you can
change it to prompt for the search string at run time... something like
this...

Sub MoveCell()
Dim cell As Range
Dim sSearchString As String

sSearchString = InputBox("Please enter the search string.")
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell.Offset(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function

--
HTH...

Jim Thomlinson


" wrote:

This code looks like what I want to accomplish. I thought I could put
it in as a module and then run it from the macro toolbar, but I'm
obviously wrong. Sorry I'm a noob. Where do I put this?

Usage: MoveCell("myword")

Sub MoveCell(ByVal sSearchString As String)
Dim cell As Range
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function

Just to add to Bob's reply, I would suspect you would have a good probability
of finding the activecell, so you probably would want to alter findcell to
avoid that if you used this approach or change the search range not to
include the column or row where you enter the search string.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote:

The problem that you have is that the macro has an argument, sSearchString.

You could change it to pick up the text in the active cell, put myWord in
the cell and then run

Sub MoveCell()
Dim cell As Range
Set cell = FindCell(Activecell.Value, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
This code looks like what I want to accomplish. I thought I could put
it in as a module and then run it from the macro toolbar, but I'm
obviously wrong. Sorry I'm a noob. Where do I put this?

Usage: MoveCell("myword")

Sub MoveCell(ByVal sSearchString As String)
Dim cell As Range
Set cell = FindCell(sSearchString, Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell(0, -1).Value = cell.Value
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count),
_
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function