Cannot jump to Range because it is hidden

Thanks for your quick response, but if I try the code, it doesn't give me an error message anymore... but it also doesn't work for what I needed.

I need all the text in columns A from 2 worksheets converted to a number. I still have little green icons in all cells showing that it's text.

Have you run into this same error message before, and do you know how can you bypass it or "unhide" this range?

If I just play around with my code, and alternate the "YTD" worksheet with the "LW" worksheet the code works and converts all my text in columns A to number.

Could it be worksheet activation issues or some sort of order of operation that was out of place, perhaps?

Hi xladept,

Ill try to attach an example tomorrow. I got the code to work towards my needs for now (I still need to validate, for sure). I was also quite stumped that I was able to get it to work with such a strange swap of code...


regards,
quailkatie

Hi xladept,

I have attached an example here with 2 tabs: SDP LW and SDP YTD.
I am running into this similar error message all over my code now, and its driving me insane. I've tried the object thingy that pops up and clicking "show hidden items", but that doesn't work either.
I'm so frustrated. It was going so well with my first attempt at writing macros...
I'm worried that there might be something with the data source that's causing a hang up (its an Excel add-in that extracts query data from SAP).

Hi xladept,

Ill take a look at it and see if this helps. But, I have the similar "cannot jump to range or worksheet or whatever because it is hidden" message for other parts in my Macros. And it seems like they crop up randomly. Where is this even coming from? Did you get the same error message with the data/code I attached? I think I need to try to understand where these error messages are coming from, otherwise my codes will never work. Im afraid its because SAP data absolutely sucks. :,(

quailkatie

I think I figured out the issue: I have this "cannot jump to range" error message because of result lines or blank lines. It seems Excel gets hung up on these blanks even though they are not selected due to filtering. I'm unable to find any solution to the problem, however.

Is there any way to ONLY re-format the selected cells from text to number?

I tried filtering, and then the same code for the selection of a range (i.e. E4:E15000) but then my cells end up getting deleted or new values end up in random places in the worksheets.

A way for converting the numbers to text would be kind of nice. You think it would be easy! It sucks having to do it manually on multiple sheets every.single.time.

quailkatie

The code half-works as is. It works for one worksheet but not the other. It also halts the rest of my code from working...

I'll have to see if I can adapt it to get it to work with the rest of my macro, but at least I don't have the stupid code randomly deleting and making up new material numbers. No one wants that, lol.

Thanks for the help.

Hello,

I am running into the same error (Cannot jump to range because its hidden). I am trying to autofill a vlookup to the last row of data. It works for column "E", but it does not work for column "F". The Debug points to the last line of code below: Range("F2").AutoFill Destination:=Range("F2:F" & LastRow).

Please help!

ChDir "I:\IDEA\Volume\Biweekly\Service Item Activity Template"
Workbooks.Open Filename:= _
"I:\IDEA\Volume\Biweekly\Service Item Activity Template\Service Item Activity Log.xlsx"
Windows("Service Item Errors Template.xlsm").Activate
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Service Item Activity Log.xlsx]Sheet1'!C4:C5,2,0)"
LastRow = Range("E" & Rows.Count).End(xlUp).Row
Range("E2").AutoFill Destination:=Range("E2:E" & LastRow)
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[Service Item Activity Log.xlsx]Sheet1'!C4:C6,3,0)"
LastRow = Range("F" & Rows.Count).End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)