I will give both a try and thank you both for your input, very helpfull.....
--



"Gord Dibben" wrote:

Double up the internal quotes.

Range("D1").Select
ActiveCell.Formula = _
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SE ARCH(""."",$C1,1)-1))"
Range("D1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

To get rid of selects, copy's and pastes, try this version which fills D1
down to last used row in column C

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("C" & Rows.Count).End(xlUp).Row
Range("D1").Formula = _
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SE ARCH(""."",$C1,1)-1))"
.Range("D1:D" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 12:21:42 -0800, WT wrote:

I really appreciate your help!!
I am trying to replicate this formula down the entire column in a macro.
However I continually get a compile error when it runs into the equation,
particularly the "period" ("."). How do you get quotes into a statement to
paste into a cell??

this is the code I am useing:

Range("D1").Select
ActiveCell.Formula =
"=IF(ISERROR(SEARCH(".",$C1,1)),$C1,MID($C1,1,SEA RCH(".",$C1,1)-1))"
Range("D1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False