I think I broke the GoTo function?

[cmore]

Hi,

I'm not sure how, but my macro seems to be jacking up the GoTo function some how. A1 won't go anywhere and then any other lookup or cell reference appears to be doing some kind of offset.

I have a pretty big interconnected workbook, I cannot disentangle easily and cannot post publicly, but the functions / methods I'm using that could potentially cause issue are:

Application.Convertformula - converting A1 formulas to R1C1 in order to push into the Name manager

I use the EVALUATE function in name manager and I have a SUMIFS that cuts accross ranges that would otherwise cause a circular reference

Please let me know if you have any thoughts?

[shg]

That whole post is pretty cryptic, cmore.

[cmore]

Sorry sir. Not sure how to explain further. So for some reason. I run this code. It runs fine, but the F5 and GoTo are jacked up. If I hit A1 it goes nowhere and A14 it offsets 14 cells downward. It's assuming, Some how the cell I reference in Application.Convertformula has reset as the reference of A1 for the GoTo function.

If I type A1 in the 'NameBox' in the top left corner though, it behaves as expected

[shg]

Post an example of the code and data that sends the selection to the wrong place, cmore.

[cmore]

Sorry sir, so it's not that straightforward. I use Covertformula several times and it's a lot of code not sure when/why it's Excel is being thrown off. Here's an example of the Convertformula?

Please Login or Register  to view this content.

[cmore]

This formula is basically a dynamic named range based on the location of the cell with a range.

[cmore]

So when I run the code now, somewhere in there the "GoTo" is broken, if I close and reopen it works as intended

[scottiex]

what do you mean by "cannot disentangle easily"?
All we would need is a simplified worksheet that still exhibits the problem. So you might start by breaking all the external links and deleting sheets and deleting data and modules as long as they don't fix the problem. It sounds like you even know the macro that breaks it...

[cmore]

So the code is a workbook that writes named ranges, formulas and creates sheets based on an input sheet. So it's not something where I can just show a piece of it and things make any kind of useful sense. You're right, I cannot really tell where exactly the GoTo goes haywire.

The only thing that would make sense to me is that the Application.Convertformula is throwing things off somehow or maybe (longshot) because I'm using the EVALUATE function in the named range and excel doesn't like it used too much as there's some weird behaviour that occurs when I use it in conjunction with the Index Function.

When I step through the code though I don't see the instance where this behaviour is initiated. So I guess I'm wondering if anyone has seen this kind of strangeness before with the GoTo?

[cmore]

I cannot say I've solved the problem, but it's not an issue anymore. What I've done is eliminate any redundant uses of Application.Convertformula, so there were instances where I used it to write formulas to the sheet or array formulas. Instead I just used the .Formula property.