CODEWITHSUNDEEP
vbaexcel
Giovanni
Giovanni

Reputation: 838

VBA String without a space doesnt work, with a space does work

I am playing around with VBA in Excel, and cant for the life of me figure out why this error is popping up.

The following code works:

Cells(1, 1) = " =if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "

The following code doesnt work:

Cells(1, 1) = "=if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "

So Just by removing the space to make the line of code insert a formula rather than a string causes the compiler to through up error 1004.

In other lines ive used itll insert a formula no problem, but concatenating text with a local variable seems to throw this up. Any ideas?

Upvotes: 0

Views: 411

Answers (2)

Scott Craner
Scott Craner

Reputation: 152650

Excel will not allow an incorrect formula to be entered.

When putting a formula directly in the sheet you get the an error if it is not a correct formula and the cell remains in edit mode till either deleted or fixed.

The 1004 error is just vba representing that error.

To help find the error in the formula, place your string in a variable then debug.print it to see what is not correct:

t = "=if(counta('" & aLoc & "Subbies'!$A$2:$A$20)>0, ""YES"", ""NO"" )"
Debug.Print t

This will put the formula in the immediate window as it would in excel, allowing the developer to check to see if there is an error.

Bottom line:

You can put a string of what ever you want in a cell, but if you are trying to put a formula it must be complete and accurate.

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96781

Excel is complaining that you are trying to put a bad formula in a cell. If you want to put a "formula as text" in a cell, then:

Sub DontQuoteMe()
    Cells(1, 1) = "'=if(counta( " & aLoc & "Subbies'!$A$2:$A$20>0 ""YES"", ""NO"" "
End Sub

enter image description here

(using the space accomplishes the same thing)

Upvotes: 0

Related Questions