move cell
 

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

move cell
 

I don't see too much wrong in here except that you never cleatr the contents
of Cell, and you are moving to the left instead of to the right.

cell(0,1).value = cell.value
cell.value = ""

In terms of moving or copying in the world of computers moving is just a
copy followed by a delete, so ther is no advantage in code to moving as
opposed to copy and delete.

HTH

"scott" wrote:

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

move cell
 

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.


"Jim Thomlinson" wrote in message
...
I don't see too much wrong in here except that you never cleatr the
contents
of Cell, and you are moving to the left instead of to the right.

cell(0,1).value = cell.value
cell.value = ""

In terms of moving or copying in the world of computers moving is just a
copy followed by a delete, so ther is no advantage in code to moving as
opposed to copy and delete.

HTH

"scott" wrote:

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

move cell
 

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.

"Jim Thomlinson" wrote in message
...
I don't see too much wrong in here except that you never cleatr the
contents
of Cell, and you are moving to the left instead of to the right.

cell(0,1).value = cell.value
cell.value = ""

In terms of moving or copying in the world of computers moving is just a
copy followed by a delete, so ther is no advantage in code to moving as
opposed to copy and delete.

HTH

"scott" wrote:

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

move cell
 

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

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

--

Dave Peterson

move cell
 

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


"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson