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
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: 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
Phil,
OK, got the additional information
I will incorporate this information.
and send you another file.
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