Quote in equation causing error
 

Hi,

This corrects the formula line, note the double interior quotes

ActiveCell.Formula =
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SE ARCH(""."",$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" 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,SEAR CH(".",$C1,1)-1))"
Range("D1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

--
Thank you

Quote in equation causing error
 

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

--
Thank you

Quote in equation causing error
 

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:D" & Cells(Rows.Count, "C").End(xlUp).Row)
MyRange.Formula =
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SE ARCH(""."",$C1,1)-1))"


Mike
"Mike H" wrote:

Hi,

This corrects the formula line, note the double interior quotes

ActiveCell.Formula =
"=IF(ISERROR(SEARCH(""."",$C1,1)),$C1,MID($C1,1,SE ARCH(""."",$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" 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,SEAR CH(".",$C1,1)-1))"
Range("D1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

--
Thank you

Quote in equation causing error
 

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

Quote in equation causing error
 

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