wher to put the code

[sonny.vinyard]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.