PDA

View Full Version : Solved: Good Formulas producing Error Messages



Wizard
08-17-2006, 08:30 AM
I have run into a very frustrating situation where I am using code to enter some simple formulas, and I keep getting a #NAME! error.

A representative line of code would be:
Range([b2], Cells(Range("A65536").End(xlUp).Row, "b")).FormulaR1C1 = _
"=VLOOKUP(RC1, Detail!B:Z, 13, False)"

& the results are #NAME! through the range, but if I select any of those cells and edit it (just hit F2, then Enter), the formula works fine (in that cell only).:banghead:

I've tried working in a smaller, standalone spreadsheet, thinking that memory might have been a problem on my Big Main Spreadsheet O' Fun, but that didn't do it, several variations on the formula - or even simpler formulae on the same sheet - and it doesn't make a bit of difference.

Anybody have a clue?

matthewspatrick
08-17-2006, 08:53 AM
Just a guess: you are using the FormulaR1C1 property, but then you mix R1C1 and A1 cell references in the formula. You probably should not do that :)

Norie
08-17-2006, 08:55 AM
The problem is probably because you are mixing R1C1 and A1 notation in the formula.

When you do that you get some strange results.

Try this.

Range([b2], Cells(Range("A65536").End(xlUp).Row, "b")).Formula = _
"=VLOOKUP(A2, Detail!B:Z, 13, False)"

Wizard
08-17-2006, 01:13 PM
Your suggestion would have worked, Norie, but I needed to use the R1C1 notation because the actual formula I wanted to use was going to be in many columns, and it needed to calculate the column number to retrieve based on the header the formula was in. I ended up doing a macro recording while I entered the formula & ended up with this
Range([B2], Cells(Range("A65536").End(xlUp).Row, "C")).FormulaR1C1 = _
"=VLOOKUP(RC1,Detail!C2:C25,MATCH(R1C,Detail!R1,0)-1,FALSE)"

The simplest things trip you up, even after 10 years of doing this crap...

Thanks - Marking solved.

Norie
08-17-2006, 01:19 PM
Wizard

The code I posted would probably have adjusted the references as required.

You might have had to anchor a column reference or something though, but it wasn't actually clear from your original post that you were working with multiple columns.

I would seriously suggest you avoid mixing A1 and R1C1, the results are just too unpredictable.

Sometimes they work, sometimes they don't and give errors like you encountered.

Stick to one or the other.

Zack Barresse
08-17-2006, 01:32 PM
Please note there is a LARGE difference between VBA R1C1 [formula] notation and your workbook being in R1C1 reference style. Since you are putting text as the formula (it will eventually be recognized when the calculation is performed), those references need to be in whatever reference style your workbook is utilizing. When VBA compiles your code, the VBA references should be in R1C1 if states as such.

And you're mixing shorthand notation. Best just to keep everything in ONE reference style.

Range("B2:B & Cells(Rows.Count, 1).End(xlUp).Row).Formula = _
"=VLOOKUP(A2, Detail!B:Z, 13, False)"


I would seriously suggest you avoid mixing A1 and R1C1, the results are just too unpredictable.
I think they are pretty predictable, actually.


I ended up doing a macro recording while I entered the formula & ended up with this..I find that hard to believe with the mixed notation types you have there. Looks more like a recorded macro with some changes/copy/pastes to me..

Norie
08-17-2006, 01:44 PM
Zack

I don't think the results are predictable.

I've seen code that worked where A1 and R1C1 notation was mixed up that worked.

I've also seen code where it didn't work.

In the OP's example I couldn't work out why they where getting the #NAME! error.

In the formula bar everything look fine and when you looked at it in the formula bar and then pressed enter you got #NA! errors rather than #NAME! errors.

Zack Barresse
08-17-2006, 02:21 PM
Well, looking at code, you should be able to - if you know what you're looking at/for - tell if it will work or not.

Norie
08-17-2006, 10:21 PM
Zack

What are you talking about?

Zack Barresse
08-18-2006, 07:47 AM
It's pretty painlessly obvious to me. We know there is a difference between VBA R1C1 [formula] reference and workbook R1C1 reference style. I'm saying that by looking at VBA code, you can tell if the code will fail or not. This was in direct response to your comment ...


I would seriously suggest you avoid mixing A1 and R1C1, the results are just too unpredictable.

Sometimes they work, sometimes they don't and give errors like you encountered.

..

I don't think the results are predictable.

I've seen code that worked where A1 and R1C1 notation was mixed up that worked.

I've also seen code where it didn't work.

As I've said, you CAN mix the two - if you do it correctly! If not you'll meet failure, as you saw yesterday. Make sense?

Norie
08-18-2006, 07:54 AM
Zack

I agree you can sometimes just look at code and see what the error is but not always.:)

Zack Barresse
08-18-2006, 08:04 AM
I think you're mistaken due to the fact that you can't say that about all people's skill levels. I think there are plenty of people out there who can tell you if code will work, what errors you'll receive, how your code could be more/less efficient, etc.

But in the end, my whole point is, we can use both reference styles because they are not related to one another, one is workbook and the other is VBA method, two very distinct differences.

Bob Phillips
08-18-2006, 08:20 AM
I think you're mistaken due to the fact that you can't say that about all people's skill levels. I think there are plenty of people out there who can tell you if code will work, what errors you'll receive, how your code could be more/less efficient, etc.

But in the end, my whole point is, we can use both reference styles because they are not related to one another, one is workbook and the other is VBA method, two very distinct differences.

Sorry to butt in lads, but it seems to me that Norie is saying that he has seen a mixture of reference styles that did woirk, and others that didn't, and there was nothing discernible about them to suggest that one would and one wouldn't. As he (Norie) seems quite experienced, I don't think we can question that. We may be dubious, but it is his experience.

Zack Barresse
08-18-2006, 08:28 AM
Yes, quite agreed that this may be his experience. :yes