Interactive Data Entry in Excel

I'm planning a multipage UserForm (like a wizard) where the user will be asked to enter data that will be inserted in specific cells of specific worksheets in a multi-sheet workbook. In other words, one piece of data goes to a single cell in one worksheet and no other. After the data is entered through one trip through the "wizard", the user will have the option to repeat the process in order to REPLACE previously entered data.

I have been given examples of code which was obviously designed for entering data to contiguous rows, row after row, forming a data list. I couldn't figure out how to convert this code to my situation where no list exists and where each bit of information is inserted in one target cell. I need to be able to "aim" the data at a specific cell of a specific worksheet, among perhaps a total of 30 worksheets.

Can someone help me with an appropriate example?

TIA

You can determine the next free row by

Code:

---

Sheets("Sheet1").Range("A65536").End(xlUp).Row + 1

---

Assuming you wanted Col A in this case. Then you can put data in, or pull it out of Range("A" & LastRow). Then just have the code put the data in the columns and sheets you want.

Can you post an example of what you are doing?

I don't have anything meaningful to post yet but I have collected enough snippets of code to give it a try, perhaps tomorrow.

One of the VBA code example that I got had the exact same line of code as in your post for determining the next available row. But why do I need to determine the next available row if I want the data to go to one specific cell?

Let's say that I have a financial evaluation workbook and I want the user to begin by entering a few key parameters: the first question on page 1 of the multipage userform could be: "what is your cost of capital, in %?" Say, the answer is 12% and I want that number to inserted in cell B6 of the worksheet called "Discounted Cash Flow." The next page may ask "what is the estimated acquisition price, in k$?" and I want the answer to be inserted in cell A5 of the worksheet named "Amortization," and so forth. One piece of data (always a number) inserted into a single cell.

Wouldn't something like this do?:

Code:

---

Private Sub CommandButton1_Click()
With ActiveWorkbook
.Worksheets("Discounted Cash Flow").Range("b6").Value = Me.TextBox1.Value
.Worksheets("Amortization").Range("a5").Value = Me.TextBox2.Value
End With
End Sub

---

That would be fine. The reason for using the next available row is that the old data would be saved instead of overwritten each time. If you don't want to save the data, then your code should work just fine.

Note that if the textbox has text use TextBox1.Text to get the value. If it has a number value use Val(TextBox1.Value)

Thanks for your help

Jacob, two more quick questions:

could you clarify a couple of points regarding the code:

Code:

---

Sheets("Sheet1").Range("A65536").End(xlUp).Row + 1

---

Why .Range("A65536")?
What does .End(xlUp) do?

Thanks for your help (and patience!)

Quote:

---

Originally Posted by K. Georgiadis

Why .Range("A65536")?

---

Because that is the bottom-most cell allowed in Excel, in any given column really. That's the very very bottom row. So searching from the bottom up, you're sure to get the last non-contiguous cell in the specified column.

Quote:

---

Originally Posted by K. Georgiadis

What does .End(xlUp) do?

---

It's like if you push and hold your Ctrl key and move around with your arrow keys. It's a navigational aid to go to the first piece of data that Excel can *see* (spaces included). So if you have information between A1 and A1000 but nothing below that, running the above lines together will put your last cell as A1000 (searching from A65536 up nothing is there until row 1000).

HTH

Got it! Thanks