PDA

View Full Version : Solved: Copy information to an Activecell that Moves



kisinana
07-06-2008, 08:48 AM
I am trying to move information from a cell on a page that collects the average score of a group of golfers and then transfers it to the cell to the right of Casper which is able to move up or down a column depending on the number of players involved.
Here is what I have


Range("O4").Select
Selection.Copy
Cells.Find(What:="Casper", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 1).Value = "O4"

Each time I run the macro it returns 0 even though "O4"
has the correct calculation

mikerickson
07-06-2008, 08:57 AM
That code is putting the string "O4" next to Casper.
Try
Range("O4").Copy Destination:=Cells.Find(What:="Casper", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)

kisinana
07-07-2008, 12:03 AM
I changed out the code and entered your suggestion and I am still getting a "0" as the final value



Range("O4").Copy Destination:=Cells.Find(What:="Casper", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)

My next line of code I thought would be

Activesheet.Paste
To place the value in the cell to the right of "Casper"
but as I say it enters a "0"
Any suggestions I could try?

Bob Phillips
07-07-2008, 01:06 AM
You do not need the activesheet.paste, the previous bit of code copies as it finds the match. Of course, it does assume it will find a match.

The first bit of code is all that you need.

mikerickson
07-07-2008, 05:51 AM
If the cell containing Casper and the O4 you want copied are on different sheets, you'll need to add sheet names to the code.

kisinana
07-07-2008, 07:18 AM
All the cells are on the same sheet.
I think I see my problem now, because "O4" is the product of a calculation (it has the formula =sum(N3) in the cell).
So when the code takes this value it also takes the formula? If that is correct, then I just need to be able to copy the cell (N3) to (O4) without transferring the formula and still have it update when the information in (N3) changes?

I tried linking the cells and I get (N3 and O4) a Circular Reference when the macro is run.

Thanks in advance to the guys who have made suggestions.
This started as a quick spreadsheet and the macros are just to make it simpler for a couple of non computer guys to use.

Bob Phillips
07-07-2008, 08:01 AM
Dim rng As Range

Set rng = Cells.Find(What:="Casper", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Range("O4").Copy
rng.Offset(0, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If

kisinana
07-07-2008, 08:16 AM
Thanks for the quick reply xld.
I leave in an hour for the week and will have my laptop but no internet connection.
Will look at trying your coding and let you know when I return.
Thanks in advance

kisinana
07-12-2008, 11:44 PM
Thanks xld
It did what I was looking for.