Excel wizards help me please

[Jessica]

I am writing a vbscript to pull data from a table in an excel spreadsheet. i want to be able to find the top left corner of the table regardless of where it is on the spread sheet.

ListObjects("Table1"). doesnt seem to have any modifiers to pull this information. Its handy for finding the table width and length but not location.

Currently i am using cell a,1 and just putting the letter of the column the table starts but i think that is janky and this program is going to be used by a plant manager to keep inventory. He has a habit of dicking with things and i want to dummy proof it as much as possible and also want it to be flexible so if the spreadsheet changes the code will adapt.

so if any of you have a way to do this besides searching each and every cell on the page manually please chime in.

thanks

[Pipo99]

mrexcel.com/forum

[MrSkippyJ]

Have you thought about locking down the table format so he can't mess with it?

[Jessica]

Have you thought about locking down the table format so he can't mess with it?

yes. I really enjoy coding and a challenge. But i have a new plan now after digging around. I am going to put the table in the upper left, and tell him to use the built in sorting functions of the table to retrive the info he wants. Then i am going to use some code to do the heavy lifting from there. If this was just pure VB it would be way easier because i could just pull data from a drop down box. I mean its easy to type "thisisstuff=listbox1.item.(listbox1.index)" or whatever the function is lol.

You have to understand that my boss has some year old code i wrote for him where i sorted data from a table and listed it that had recursive calls and all kind of craziness. I had to throw it together in like 4 hours and i never had done vbscript before that. He asked me to recycle that to do some other shit. That spreadsheet had hidden cells and all kinds of moving parts and in the code i annotated that if you move things around the code would break. So he moved things around and said "well, i read some of your code but i didnt really understand, can you fix it?"

[MrSkippyJ]

man my bad...I thought you said VBA script. Not that it changes much, I have no idea how you would get it took look for where a table is located. I don't even know how to do that manually other than looking at the sheet and clicking in the cell with data.

[Jessica]

thats the issue, all i can find is how to find an active cell. I have googled the hell out of this looking for answers. At least i am on the clock while doing this.

Its no big deal, i have a plan now so i think i can make this work as its easy to find the number of columns and rows in a table so if he adds fields to it i can adjust for it.

I just hate writing code that has things hard coded into it, i have worked hard to learn how to write functions that are fluid and adjust regardless of what the user does and this s one of those things where i am limited by a lazy apporach by microsoft.

[MrSkippyJ]

I feel you only the hard coding thing...I run into the same problem building datamarts from our databases at work. I find myself having to put in ridiculously long case statements into my SQL scripts to cover for all the bad data we have.

[Jessica]

omg, Select case is so amazing compared to "if then". I remember when i learned that select case was basically two instrunctions compared to "if then" being every single "else" statement. i was like "ok, i can have 15 instructions, or i can have two instructions. let me think about this..

lol

[MrSkippyJ]

Oh I agree completely! I still am not a fan of using it to correct for terrible data though.

[Jessica]

well, thats the life we live though.

I hate that i have to waste so many lines of code to just protect other code from stupidity.