Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How move arrow keys from cell to cell and NOT entire col/rows | New Users to Excel | |||
How do I move from cell A 10 to cell B1 with one move or click | Excel Worksheet Functions | |||
Arrow keys move screen instead of moving from cell to cell | Excel Discussion (Misc queries) | |||
How do I make my arrow buttons move from cell to cell in Excel? | Excel Discussion (Misc queries) | |||
Arrow keys move screen instead of moving from cell to cell. | Setting up and Configuration of Excel |