Big an complicated project

[Little_Ghost]

Hi eveybody,

I'm working on a big and complicated project. This type of project will return every so many months and I don't want to have to do this whole thing by hande every time. let me go into a little more detail:

I get a txt file supplied full of data (I think it's outputted from a database). I can get the data into excel in 2 neat columns but then the the problems starts.

The first column will be a code ranging from AA-0000 to ZZ-ZZZZ and everything in between (currently it's 104,085 entrys so it's spread out over 2 worksheets).
In the first column some blank lines, lines with '---' and lines with a header as well but those are irrelevant.
The second column will be just be some description.

How it eventually needs to be is that every row starting with an 'A' in column A has to go on a new sheet titled 'A'. Then every row starting with 'B' should be placed on a sheet titled 'B' etc etc. In the end you would have 26 sheets; one for every letter.

I tried so many options I don't even remember what I tried (or what not for that matter :S) and I'm getting realy frustrated with this whole project. I'm at my wits end on how to proceed (or start over) so all help is MORE then welcome!

Hope one of you people know what I could do or can help me out.

Kind regards,

- Little_Ghost

 

[Little_Ghost]

A space in front of it would be enough for it to work I guess but then the user would have to look that code up on forehand which is far from ideal....
Then again; an = at the beginning is a problem for excel anyway because it then expects a formula.

- Little_Ghost

 

[hiker95]

Little_Ghost,

= TST EVENT =============================================================================

I figured out that it is the '=' sign at the beginning that causes the problem but I don't know how to solve this (I get the biggest part of the code but that's where it stops I'm afraid).

Can we have another workbook with the above information in one of the two worksheets?

 

[Little_Ghost]

Of course you can! Here you go!
http://www.box.net/shared/z17tghkys753fu0as3fy

I've brought it down to one worksheet since the problem lies with that one specific error.
Hope that's not a problem.

- Little_Ghost

 

[hiker95]

Little_Ghost,

Thanks for the workbook.

Remember, the macro will only work correctly if there are two worksheets in your workbook (per your original request).


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub DistributeRowsV2()
' hiker95, 09/27/2011
' http://www.mrexcel.com/forum/showthread.php?t=574321
Dim wA(), w As Long, r As Long
Dim ws As Worksheet, nr As Long
Application.ScreenUpdating = False
For w = 1 To 2 Step 1
  wA = Worksheets(w).Range("A1", Worksheets(w).Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
  For r = 1 To UBound(wA)
    wA(r, 3) = Left(wA(r, 1), 1)
  Next r
  For r = 1 To UBound(wA)
    If Not Evaluate("ISREF(" & wA(r, 3) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wA(r, 3)
    Set ws = Worksheets(wA(r, 3))
    nr = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    If nr = 2 And ws.Range("A1") = "" Then nr = 1
    ws.Range("A" & nr) = wA(r, 1)
    If Left(wA(r, 2), 1) = "=" Then
      ws.Range("B" & nr) = "'" & wA(r, 2)
    Else
      ws.Range("B" & nr) = wA(r, 2)
    End If
  Next r
  Erase wA
Next w
Worksheets(1).Activate
Application.ScreenUpdating = True
End Sub

Then run the DistributeRowsV2 macro.

 

[Little_Ghost]

in the original file there are 2 worksheets but just for the ease of thnigs I made a micro version with one sheet for here.
So far the macro also seems to be working perfect with a one sheet variant.

I'll test it tomorrow when I'm @ work again... when I have the time that is :S

Thanks in advance!

- Little_Ghost

 

[Little_Ghost]

Sorry for not getting back to you guys for a while but it has just been insanely busy here.
I finally got around to testing the code and it worked but that's when I realized I forgot something:

The first column will not hold the error code.
The second column will hod the error code (the third the description).
The first column will have a formula in it that will look up the error code from column B in a 3rd worksheet and output either N/A when it's not found or the 'cluster' it belongs to when it is found.

I've been fiddeling around but can't figure it out. I've had a lot of different outcomes (columns moving, columns adding (that's good!)) but the data from column A never gets copied. :S

 

[Little_Ghost]

... wait.. it gets worse... there should be a header above the columns...

**edit**

I've made a new test workbook with the exact way the input sheets will look.
http://www.box.net/shared/0slsolxxcthyre8l34ee

Sorry for the hassle :$

- Little_Ghost

 

[Little_Ghost]

...And then we got upgraded tot Excel 2010 and the macro stopped working
It will give a run-time error 13 (type mismatch).
When I debug it, it will tell me it goes wrong at the line reading

 wA(r, 3) = Left(wA(r, 1), 1)

 

[hiker95]

Little_Ghost,

...And then we got upgraded tot Excel 2010 and the macro stopped working
It will give a run-time error 13 (type mismatch).
When I debug it, it will tell me it goes wrong at the line reading

wA(r, 3) = Left(wA(r, 1), 1)

I would like to see the actual workbook where the above error is occurring.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Little_Ghost]

I've been playing around with it and got it working.

Just wondering about something:
Each block (AA - AB - AC etc) starts and ends with -------------------------
after that there is a white/blank line followed by a header/line of code

Currently I have to go through the file by hand and delete all those lines manually.
Using filter doesn't work somehow... Is there a way to make this easier/automate this/include this in the macro?
And is there a way to get the top row on each newly created sheet to display a header (the same for every sheet)?

- Little_Ghost