move cell

[scott]

below, I'm finding a cell if it contains string "myword", then i'm trying to
copy (buy I'd rather move the cell 1 cell to the right from where it's
found). I need help on the cell copy (preferably move) part. No matter what
variables I change in the OFFSET part, I can't get the contents in the found
cell to copy (preferably move) 1 cell to the right. My function FINDCELL
works good.

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

 

[scott]

When i use 0,1, the copy makes copy 1 cell above the cell containing my
string. Please try my function and sub out and see if you can make it copy
the dell containing a keyword and then make a copy in cell 1 cell to the
right.

This is driving me nuts.

 

[scott]

If you or anyone has a better way to find a cell containing a string and
move it 1 cell to the right, please enlighten me.

 

[Dave Peterson]

You're not using .offset(). You're using a short hand notation for .item().

I think I'd use .offset() explicitly:

cell.offset(0, 1).Value = cell.Value

(1 column to the right is +1, to the left is -1).

When you write cell(0,-1) that's the equivalent of:
cell.offset(-1,-2)
(one row up, two columns to the left)

cell.item(1,1) is the equivalent of cell.offset(0,0) or just cell.
(.item() is 1 based and .offset() is 0 based.)

Alan Beban has some notes on this form of addressing at Chip Pearson's site:
http://www.cpearson.com/excel/cells.htm

 

[scott]

thanks alot for that tip. excel vba gets confusing for me coming from a
database background.