Excel VBA inserting unwanted quote marks!

[CWalsh87]

Hi,

I need to insert a function into cells using VBA. For various reasons i need a variable in the function so i've used the following code:

For a = 1 to 10

activesheet.range("D1100").select
Selection.FormulaR1C1 = "=IF(ISERROR(SEARCH('Product Type'!D" & a & ",rc[1],1)),"""",'Product Type'!D" & a &")"

Next a

The forumula is inserted into the correct cells, but reads as follows:

=IF(ISERROR(SEARCH('Product Type'!'D10',F6,1)),"",'Product Type'!'D10')

As you can see the D10 has extra ' marks placed around it, therefore returning #NAME? error.

Has anyone any ideas??

Cheers
Chris

 

[CWalsh87]

I've narrowed the problem down to the fact that i'm using R1C1 notation.

But i still don't know how i can stop the extra ' marks getting inserted

 

[bhujanga]

That's the exact problem I wrote in about at almost the exact time you wrote in. If there's any way you can construct your string to not use relative cell references then you can use ".formula" (without the R1C1). That's what I did.

 

[SkipVought]

for a = 1 to 10
  with Sheets("Product Type")
    activesheet.cells(a, "D").formula = "=IF(ISERROR(SEARCH('Product Type'!" & .cells(a, "D").address(false, false)& ",F6,1)),"""",'Product Type'!" & .cells(a, "D").address(false, false)& ")"
  end with
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]