Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
I use Excel 2002 under XP Pro SP2.
Perhaps there are some memory limits, in particular to do with named expression/ranges -- which I use extensively -- that could be causing unexplained corruption in my formulas when I populate ranges via VBA. Does anyone know, for example, why row ranges in formulas might suddenly start showing up as Roman numerals, and relative references go kablooey (highly technical term, there)? :-) Here's a code snippet: ' data validation for Order # With .Range("B2", Cells(myRow, 2)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(B:B,B2)1" .FormatConditions(1).Interior.ColorIndex = 22 ' pink .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)" .FormatConditions(2).Interior.ColorIndex = 24 End With I run the macro. Things that should be pink aren't. I look in the conditonal formatting for the cells in question. I see, e.g., for cell B2: =COUNTIF(II:II,HI3)1 Can someone explain that?! I change that manually to =COUNTIF(B:B,B2)1 and the row's cells format as I expected and wanted. There are other relative-reference errors as well. I have a couple of fairly long named expressions, which I created with the free Add-In "Names Manger" (the current version). Could I be overrunning some program limit and corrupting memory or something? Any ideas would be most appreciated. A long related posting of mine from the other day that exlains some of the other errors is here in this group as Message-ID: , which I posted on the 17 July 2007. -- dman |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
Those aren't roman numerals, they're column ii (eye-eye) references.
The snippet you posted doesn't have any copy paste code in it, but my guess is somewhere along the line certain cells are pasted and the references shift accordingly. Some debugging will be required to figure this out: can you post the entire routine? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
In .com, Dave O
spake thusly: Those aren't roman numerals, they're column ii (eye-eye) references. The snippet you posted doesn't have any copy paste Hi, Dave, Well, while I appreciate your answer and willingness to help, I am left wondering about your citation. I Google on "column ii" and "eye-eye" with "reference" and "Excel" and come up with nothing useful or common that I can see. Moreover, how did they get turned on, and how do I turn them off? code in it, but my guess is somewhere along the line certain cells are pasted and the references shift accordingly. Some debugging will be required to figure this out: can you post the entire routine? Also, my code snippet does indeed populate Column B with the conditional-formatting formula I gave. (Or rather, a corrupt version of it.) I'm certainly willing to post the whole megillah, but I'm a bit skeptical that it will help shed much light here. Basically, relative references that my VBA code produce to populate cells (whether the cells themselves or conditional formatting formulas) no longer work right for me in this workbook. I can't even get OFFSET, ADDRESS, or INDIRECT to work in their stead anymore in this VBA. It comes out shifted or wrong. Again, the only explanation I can conceive of is memory corruption, as in I've probably exceeded some upper bound somewhere and am causing the VBA to overwrite itself or something. I'll put the whole thing on a web page and leave it up for a couple of weeks at least. Okay, the macro is at http://heliotropos.com/xl/tmp/deBruinMerge.txt If you search on ' data validation or even just "validation" in there, you'll find the snippet I posted in the previous message. The worksheet the VBA produces looks like this example: http://heliotropos.com/xl/tmp/OpenOrders.jpg How long may "name" formulas in Excel 2002 be, anyway? There is one long one in particular that, when I look at it in Names Manager, only shows up in part. I'm pretty sure it's longer than 256 chars, for example. I'm concerned that I can't see the end of the expression. This leads to my speculation above about corruption of memory addresses. Any help resolving this mystery will be most appreciated! Dallman ======================== [My previous message in pertinent part:] Does anyone know, for example, why row ranges in formulas might suddenly start showing up as Roman numerals, and relative references go kablooey (highly technical term, there)? :-) Here's a code snippet: ' data validation for Order # With .Range("B2", Cells(myRow, 2)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(B:B,B2)1" .FormatConditions(1).Interior.ColorIndex = 22 ' pink .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)" .FormatConditions(2).Interior.ColorIndex = 24 End With I run the macro. Things that should be pink aren't. I look in the conditonal formatting for the cells in question. I see, e.g., for cell B2: =COUNTIF(II:II,HI3)1 Can someone explain that?! I change that manually to =COUNTIF(B:B,B2)1 and the row's cells format as I expected and wanted. There are other relative-reference errors as well. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
In , Dallman Ross <dman@localhost.
spake thusly: In .com, Dave O spake thusly: my guess is somewhere along the line certain cells are pasted and the references shift accordingly. Some debugging will be required to figure this out: can you post the entire routine? Basically, relative references that my VBA code produce to populate cells (whether the cells themselves or conditional formatting formulas) no longer work right for me in this workbook. [. . .] I've tested things more by removing the long named expressions from the Names Manager and performing a couple of other tests. That was not the problem. The references shift each time the code is run. If I close the book and re-open and run again, it moves back to the beginning. But the strange "II:II" references (that you called "eye-eye") are still there, and the conditonal-format formulas won't work until I change those to B:B (manually). And when I run the code again the relative references shift out of sync, and I get a dozen or so extra blank columns on the right end of the sheet. (See "kludge" in my code.) Maybe where I have "clear" (for the cells on the sheet) I will need to delete. But then my named expressions lose their references and have them replaced by error messages. I'll put the whole thing on a web page and leave it up for a couple of weeks at least. Okay, the macro is at http://heliotropos.com/xl/tmp/deBruinMerge.txt If you search on ' data validation or even just "validation" in there, you'll find the snippet I posted in the previous message. The worksheet the VBA produces looks like this example: http://heliotropos.com/xl/tmp/OpenOrders.jpg Dallman |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
In .com, Dave O
spake thusly: Those aren't roman numerals, they're column ii (eye-eye) references. Dave, can you clarify further about that? I can't find anything useful via the usual search engines on the web for "eye-eye." In any case, they cause my formatting formula not to work. Anyway, though, here is a happy update: I solved my problem. I changed the code simply by making the column references absolute rather than relative, and the "II:II" stuff went away and "$B:$B" showed up and my conditonal formats are now working. I can't explain it, though, and I'd sure like to understand it. Again, here was the code. "myRow" is the last row in the column: ' data validation for Order # With .Range("B2", Cells(myRow, 2)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(B:B,B2)1" .FormatConditions(1).Interior.ColorIndex = 22 ' watermelon-pink .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)" .FormatConditions(2).Interior.ColorIndex = 24 End With All I changed is, "=COUNTIF($B:$B,$B2)1" and now it works. I had written: I run the macro. Things that should be pink aren't. I look in the conditonal formatting for the cells in question. I see, e.g., for cell B2: =COUNTIF(II:II,HI3)1 Can someone explain that?! [snip] Oh, and that "3" instead of "2" was no typo, either. But now it says 2, since I switched to absolute column references. I can't explain it! -- dman |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
In , Dallman Ross <dman@localhost.
spake thusly: In .com, Dave O spake thusly: Those aren't roman numerals, they're column ii (eye-eye) references. Dave, can you clarify further about that? I can't find anything useful via the usual search engines on the web for "eye-eye." In any case, they cause my formatting formula not to work. Following up here to see if Dave is reading this and might still answer this old question. -- dman |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inexplicable VBA errors in formulas
I really can't tell what you are talking about because there is no clue in
the post. Might have been for i =1 to 21 for ii=3 to 81 could have just as easily been for dman=3 to 81 or whatever you want to call it. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dallman Ross" <dman@localhost. wrote in message ... In , Dallman Ross <dman@localhost. spake thusly: In .com, Dave O spake thusly: Those aren't roman numerals, they're column ii (eye-eye) references. Dave, can you clarify further about that? I can't find anything useful via the usual search engines on the web for "eye-eye." In any case, they cause my formatting formula not to work. Following up here to see if Dave is reading this and might still answer this old question. -- dman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I correct rounding errors in Excel formulas? | Excel Worksheet Functions | |||
Nested IF AND OR function Inexplicable error | Excel Worksheet Functions | |||
Handling errors in formulas (how annoying are they!) | Excel Discussion (Misc queries) | |||
Errors in copying formulas | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |