getting the value into a cell instead of the formula..

  • Thread starter Thread starter neeraja
  • Start date Start date
N

neeraja

Hi,

I try to copy a value to a different sheet in the
workbook through the below line

Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")

B5 contains the product number(this is some formula). In
A1 i get "#Ref!" instead of the product number. what do i
do to solve this..
Any help would be appreciated.

Thanks,
Neeraja.
 
Neeraja -
I try to copy a value to a different sheet in the workbook through the below line
Worksheets(Esheet1).Cells("B5").Copy _
Destination:=Worksheets("DummyData").Range("A1")
B5 contains the product number(this is some formula). In A1 i get "#Ref!"
Click to expand...
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton
 
Michael R Middleton said:
Neeraja -
"#Ref!"
instead of the product number. what do i do to solve this..

One way is to first Copy and then separately PasteSpecial with the
xlPasteValues option.

- Mike Middleton, www.usfca.edu/~middleton
Click to expand...

Out of interest, why does it not work if you insert the value property in
the above?

Worksheets(Esheet1).Cells("B5").Value.Copy etc
 
If you look at VBA's help for .copy, you'll notice that it applies to
objects--not simple variables.

dim myVal as string
myval.copy
fails. But if you copy a cell, it works ok.

Another way to "copy" the value from one cell to another is to just plop it in
there:

worksheets("dummydata").range("A1").value _
= worksheets("esheet1").range("b5").value

(watch out for that cells("b5") stuff, too. Either range("B5") or cells(5,"B")
or even cells(5,2) would be ok.)
 
Dave Peterson said:
If you look at VBA's help for .copy, you'll notice that it applies to
objects--not simple variables.
Click to expand...
Ah! Too subtle for me.

dim myVal as string
myval.copy
fails. But if you copy a cell, it works ok.

Another way to "copy" the value from one cell to another is to just plop it in
there:

worksheets("dummydata").range("A1").value _
= worksheets("esheet1").range("b5").value
Click to expand...
Ah! Just what I wanted, but was too close to the trees to see. I kept using
copy and pastespecial, which is clearly inferior.

Incidentally, this works too
Range("E3")= Range("D3")
Is this a case of Excel assuming (helpfully) that you mean the value
property when nothing is inserted?


Incidentally, the following works

Range("E5").Font.Bold= Range("E3").Font.Bold

I can see the logic of this. You are setting the Bold property of E5's font
object equal to the value of E3's. But how do you set all of the font
properties at the same time? I thought I could use Set to make one object
equal another, so what is wrong with:

set Range("E5").font= range("E3").font

I hope I'm not being too tiresome!

(watch out for that cells("b5") stuff, too. Either range("B5") or cells(5,"B")
or even cells(5,2) would be ok.)
Click to expand...

Noted, thanks.

Thanks very much

Geoff
 
Incidentally, this works too
Range("E3")= Range("D3")
Is this a case of Excel assuming (helpfully) that you mean the value
property when nothing is inserted?
Click to expand...

Yep. The default property for a range object is .value.
Incidentally, the following works

Range("E5").Font.Bold= Range("E3").Font.Bold

I can see the logic of this. You are setting the Bold property of E5's font
object equal to the value of E3's. But how do you set all of the font
properties at the same time? I thought I could use Set to make one object
equal another, so what is wrong with:

set Range("E5").font= range("E3").font
Click to expand...

You use Set with objects and Let (or nothing) with non-objects--simple variable
type thingys.

<<snipped>>
 
Back
Top