MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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