Transpose Array VBA Script

I had come previously for help with a formula in This Post.

1.
The formula seemed to have been working at the time, however once I added to it and began testing it using multiple inputs, it stopped properly working. It would only convert, transpose, and format the first input, multiple times in the outputted area.

What the output is suppose to look like

Please Login or Register  to view this content.

What the output actually looks like.

Please Login or Register  to view this content.

2.
Originally, the VBA script was to...
1. Take the inputs, each located in their own cell in the first worksheet
2. Convert them from their comma deliminated format
3. Transpose them into the second worksheet
4. Format them by deleting cells, moving infromation around etc.
5. Go on to the next input and repeat the process.

3.
Example Input Located in a Single Cell

Please Login or Register  to view this content.

Current VBA Script

Please Login or Register  to view this content.

4.

Additionally I have attached a copy of the excel sheet to this post.

Attachment 342729

I appreciate any and all help. Thank you!

Originally Posted by Vikas_Gautam

Use following statement to transpose your respective array..

Please Login or Register  to view this content.

I apologize, I may not have used the correct wording in my post.
The script currently moves everything in the array properly, from the first worksheet to the new format in the second. This is not an issue.

The issue is that the script only seems to work properly for the first cell in the first worksheet, and ignores all other input cells, while outputting the results from the first cell multiple times.

Originally Posted by Norie

The attachment is invalid apparently, could you try it again?

Originally Posted by Izandol

This attachment link does not work.

Reattaching. Sorry about that!

Transpose Array.xlsm

Originally Posted by Norie

As far as I can see you never change the input range, it's always A1.

I set a new variable called InputCount and tried using it to change the row every time the script loops.
However keep getting a debugging error on the following line.

For Each cl In Sheets("Input").Range("A" & InputCount).Resize(Sheets("Input").Range("A" & Rows.Count).End(xlUp).Row)

I apologize I am a bit new to programming in general, and have been teaching myself VBA, starting with this script.

Here is what the new script looks like.

Please Login or Register  to view this content.

"For Each cl In Sheets("Input").Range("A" & InputCount).Resize(Sheets("Input").Range("A" & Rows.Count).End(xlUp).Row)

I changed the line to X = Split(Range(cl).Value, ",") , however am still getting the debugging error I mentioned above.

Thank you for all your help so far! Its greatly appreciated.

Please don't miss my last post on page 1! Thank you!

Originally Posted by Izandol

I do not think your data is well delimited. If all delimiters were present I believe you may use code like this:

Please Login or Register  to view this content.

Edited: I do now see you do not require only each second column!

I believe I missed this post when I responded earlier.

Yes I understand that the input is not well deliminated. However, I have no control over this, since the put is given to me as is and I am trying to find a way to organize it into something more legible.

But thank you for the suggestion!

Does anyone have any ideas regarding the aforementioned "invalid or qualified reference" error that I am getting?
The debugger stops on line Set rngDst = rngDst.Offset(1).

I tried running your script however received a compile error.

Please see the below screenshot.

compile error.png

Originally Posted by Izandol

I left a mistake - i have corrected it now.

I am still receiving the following error. (I copied your updated version of the code)

compile error 2.png

And then debugger brings me to the following line of code: Count = rngDest.Row

Originally Posted by Izandol

This is very bad - I left another mistake! Corrected once more.

Thank you for being so patient with me!

However, I am still receiving the same error from my previous post but on a different line of code.

Set rngDst = rngDst.Offset(1)

Once again, thank you for your patients and for taking so much of your time to help with this!

Originally Posted by JosephP

it's deleting the range-try:

Please Login or Register  to view this content.

Thank you for your help, however I am receiving this error from your script, and the debugger stops on this line.

compile error 3.png

For Each cl In Sheets("Input").Range("A" & InputCount).Resize(Sheets("Input").Range("A" & Rows.Count).End(xlUp).Row)

Thank you, Josie. I think I must have some sleep now so I do not provide more foolish answers!

It's ok! I really appreciate all the effort you put forth in trying to help me with this script!
Enjoy your rest.

Originally Posted by JosephP

well it's way too early for me to go to bed :-)
my bad-that line oughta be

Please Login or Register  to view this content.

Wohoo!!!! It works!!

Thank you very much for all your help!!

I also want to thank everyone else who also helped.

I am now going to spend some time reading through the script. I may have some questions relating to how it works.
I believe that I should at least understand your hard work if I am to use it.

Try the attached.

If this is not how you wanted, just ignore it.