| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What's causing error message? | Excel Worksheet Functions | |||
| What's causing error message? | Excel Programming | |||
| Single quote in filename causing problems | Excel Programming | |||
| #NAME? error with MSN stock quote function | Excel Discussion (Misc queries) | |||
| VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
Hybrid Mode
