simple cell copy using VBA

[Bob Taylor]

I get the error #VALUE! as a result of the following VB code Cells(2, 2) =
Cells(2, 25).

according to the examples and the books this should work and is very easy,
but i can not make it work, the cell types are both GENERAL and the
contents are strings. i have tried string to string and using ranges and
everything else that i can find and nothing allows me to move the contents
of one cell to another. it has to be a simple answer.

 

[Guest]

Give this a try.
Range("B2").value = Range("B25").Value

Devin

 

[R.VENKATARAMAN]

where is the entry
in your case try
cells(2,25)=cells(2,2)
does it help?
cells(2,2) has already data and cells(2,25) is blank
so you configure that cells(2,25) willhave the value of cells(2,2)

 

[Bob Taylor]

Devin
thanks for the info.. i had tried this and it failed. i recode it as
Function MV()
On Error GoTo bob
Range("B3").ClearContents
Range("B3").Value = Range("Y3").Value
'Cells(2, 2) = Cells(2, 25)
bob:
' MsgBox " cannot store in old values"
End Function
it fails again. when i run with a stop on, and watch the cells it fails on
first pass. however if i step into it again it works. i don't understand
what is going on.

bob

 

[Jim May]

Bob:
You have written a procedure and enclosed it with FUNCTION() ' a
different breed of cat.. Replace (in a Standard module) with:

Sub MV()
Range("B2").ClearContents ' or cells(2,2).ClearContents
Cells(2, 2) = Cells(2, 25)
End Sub

HTH

 

[Bob Taylor]

Jim
my understanding of the difference between a function and a subprocedures is
that the function returns a value and the sub doe not.

i recoded the function to a sub and as i step through the sub, the
range.clearcontents does not clear the content and then the range().value =
range().value does not copy the data.

the assignment statement should work in either wrappers without error.
i am still looking for the root of the problem

thanks
bob

 

[Bob Taylor]

it does not make any difference.

thanks though

where is the entry
in your case try
cells(2,25)=cells(2,2)
does it help?
cells(2,2) has already data and cells(2,25) is blank
so you configure that cells(2,25) willhave the value of cells(2,2)

 

[Bob Taylor]

i just put an on error handler in and found Err.Number = 1004. where do you
find out what the 1004 really means.

thanks

 

[Tom Ogilvy]

A function called from a worksheet cell can not change the excel environment
or change the value of other cells. It can only return a value to the cell
in which it is located (just like builtin functions).

 

[Bob Taylor]

Tom
is there any way of changing other cell value. this is explaining to me
what the root of the problem is

thanks
bob

 

[Tom Ogilvy]

No. It isn't allowed by Excel.

You might want to use the Change, SelectionChange or Calculate events to
accomplish your purpose.

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events

 

[Tom Ogilvy]

1004 is a general error. More important is the descriiption provided with
the 1004 error. But as stated, what you are trying to do is not allowed in
a User defined function used in a worksheet.