The Excel Questions Thread

[NameBack]

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.

 

[rajesh06]

Originally Posted by NameBack

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

 

[NameBack]

Originally Posted by rajesh06

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.

 

[CunningSmeagol]

Originally Posted by NameBack

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.

 

[rajesh06]

Originally Posted by NameBack

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.

 

[Bhowie]

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.

 

[rajesh06]

Originally Posted by Bhowie

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.

 

[yerfdog]

Originally Posted by rajesh06

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.

 

[rajesh06]

Originally Posted by yerfdog

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).

 

[deadly7]

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.

 

[MetroStyles]

Originally Posted by deadly7

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.

 

[deadly7]

Originally Posted by MetroStyles

Sumif

 

[rdaws]

Originally Posted by MetroStyles

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.

 

[Neo1824]

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?

 

[Neo1824]

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?