Vba - rows to columns

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:01 PM GMT

Question

QUESTION: Tried to post on AllExperts but got a ¡°Too many follow-ups, please ask a new question¡± error.

That¡¯s fantastic Working all perfectly. Just a couple of adjustments if possible.

The quantity of data is unknown, at the moment it only handles 5 pairs. Is it possible to increase this, but to a maximum value, depending on the item (See email). And also is it possible to output to data into another set of columns, ie. It only fills Columns A&B to Row 28 and then goes into Columns C&D (Also See email).

Please see the attached file in email, Output Required Tab for more details.

If question is unclear, please let me know.


ANSWER: Phil,

sent revised file. Note that your formatted sheet has some merged cells. They don't seem to be consistent with the data you are writing there, so I am just going to write to a blank sheet for now.


---------- FOLLOW-UP ----------

QUESTION: It is working almost perfectly. Just trying to deal with the merged cells on the output sheet.

After playing around a bit with your code, I believe that the adjustments need to be made to the following code:

If i > lfirstRow Then

           jj = jj + 2
           j = jj
           i = rr.Row
        End If

As the number of columns that need to be offset is different for Fascia than Gutter, I thought adding a simple If statement would work.

Have tried implementing as follows:

        If i > lfirstRow Then
        If jj = Range("E19").Column = 5 Then      'Gutter
                jj = jj + 4
                j = jj
                i = rr.Row
        End If
       If jj = Range("A19").Column = 1 Then       'Fascia
                jj = jj + 2
                j = jj
                i = rr.Row
        End If
        End If

However it fails to work. Any assistance is much appreciated.

Phil

Answer

Phil,

You would have to explain the output you want. On the sheet you sent me qty has merged cells over two columns in one section and one column in other sections. Then length is the opposite.

A: Quantity B: Length C: Quantity D: Length E: Quantity F & G: Length H:  ?? I & J: Quantity K: Length

If jj = Range("E19").Column = 5 Then      isn't really a legitimate construct. I suspect it would end up testing whether jj  = True which would fail. 

So if the sheet you sent is correct, then I will try to adapt the code that way. Otherwise send the output definition you want to use.


Question

QUESTION: Looking for a bit of assistance with transposing some data.

I am importing CSV files which have data outputted like this:

[ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] Item1 [ ] 1 5200 2 3200 1 6500 1 900 5 4000 [ ] Item2 [ ] 2 4500 3 9600 1 600 1 1900 4 2000 [ ] Item3 [ ] etc etc

And I need to convert into output like this:

[ ] [ ] [ ] [ ] [ ] [ ] [ ] Item1 Item2 [ ] 1 5200 2 4500 [ ] 2 3200 3 9600 [ ] 1 6500 1 600 [ ] 1 900 1 1900 [ ] 5 4000 4 2000

Using VBA Macro would be ideal, so that is can be linked to an on screen button.


Any help is much appreciated.

Phil

ANSWER: Phil,

could you send a sample file so I could take a look and test the code.

Also, what version ofare you using?

send to


---------- FOLLOW-UP ----------

QUESTION: Email has been sent.

Will be run on2007 and 2010. (May potentially be run on 2003, will this be an issue?)

Phil

ANSWER: Phil,

Received the file.

So the file will already be in a workbook when you want it processed? I will write the code to do that. You have 4 categories in your input, but you only show 3 for the output - plus the output look pretty random as far as location. I will send you something and you can tell me how you actually want it.


---------- FOLLOW-UP ----------

QUESTION: The data will be imported from a CSV file by using the Data -> Get External Data From Text option in Excel's Ribbon. (Unless there is an easier way to import a CSV file into a new sheet in a workbook?)

The "Ridge" data is not required in the output.

As for the locations of the output, if possible they will be as follows:

Fascia Heading ("Fascia"): A15 Values start: A18,B18

Gutter Heading ("Gutter"): E15 Values start: E18,F18

Valley Heading ("Valley"): A39 Values start: A41,B41

There are also 5-6 other items, but I should be able to manipulate the macro/code to suit the other items after wards.

Phil


Answer

Phil,

OK, got the additional information

I will incorporate this information.

and send you another file.

========<Revised/Additiona>

Phil,

OK, I adapted the macro to the additional information.

It still adds a sheet and writes the data there. It processes the first worksheet in the workbook.

run Process_Data

Let me know if you need changes.

Advertisement

©2017 eLuminary LLC. All rights reserved.