Cell Value Code

[truitt20]

Here is my problem: I have two columns as below


CCH7 Cocoa, Mar 07
P.CCH71450 Cocoa, Mar 07 1450 Put
P.CFF7105 Coffee, Jan 07 1050 Put
C.CFH7135 Coffee, Mar 07 1350 Call
CTH7 Cotton, Mar 07
CTH7 Cotton, Mar 07
P.CTH750 Cotton, Mar 07 50 Put
C.SUH712 Sugar World #11, Mar 07 1200 Call

Column A holds the "product identifier" and column B is a result of dde link. Column B1 cell, for example, is "=CQGPC|CCH7!LongDescription" As you can see, the formula does not reference the cell, but rather the product identifier (in this example "CCH7") in the formula. I want to be able to write code so that I do not have to write out the long formula every single time I add a new product. Any ideas on how to have formula reference the column A cells?

any help is much appreciated.

thanks

 

[SkipVought]

HI,

Like this maybe...
[tt]
=INDIRECT("CQGPC|"&A1&"!LongDescription")
[/tt]


Skip,

_[red][/red]

 

[truitt20]

Hmmm...tried that, but got the famed #REF! error

 

[pbrodsky]

or simply use VBA to insert the desired formulas in Col B

range("B2").formula = """" & range("A2").value & "!LongDescription

 

[SkipVought]

and what does
[tt]
="CQGPC|"&A1&"!LongDescription"
[/tt]
return?

Skip,

_[red][/red]

 

[truitt20]

thanks pbrosky! that works, but one other question:

How do i get a range to populate?

Range("B2:B100").Formula = "=CQGPC|" & Range("A2:A100").Value & "!LongDescription"?

that doesnt seem to work.

 

[pbrodsky]

something like:
Sub zit()
For i = 2 To 20
txt = "CQGPC|" & Cells(i, 1).Value & "!LongDescription"
Cells(i, 2).Formula = txt
Next i
End Sub