or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 26

post #376 of 532
So, is there a good way to duplicate the way autofill works with VBA? In the sense that, I want a macro that opens up a document, adds a column, and fills that column with a value all the way down to the bottom of the sheet, as would happen if I manually double-clicked the corner of the cell.

Basically, how do I use relative positioning in VBA? I once boot-strapped something with a macro that would find the next blank cell, activate it, and then paste what had been copied (it was just a simple macro to stitch together multiple files into one sheet), but I can't figure out how get a fill-down command to work.
post #377 of 532
Quote:
Originally Posted by NameBack View Post
So, is there a good way to duplicate the way autofill works with VBA? In the sense that, I want a macro that opens up a document, adds a column, and fills that column with a value all the way down to the bottom of the sheet, as would happen if I manually double-clicked the corner of the cell.

Basically, how do I use relative positioning in VBA? I once boot-strapped something with a macro that would find the next blank cell, activate it, and then paste what had been copied (it was just a simple macro to stitch together multiple files into one sheet), but I can't figure out how get a fill-down command to work.

The VBA code is

Selection.AutoFill Destination:=Range(Range address)

of course the trick is defining Range address within the macro code
post #378 of 532
Quote:
Originally Posted by rajesh06 View Post
The VBA code is

Selection.AutoFill Destination:=Range(Range address)

of course the trick is defining Range address within the macro code

Yeah. Sorry, should have clarified -- that's my question.

I know how to get it to do an autofill to a certain range, the problem is that I can't figure out how to get it to fill to anything other than a static range.

The makeshift solution I have is that it fills down to a predetermined range, which is way longer than it needs to be (to make sure that it fills down sufficiently far, as the files I'm working with change regularly in number of records), and then I de-dupe the thing to get rid of the rows that only contain the filled value.
post #379 of 532
Thread Starter 
Quote:
Originally Posted by NameBack View Post
Yeah. Sorry, should have clarified -- that's my question. I know how to get it to do an autofill to a certain range, the problem is that I can't figure out how to get it to fill to anything other than a static range. The makeshift solution I have is that it fills down to a predetermined range, which is way longer than it needs to be (to make sure that it fills down sufficiently far, as the files I'm working with change regularly in number of records), and then I de-dupe the thing to get rid of the rows that only contain the filled value.
If you want to get to the last used cell in a (continuous) row or column, you can hold ctrl and press an arrow key. There's a VBA method that does this, I just can't remember exactly. You can record a macro and watch the VBA code it generates as you do this to figure it out. Something like .End(xlRight). Anyway I think that returns the cell address of the cell you land on. I don't remember a lot of this stuff off the top of my head.
post #380 of 532
Quote:
Originally Posted by NameBack View Post
Yeah. Sorry, should have clarified -- that's my question.

I know how to get it to do an autofill to a certain range, the problem is that I can't figure out how to get it to fill to anything other than a static range.

The makeshift solution I have is that it fills down to a predetermined range, which is way longer than it needs to be (to make sure that it fills down sufficiently far, as the files I'm working with change regularly in number of records), and then I de-dupe the thing to get rid of the rows that only contain the filled value.

I think if you can get to the first cell in the range than you can use xlDown to select a range of continuous cell. You than give that selection a name and use it in the AutoFill.
post #381 of 532
I have a column with addresses some of the duplicates are not all spelled exactly the same way ( Capital of Texas Highway can look like Capital TX Hwy, CapL of Tx Hwy... etc.) and I also have a column with the corresponding city. The problem is I don't have the zip code for any of these locations. Is there any way I can match up a zip code without looking up each inidvidual address? There are over 1000 entries so far. I'm not holding out much hope.
post #382 of 532
Quote:
Originally Posted by Bhowie View Post
I have a column with addresses some of the duplicates are not all spelled exactly the same way ( Capital of Texas Highway can look like Capital TX Hwy, CapL of Tx Hwy... etc.) and I also have a column with the corresponding city. The problem is I don't have the zip code for any of these locations. Is there any way I can match up a zip code without looking up each inidvidual address? There are over 1000 entries so far. I'm not holding out much hope.

I don't exactly understand your issue - but I would think that only the city matters in terms of zip code. So long as you have a table that has the city and zip - you can simply lookup the zip from that table.
post #383 of 532
Quote:
Originally Posted by rajesh06 View Post
I don't exactly understand your issue - but I would think that only the city matters in terms of zip code. So long as you have a table that has the city and zip - you can simply lookup the zip from that table.

Yeah, but most cities have more than one zip code, and big cities have many.
post #384 of 532
Quote:
Originally Posted by yerfdog View Post
Yeah, but most cities have more than one zip code, and big cities have many.

good point - i have no good solutions for you then.

If your goal is to rename all the addresses consistently then I would suggest creating a "rename" table which starts with a pivot table with the addresses in the rows and then some sort of count as the data. You would then sort this alphabetically in order to get similar names as close together as possible. Finally you'd add a rename field that would be common for all the addresses that are the same (but not coded the same).
post #385 of 532
I have a column that has cells with keywords in them. Adjacent to that column I have cells with numbers in them. Is it possible to write a big if statement to go through and sum up the numbers associated with a keyword? Or would I have to write a macro to loop through all of the cells in whatever range I want? Any guidance? I'm using '03 if it makes a difference.
post #386 of 532
Quote:
Originally Posted by deadly7 View Post
I have a column that has cells with keywords in them. Adjacent to that column I have cells with numbers in them. Is it possible to write a big if statement to go through and sum up the numbers associated with a keyword? Or would I have to write a macro to loop through all of the cells in whatever range I want? Any guidance? I'm using '03 if it makes a difference.

Sumif or pivot table bro.
post #387 of 532
Quote:
Originally Posted by MetroStyles View Post
Sumif

post #388 of 532
Quote:
Originally Posted by MetroStyles View Post
Sumif or pivot table bro.

More specifically... If it looks like this:

A B C
1 Thomas Pink Shirt $190
2 Mabitex Trousers $90
3 Thomas Pink Tie $70
4 Edward Green Shoes $750

=sumif(a1:a4,"Thomas Pink",c1:c4)

Would evaluate $260.
post #389 of 532
this one has me stumped. I have a file with numberous vlookups in it that look to another excel file to find the data. so...

file1 has vlookup and points to file2 to find the data.

so for some reason file1 is caching the data it looks up such that even when the linked file is unavailable, lookup data shows up. This is causing the file to get quite large.

Does anyone know how to turn off this feature or prevent it from happening?
post #390 of 532
this one has me stumped. I have a Excel 2003 file with numberous vlookups in it that look to another excel file to find the data. so...

file1 has vlookup and points to file2 to find the data.

so for some reason file1 is caching the data it looks up such that even when the linked file is unavailable, lookup data shows up. This is causing the file to get quite large.

Does anyone know how to turn off this feature or prevent it from happening?
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread