Column number reference using cell reference

**Fatrobo** · 06-02-2015, 10:47 PM

Hi all,

I have created a basic macro which hides columns before and after a specified range. I have calculated the column numbers from a user specified date in excel and want to use these cell references in my VBA code to hide the columns.

For example:

Cell B9's formular tells me the users date starts at column 37.
Cell B10's formular tells me the users date ends at column 42.

Becuase these dates change I need to reference cells B9 and B10 and not the column numbers 37 and 42.

So I want to hide colomns 4:B9 and B10:55.

My code keeps failing with error "type mismatch".

Please Login or Register  to view this content.

Any help would be great!

**FDibbins** · 06-02-2015, 10:54 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have added the for you - this time

**Fatrobo** · 06-02-2015, 11:03 PM

My Apologies. Thanks for your help.

**FDibbins** · 06-02-2015, 11:15 PM

No problem, if you dont get told, you wont know

**Fatrobo** · 06-04-2015, 09:21 PM

Bump. Does anyone have any thoughts?

**Fatrobo** · 06-15-2015, 06:53 PM

So I have tried another approach with this question. I have attached the spreadsheet in question. In the 2015 tab I already have some "Hide Quarter" buttons which hide/unhide everything but the current quarter. They work. It's the code for the buttons below which I can't get to work.

As you can see I am intending the user to enter in a project start and end date and I want to see all columns outside these dates hidden. So far this is where I am with the code but I keep getting a "Compile Error: Next without for". From what i can see I have the next and for's but I get a bit out of my depth there.

Please Login or Register  to view this content.

Can someone provide some feedback?

**StuCram** · 06-15-2015, 07:10 PM

The IF / THEN ELSE statement in each FOR / NEXT loop is missing its END IF.

Adding ENF IF just before NEXT should prevent the error (the compiler gets to NEXT before the IF decision has ended, hence the obscure error message.)

Hopt this helps.
- Stu

**Fatrobo** · 06-16-2015, 12:50 AM

OK thanks Stu that helped. It's still producing errors. This time "method 'Range' of object '_worksheet' failed".

Please Login or Register  to view this content.

And the GetColumnLetter_ByInteger code:

Please Login or Register  to view this content.

Anyone have any ideas? I didn't imagine this would be such technical code. All I wanted was to his columns outside a users specified range. I'm not sure my code is even on the right path...

Column number reference using cell reference

LinkBack

Thread Tools

Rate This Thread

Display

Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Re: Column number reference using cell reference

Thread Information

Users Browsing this Thread

Similar Threads

how to reference cell value from column number and row number

Extract Cell Reference from Formula Change Reference Column Populate new Cell

[SOLVED] VLOOKUP to reference cell, not column number

Go to a cell in column, based on a reference number

Column Reference-How do I reference a cell in a different workbook?

Bookmarks

Bookmarks

Posting Permissions