copying formulas
Posted by jp on May 01, 2001 9:40 AM
This is probably a stupid question, but is it possible to place a formula into a spredsheet col from only the macroe. e.g.
------------------------------------
(something like this)
range("A2").select
activecell.formula = "=IF(SheetX!RC="""","""",left(SheetX!RC,FIND("":"",SheetX!RC)-1))"
columns("A:A").select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= False, Transpose:=False
'--- place col header ---
Range("A1").Select
ActiveCell.FormulaR1C1 = "Device"
-------------------------------------------
the data is on SheetX, the #rows are unknown, I need the new col on current sheet. The problem is that this is not a copy from one sheet/cell to another.
I told you this was a stupid question.... ;) ???
Posted by Dave Hawley on May 01, 2001 10:59 AM
Hi jp
Not a stupid question at all!
Rather than fill the whole Column with formulas, just go down as far as needed.
Sub TryThis()
Range("A2:A500").Formula = "=IF(SheetX!RC="""","""",left(SheetX!RC,FIND("":"",SheetX!RC)-1))"
'--- place col header ---
Range("A1") = "Device"
End Sub
Dave
OzGrid Business Applications
Posted by jp on May 01, 2001 1:05 PM
This works great, but I have another poser--- What determines the 'RC' (rowxcol?) value when the copy is carried out. Does it always have to be the same column as the one being copied from? If I put a Col/Row value in there, it puts ' marks around it, and it does not function correctly as a formula. I want to copy from specified columns on the main page-- sometimes the same col on the main page provides several cols on sheet1.. ??
Posted by Dave Hawley on May 01, 2001 1:47 PM
jp, Im not too sure i understand you but, this code will copy the formulas form Column 1 to Column 2 WITHOUT changing the reference:
Columns(2)=Column(1).Formula
The best way to work with cell formulas in VBA is to type it in a cell then select the cell and Record a Macro. Push F2 then push Enter, then modify the recorded code.
Dave
OzGrid Business Applications
