Custom formatting using either Macro or VB script

[xander208]

Hi,

Ok, what I am trying to do is this. I have a report at that I am trying to extract information from that can be copied and pasted into another which already has a VLOOKUP function implemented to reference data within a certain format.

The report that has the source information is in a different format going down columns with multiple duplicate entries in various cells down the columns.

I have a macro that seperates out the data from the report that I ened but I am having trouble getting the data to format the way I need automatically. I have tried using a Copy>Paste Special> Transpose but it causes the data to not format correctly. Transpose is the function that I believe I need because the format goes from left to right instead of down the page on the report that needs the ference data.

I believe I could do this in VB by setting up a script that would say something a long the lines of this psuedo code:

Index Column (select column)
If Value, transpose to cell to right
Else, leave blank

Any ideas on how I could accomplish this?

[JBeaucaire]

Start by posting a sample workbook with a BEFORE/AFTER example in it.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[xander208]

Hi Paul,

The name of the thread of is now changed to reflect my issue. I have also attached a copy of an example.

[watersev]

hi, xander208, option, run code "test". The types are considered to be consecutive as in example file.

[xander208]

Looks like this is doing what I am needing, the only issue is on the script backend, im not understanding what all it is doing. Would you be able to possibly add a few comments to it?

Also, would it be possible to modify the script so that i includes an additional column for a "product description" inbetween columns A & B (Product ID & Product Cost)?

[watersev]

can you post sample file containing exact data layout of your real-life file. I will add code to it together with comments

[xander208]

Uploading a new copy. This is the exact format, though some of the naming conventions on the "types" are different. The rest is basically the same formats im working with.

[watersev]

please check attachment, run code "test", the code has been competely changed, comments added

[watersev]

please note, the file should be saved before running the code

[xander208]

looks like that does what i need. only other question I have is is there any way to rename the macro from 'test' to something different?

[watersev]

yes, you can give it a different name (w/o spaces), goto Tools - Macros - select macro "test", press Change.
The code name is in the following line at the top

Please Login or Register  to view this content.

[xander208]

ok. that worked. Thank you! One more thing i noticed, if i rename the file to anything other than the current name the macro will not run. Any suggestions?

[watersev]

I renamed the file and saved it, the code runs as expected as the file name is not hardcoded in it. There are 2 options that are checked before running the code to avoid errors:
1)

Please Login or Register  to view this content.

makes sure the sheet the code would run has ""Product Description" in B1 cell
2)

Please Login or Register  to view this content.

checks for word "Total" existing on th sheet.
If you feel it's not required, you can comment them out by putting ' sign at the start of the row. But when you run the code on a sheet with different data layout (different header, table of data located say from Y2 cell) it would result in error.

[xander208]

Ok, looks like everything is working. Was able to make another macro to work in conjunction with the script. I really appreciate the help!

[watersev]

if you are satisfied with the help provided and there are no more outstanding issues, please mark the thread as Solved:

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.