Quick Question for Filling Cells

[CKRebel]

Thanks to anyone who takes time to help answer this question!

I have a userform developed, and it functions exactly the way I'd like. The user selects a value from the first combo box, then from the second combo box. When the "Add" button is clicked, the values are pasted to another worksheet in their proper places. Here is where I ran into my problem: When I open the userform again, I'd like for the next two selections to be placed on the row below the first selections. The first selection fills at row 13, and I'd like for the second selection to fill at row 14. I hate asking for someone to write a macro for me, so I've been grinding it out making this thing work, so if anyone could just steer me in the direction I need to go it would be greatly appreciated. Again, thank you.

[JBeaucaire]

Store the "row number" that you're using to enter the next value somewhere you can read it, like a hidden cell... AA1?

Every time you write information into that row, also increment that value by 1 to set the next row as the needed value. This will survive opening and closing the workbook.

[CKRebel]

Great. I'll be sure to try that one. Once I get it worked out, I'll explain exactly what I did and mark this as solved.

---------- Post added at 04:37 PM ---------- Previous post was at 04:16 PM ----------

Okay so here's what I did:

On the subroutine for when the command button is clicked, I said the value of the cell (equivalent to one less than the row number I'm starting with) is equivalent to it's previous value + 1. Then I said that the cells were to fill as Cells(RowNum, column). It might be easier to follow along when I add the code. JBeaucaire, is this similar to the way you instructed me to make this function?

Please Login or Register  to view this content.

[JBeaucaire]

A little non-linear logic for me, but it appears to be sound, does it work for you, if so, then there you go.

A little simpler in my mind would be:

Please Login or Register  to view this content.

[CKRebel]

Wow JBeaucaire, that code you wrote is much more simple than the code I have; I actually wound up using yours. This macro I'm working on is the first one I've ever written, so any code I write is a modification to an example I have in a book. I'm sure once I spend time with it and become more familiar, I'll be able to write code like yours, which is why I try to grind it out myself rather than ask someone to write for me.

Once again, your help is greatly appreciated!

[JBeaucaire]

Glad to help. The point of the forum is bridge to gap in understanding the difference between recorded inefficient code and verbose, efficient code you can still understand. Read through the forum thread titles under NEW POSTS, especially in the programming forum, and the thread that have topics that are of interest to you, read them and learn.

[CKRebel]

Another quick help if you don't mind me asking. What is the most efficient way inside that code to take the values from Sheet 1 ("B4","B5","B3", and "D3") to Sheet 2 ("D:Row Num","E:RowNum","H:RowNum", and "I:RowNum") respectively?

[JBeaucaire]

Efficiency is a measure of comprehension. There are single lines of code that can take congruent data in a column and transpose the values into congruent cells in a row:

Please Login or Register  to view this content.

But your example shows how sometimes you have to settle for longer, more surgical code that has the benefit of "ease of maintenance"

Please Login or Register  to view this content.

[CKRebel]

Perfect. I used the word "effiecient" to describe the manner in which VBA performs the function I need, while avoiding a waste of time or effort. The way you specified above in your code fulfills both objectives, and again I thank you for the assistance.

[CKRebel]

For future reference to anyone needing similar help as I did, I have provided (below) the code I wrote to do the converse of the before-mentioned issue. This code was designed to remove the previous selection from the menu in the case of a user inputting the wrong data and needing to remove that selection. The code is initiated by clicking "CommandButton5". JBeaucaire, feel free to comment back to provide any feedback on more simple ways to do so for those that may need do write similar code.

Please Login or Register  to view this content.

[JBeaucaire]

As an exercise in efficiency, adjacent cells call almost always be commanded together. Since D:I are adjacent cells:

Please Login or Register  to view this content.

Another way:

Please Login or Register  to view this content.