Author Topic: VBA in Excel  (Read 10645 times)

0 Members and 1 Guest are viewing this topic.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
VBA in Excel
« on: January 23, 2008, 09:18:05 AM »
I have never written anything in VBA for excel.  that being said, I could use some help with a function to purge a spreadsheet.  Any takers?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #1 on: January 23, 2008, 09:18:52 AM »
Quote
000011815   UNS Electric Inventory   STICK SHOT GUN CHANCE SINGLE   Each   DCLASS   EF-KSAFETY 1   7   $202.57
000011815   UNS Electric Inventory   STICK SHOT GUN CHANCE SINGLE   Each   DCLASS   EF-LSAFETY 3   7   $202.57
000011815   UNS Electric Inventory   STICK SHOT GUN CHANCE SINGLE   Each   DCLASS   EF-NSAFETY 4   7   $202.57
000011816   UNS Electric Inventory   GRIP SMALL   Each   DCLASS   EF-KSAFETY 3   7   $79.60
000011816   UNS Electric Inventory   GRIP SMALL   Each   DCLASS   EF-LSAFETY 12   7   $79.60
000011816   UNS Electric Inventory   GRIP SMALL   Each   DCLASS   EF-NSAFETY 2   7   $79.60
« Last Edit: January 23, 2008, 09:24:34 AM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Guest

  • Guest
Re: VBA in Excel
« Reply #2 on: January 23, 2008, 09:22:56 AM »
I have never written anything in VBA for excel.  that being said, I could use some help with a function to purge a spreadsheet.  Any takers?


Huh?  Purge as in like, selecting everything and pressing the DEL key??  :?

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #3 on: January 23, 2008, 09:26:56 AM »
Purge as in like, selecting everything and pressing the DEL key??  :?
Yes  hahaha,

OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below.  Delete the row which is < the other.  Make sense?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Guest

  • Guest
Re: VBA in Excel
« Reply #4 on: January 23, 2008, 09:30:41 AM »
Purge as in like, selecting everything and pressing the DEL key??  :?
Yes  hahaha,

OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below.  Delete the row which is < the other.  Make sense?
Yup.  Can you post a small example, like what you have above (only in XLS format, of course).   :-)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #5 on: January 23, 2008, 09:31:31 AM »
I am trying to figure out how to grab A# and A#+1 .  From there the > comparison is pretty easy.  Deleting the row is the next challenge.  I also thought about using N (integer) to keep up with where I am row.count wise

Edit: Attached XLS file
« Last Edit: January 23, 2008, 09:36:39 AM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #6 on: January 23, 2008, 09:37:14 AM »
Sorry it took so long, I didn't know you couldn't post xls file directly. 
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Guest

  • Guest
Re: VBA in Excel
« Reply #7 on: January 23, 2008, 09:40:53 AM »
Purge as in like, selecting everything and pressing the DEL key??  :?
Yes  hahaha,

OK no, I need to search Column A for each number, like 11815 in above example, then go over to the H column and compare the $$ amount to the one below.  Delete the row which is < the other.  Make sense?

What if, like in your example, the dollar amounts are the same?

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #8 on: January 23, 2008, 09:44:47 AM »
Delete one of the rows.  This is a very small part of an inventory/vendor spreadsheet that our warehouse has with the people that order the parts.  So there might be 2 vendors with the same price, or 10 w/ the same, or 10 and 9 have the same price, but 1 is higher.  Im trying to narrow it down for the upcoming budget season so I can use the biggest amount to CYA in the budget.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #9 on: January 23, 2008, 09:45:53 AM »
BTW, this SS is 35000+ lines long and I spent 4 hours yesterday manually deleting the extra rows.  I only made it through ~1000 before I went crazy. 
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Greg B

  • Seagull
  • Posts: 12417
  • Tell me a Joke!
Re: VBA in Excel
« Reply #10 on: January 23, 2008, 10:19:53 AM »
A good starting point is to record a macro.  Then you can modify it to fit your needs.

Basically start recording and do it manually what you want done and take a look at the code.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #11 on: January 23, 2008, 10:21:24 AM »
How do you record something when the process takes place in your mind?  How do you look at 2 rows, and delete the smaller one?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Greg B

  • Seagull
  • Posts: 12417
  • Tell me a Joke!
Re: VBA in Excel
« Reply #12 on: January 23, 2008, 11:05:52 AM »
How do you record something when the process takes place in your mind?  How do you look at 2 rows, and delete the smaller one?

This of course is where it gets modified.

At least you'll have to code to grab a row and delete it.  You throw in an

if x > y then
rows.delete yadda


Greg B

  • Seagull
  • Posts: 12417
  • Tell me a Joke!
Re: VBA in Excel
« Reply #13 on: January 23, 2008, 11:06:57 AM »
First hard code, then modify to allow for variables.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: VBA in Excel
« Reply #14 on: January 23, 2008, 11:09:32 AM »
ok, now I see where your going.  The problem is I know very little about VBA in excel.  How do I grab a row?  Im thinking there should be an activerow, but I haven't found it yet.  Once I figure out how to grab a row, the > logic should be pretty straightforward
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)