rossmiddleto
12-16-2010, 06:12 AM
Hi Everyone,
I am looking to do the following.
1) Find a cell called 'test' in sheet2 of my workbook.
2) Cut the value in the cell to the right of the 'test' cell
3 Find a cell called 'test' in sheet1
4) Finally, paste the value found in step2 to the cell to the right of the cell found in step3.
My code is below, any help will be much appreciated!
Kind Regards
Ross
Option Explicit
Sub Arrange_Cells()
Dim sourcecell As Range, sourcecell2 As Range, sourcecell3 As Range, destinationcell As Range, destinationcell2 As Range, sourcerange As Range
Dim matchrow As Integer, matchrow2 As Integer
Set sourcecell = Cells.Find(What:="Test*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set sourcerange = sourcecell.Offset(0, 2) 'Selects cell in same Row, 2 Columns RIGHT
sourcerange.Cut
Set destinationcell = Cells.Find(What:="Test*", After:=Worksheets(1).Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set destinationcell2 = destinationcell.Offset(0, 2)
Set sourcecell3 = sourcecell2
sourcecell3.Paste Destination:=Worksheets(1).Range(destinationcell2)
Set sourcecell = Nothing
End Sub
I am looking to do the following.
1) Find a cell called 'test' in sheet2 of my workbook.
2) Cut the value in the cell to the right of the 'test' cell
3 Find a cell called 'test' in sheet1
4) Finally, paste the value found in step2 to the cell to the right of the cell found in step3.
My code is below, any help will be much appreciated!
Kind Regards
Ross
Option Explicit
Sub Arrange_Cells()
Dim sourcecell As Range, sourcecell2 As Range, sourcecell3 As Range, destinationcell As Range, destinationcell2 As Range, sourcerange As Range
Dim matchrow As Integer, matchrow2 As Integer
Set sourcecell = Cells.Find(What:="Test*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set sourcerange = sourcecell.Offset(0, 2) 'Selects cell in same Row, 2 Columns RIGHT
sourcerange.Cut
Set destinationcell = Cells.Find(What:="Test*", After:=Worksheets(1).Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set destinationcell2 = destinationcell.Offset(0, 2)
Set sourcecell3 = sourcecell2
sourcecell3.Paste Destination:=Worksheets(1).Range(destinationcell2)
Set sourcecell = Nothing
End Sub