Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy And Paste Macro Syntax Problem
Greetings,
I found a solution to my copy and paste problem in the archives. the problem is that I need to paste an empty cell as a choice in my IF statement. Here is the formula in the cell called pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,"",OFFSET(clStart,p fDisc,1)) After I run the macro to overwrite the contents of the 79 named ranges, this is what is returned in pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,,OFFSET(clStart,pfD isc,1)) Notice the missing "" marks. Here is the macro that changes all 79 named ranges at one time: Sub RecoverFormulas() Dim i As Integer With ActiveSheet For i = 1 To 79 If Not i = 26 Then .Range("pfCell_" & i).Formula = _ "=IF(OFFSET(clStart,pfDisc," & i & _ ")=0,,OFFSET(clStart,pfDisc," & i & "))" Next i End With End Sub When I tried to put "" between the ,,'s, debug kicked in and stopped the macro with an error message - it did not like the ""'s !!! How can This be rewritten so the I get empty named range if the result is 0??? Any ideas, suggestions or thought on this matter? Any help is appreciated. -Minitman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy And Paste Macro Syntax Problem
Minitman,
You need to return double qotes inside of a string (a string is surrounded by double quotes). So when you insert 2 double quotes between the commas...well I'm not quite sure what it is trying to do. To return double quotes inside a string, you need to double, and sometimes triple or quadruple your double quotes, depending on how many you need and where you need them. In your case, you might try 4 quotes (""""). But I find the whole thing terribly confusing. What I normally do is concatenate the code for the double quotes ("CHR(34)"). So, something like the following might work (I removed all line continuations (" _"), but OE may have wrapped the line...make sure to paste in as one line and add line continuations where you like): If Not i = 26 Then .Range("pfCell_" & i).Formula = "=IF(OFFSET(clStart,pfDisc," & i & ")=0," & CHR(34) & CHR(34) & ",OFFSET(clStart,pfDisc," & i & "))" HTH, Conan "Minitman" wrote in message ... Greetings, I found a solution to my copy and paste problem in the archives. the problem is that I need to paste an empty cell as a choice in my IF statement. Here is the formula in the cell called pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,"",OFFSET(clStart,p fDisc,1)) After I run the macro to overwrite the contents of the 79 named ranges, this is what is returned in pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,,OFFSET(clStart,pfD isc,1)) Notice the missing "" marks. Here is the macro that changes all 79 named ranges at one time: Sub RecoverFormulas() Dim i As Integer With ActiveSheet For i = 1 To 79 If Not i = 26 Then .Range("pfCell_" & i).Formula = _ "=IF(OFFSET(clStart,pfDisc," & i & _ ")=0,,OFFSET(clStart,pfDisc," & i & "))" Next i End With End Sub When I tried to put "" between the ,,'s, debug kicked in and stopped the macro with an error message - it did not like the ""'s !!! How can This be rewritten so the I get empty named range if the result is 0??? Any ideas, suggestions or thought on this matter? Any help is appreciated. -Minitman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy And Paste Macro Syntax Problem
The CHR(34) did the trick, thanks
-Minitman On Wed, 26 Mar 2008 19:02:03 GMT, "Conan Kelly" wrote: Minitman, You need to return double qotes inside of a string (a string is surrounded by double quotes). So when you insert 2 double quotes between the commas...well I'm not quite sure what it is trying to do. To return double quotes inside a string, you need to double, and sometimes triple or quadruple your double quotes, depending on how many you need and where you need them. In your case, you might try 4 quotes (""""). But I find the whole thing terribly confusing. What I normally do is concatenate the code for the double quotes ("CHR(34)"). So, something like the following might work (I removed all line continuations (" _"), but OE may have wrapped the line...make sure to paste in as one line and add line continuations where you like): If Not i = 26 Then .Range("pfCell_" & i).Formula = "=IF(OFFSET(clStart,pfDisc," & i & ")=0," & CHR(34) & CHR(34) & ",OFFSET(clStart,pfDisc," & i & "))" HTH, Conan "Minitman" wrote in message .. . Greetings, I found a solution to my copy and paste problem in the archives. the problem is that I need to paste an empty cell as a choice in my IF statement. Here is the formula in the cell called pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,"",OFFSET(clStart,p fDisc,1)) After I run the macro to overwrite the contents of the 79 named ranges, this is what is returned in pfCell_1: =IF(OFFSET(clStart,pfDisc,1)=0,,OFFSET(clStart,pfD isc,1)) Notice the missing "" marks. Here is the macro that changes all 79 named ranges at one time: Sub RecoverFormulas() Dim i As Integer With ActiveSheet For i = 1 To 79 If Not i = 26 Then .Range("pfCell_" & i).Formula = _ "=IF(OFFSET(clStart,pfDisc," & i & _ ")=0,,OFFSET(clStart,pfDisc," & i & "))" Next i End With End Sub When I tried to put "" between the ,,'s, debug kicked in and stopped the macro with an error message - it did not like the ""'s !!! How can This be rewritten so the I get empty named range if the result is 0??? Any ideas, suggestions or thought on this matter? Any help is appreciated. -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste problem | Excel Discussion (Misc queries) | |||
Copy/ Paste Problem | Setting up and Configuration of Excel | |||
Copy/Paste Problem | Excel Worksheet Functions | |||
Copy and Paste Problem | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |