excel VBA/Macro Help


  #1  
Old 04-30-14, 06:54 AM
B
Member
Thread Starter
Join Date: Jul 2006
Location: Central MN
Posts: 559
Received 0 Votes on 0 Posts
excel VBA/Macro Help

I need help with a macro or some code to format a report I get in excel.

I don't have the option of saving the report in any other format when it is first downloaded from the website I use. I could open and re-save in CSV, but would prefer not to do that if possible.

I need to move various cells around to make a much easier to read format. I have include a link to a file that has 2 sheets. The first sheet (raw output) is the layout I get when it is downloaded. Everything on that sheet is a variable EXCEPT the bolded headings.
File

The 2nd sheet is the layout I want.
Key: Green Text is added by me.
Red Text is variables
Black Text is in original layout.

My problem is that all the variables change based on the product selected. For example, one product may have 3 chemicals so there will only be 3 rows under the CAS no. heading, the next may have 12. The report will list the product in alphabetical order by number (all the numbers have letters in front of them e.g. nuf3074). So one report may have a product first that has 12 chemicals and the next report will have 9. This will then change where all the other subsequent products line up in cells.

Help?
 
  #2  
Old 04-30-14, 10:48 AM
W
Member
Join Date: Feb 2006
Location: USA
Posts: 6,911
Received 51 Votes on 46 Posts
Excel

You need the cell addresses of the raw data sheet to assign the data to a new format.
 
  #3  
Old 04-30-14, 10:52 AM
B
Member
Thread Starter
Join Date: Jul 2006
Location: Central MN
Posts: 559
Received 0 Votes on 0 Posts
buthe cell addresses chang with each report based on products requested/
 
  #4  
Old 04-30-14, 07:56 PM
Z
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 3,652
Received 85 Votes on 79 Posts
hi bc-

Iím not that familiar with Excel, I use it just a little. You can go into Developer mode in Excel (which I never knew. lol) and write VB code. I played around a little and came up with the code below that works but needs a little bit added. I really wasnít sure how the input VOCís you show are related to the output format. But that should be easy. The code assumes that the sheet to be converted is Sheet 1 and it builds an output Sheet 2 in the converted format.

The idea is you loop through sheet 1 detecting a product # via a non-blank entry in column 1 (as you show with Raw Output). When you find a product # the data is moved from sheet1 to sheet2 adding row and column headers like you show (but some are not added here for brevity). To process a given product a loop is also executed that runs down the CAS column on sheet 1 moving the entries to the CAS column (as you specify) on the output sheet (sheet2).

The code detects the end of the CAS entries for that product when it sees a blank cell as you show. It then checks that same row to see if another products starts and so on until no further products are found.

There are a million ways to do this but I think this may give you the general idea. You can use hard coded constants as I did, or you can search for information to set the variable pointers to rows and columns. For example, you can search the input sheet for the column starting with the word ďCASĒ and save that column number and use it the remainder of the code instead of hard coding it like I did.

There are better ways to do this but I though this may point you in the general direction.



 
  #5  
Old 05-02-14, 04:55 AM
B
Member
Thread Starter
Join Date: Jul 2006
Location: Central MN
Posts: 559
Received 0 Votes on 0 Posts
Thanks, I think this will give me a good start on what I want. I am self-taught in VBA so I think I have missed some things.
 
  #6  
Old 05-02-14, 08:10 AM
Z
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 3,652
Received 85 Votes on 79 Posts
Glad if I can help a little bc. Iím really no expert on VB, but I used it a little on a project I worked on years ago. (Iím certainly no expert on Excel. Iím a little confused on what happens with code, etc. when you rename sheets in a workbook? Might play around a little.) But I had a lot of years as a software guy.

If you have any questions I would be glad to help, if I can.
 
 

Thread Tools
Search this Thread
 
Ask a Question
Question Title:
Description:
Your question will be posted in: