Filling Worksheet from USerform without a Table

[rakotonirinas]

Hello,

I would like to capture data from a userform and transfer it to a worksheet. I have defined all textboxes and comboboxes to the order it should appear on the worksheet.

I have the following formula. However, I would like to execute it without creating the dynamic range or rather the Table. Is there an alternative way of writing this code please?

Your help is much appreciated. I guess what I'm trying to achieve is to transfer the data from the userform to the worksheet in the order in the userform.

Thank you very much

Please Login or Register  to view this content.

[TMS]

Untested (not near PC)

Please Login or Register  to view this content.

[rakotonirinas]

Dear TMS,

I'm sure it's closer to what I need but I'm guessing it needs adjusting given that I didn't give all the info needed. I have attached the workbook plus vba to make it easier to understand.

I still have loads to learn when it comes to using the For ... Next and adjusting the array through reDim.

Hope you can help or if anyone would like to contribute also.

Much appreciated.

The current code I had, which I thought would make it simpler, didn't work either

Please Login or Register  to view this content.

[TMS]

In what way is it not close enough? There's not much difference to your original code. It uses the array you populate from the form and checks column A for the next row number. Your final code does the same but uses column C (and loops through the form entries).

I would have thought you could adapt the elements to meet your needs.

[rakotonirinas]

Hi TMS,
My apologies if you read it's not close enough. Rather the contrary, it's definitely closer and certainly there but somehow, I don't know how to adapt it to my current worksheet setting. In other words, it's my lack of knowledge that sucks ... but I'm learning.

My worksheet only starts from column C instead of column A, so I'm not sure how I can adjust the code accordingly. Also, do I have to change the ReDim arr(0, 36) since the first box in the userform starts with CT_01.

Would that make any sense? Thank you.

[TMS]

Does the array method work with the table. I had assumed your table started in column A, why wouldn't it?

Try this.

Please Login or Register  to view this content.

[rakotonirinas]

Does the array method work with the table. I had assumed your table started in column A, why wouldn't it?

That would have been the ideal thing to do, however I have to add 2 additional information on the column A and B which are some sort of calculation once the row is populated.

I got Run-Time error '-2147024809 ...):
Could not find the specified object >>> highlighting the

Please Login or Register  to view this content.

.

Thank you

[rakotonirinas]

I've got the following as well but this time it doesn't come with error but not writing the value to the worksheet either

Please Login or Register  to view this content.

[rakotonirinas]

So... after some work around

I managed to make the data transfer work. However, even thought the result is what I would like, the code is not exactly what I'm looking for. I would like to cut the long part by using For ... Next (I assume).

Your help is much appreciated.

Please Login or Register  to view this content.

[TMS]

I am a little bemused as my understanding was that you wanted to improve on existing working code and remove the requirement for a Table.

The sample workbook and subsequent code examples appear to bear no relation to the original code.

So, points for the future: start with a sample workbook that accurately reflects your requirement. If the code doesn't work correctly, say that ... it saves a lot of time wondering what is going wrong in testing.

Whatever, try this:

Please Login or Register  to view this content.

[rakotonirinas]

Hi TMS,
Feedback and points noted. I really wanted to improve on that original code I provided though but at the same time, I was trying any other alternative ways to accomplish the same end result, which I did manage from my last post but clearly too long and not as neat as what you provided.
Perhaps, I have been too unclear on what I wanted to achieve.
I think the code you just gave was an adjustment of the one you suggested one comment above. I will try them and will let you know.
In any case, thank you so very much for your help.

[TMS]

Only the code in Post #10 will work.

Earlier suggestions were based on the premise that your posted code worked.

[rakotonirinas]

Good morning TMS,
You're a star. It worked like a charm. Thank you very much for your help.
One question please and for purely my learning and understanding: what do you think is the real difference between your code (#10) and the one I managed to write (#9) although they arrive at the same result?
Much appreciated, as always.

[TMS]

You're welcome. Thanks for the rep.

One question please and for purely my learning and understanding: what do you think is the real difference between your code (#10) and the one I managed to write (#9) although they arrive at the same result?

Not a lot, other than your version isn't really scalable. The loop builds the array from the elements then transfers the array to the sheet. You're manually coding the array which is tedious, inefficient and prone to errors, omissions and duplication. Imagine if there were a hundred elements on the user form that you wanted to add.

[rakotonirinas]

Very grateful for your help TMS. Indeed, scalability was difficult to achieve on my code. Thanks so much again.