Copy And Paste Macro Syntax Problem

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

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,pfDisc,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,pfDisc,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
 
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
 
The CHR(34) did the trick, thanks

-Minitman

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
Click to expand...
 
Back
Top