Quote in equation causing error

[Mike H]

Hi,

This corrects the formula line, note the double interior quotes

ActiveCell.Formula =
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SEARCH(""."",$C1,1)-1))"

But what happens next bit of your code doesn't work. If you explain what
your trying to do someone will help

Mike

 

[WT]

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,SEARCH(".",$C1,1)-1))"
Range("D1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

 

[Mike H]

Hi,

Looking a bit more closely are you trying ti fill down as far as there are
data in column C?

Dim MyRange As Range
Set MyRange = Range("D1" & Cells(Rows.Count, "C").End(xlUp).Row)
MyRange.Formula =
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SEARCH(""."",$C1,1)-1))"


Mike

 

[Gord Dibben]

Double up the internal quotes.

Range("D1").Select
ActiveCell.Formula = _
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SEARCH(""."",$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,SEARCH(""."",$C1,1)-1))"
.Range("D1" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

 

[WT]

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