CODEWITHSUNDEEP
excelvba
Israel Gonzales
Israel Gonzales

Reputation: 29

VBA textbox to cell

I have a input user form in excel where a user enters data into a three different textbox. This is my code

      Private Sub Process_Click()

     Worksheets("1").Range("A1").Value = Date.Value
     Worksheets("1").Range("B1").Value = Name.Value
     Worksheets("1").Range("C1").Value = Amount.Value


     End Sub

So the first time I press process it will write the data into those cells A1,b1, and C1. What i want to do is that everytime I open the form and click process on my form I would like for those values to go down a row for example the next time i click process it will go to A2,b2,and c2, and so on.

Upvotes: 0

Views: 10567

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169494

This is similar to Barranka's answer.

The basic idea is the same. You check for the last-populated row, and append your data one row lower than that.

The method used here to find the last-populated row might be slightly more efficient than using a while loop, but you should test both to see for yourself:

Private Sub Process_Click()
    Dim appendRow As Long

    ' add 1 to get the row after the last-populated row
    appendRow = Worksheets("1").Cells(Rows.Count,1).End(XlUp).Row + 1

    Worksheets("1").Range("A" & appendRow).Value = Date.Value
    Worksheets("1").Range("B" & appendRow).Value = Name.Value
    Worksheets("1").Range("C" & appendRow).Value = Amount.Value
 End Sub

Upvotes: 2

Barranka
Barranka

Reputation: 21067

First, you need to know which was the last filled cell. This can be done easily navigating the range. Assuming that all three columns are filled with data, you can read only one (let's say A) to get the last row:

Dim i as Integer
i = 1
while Worksheets("1").Range("A" & i).Value <> "" or Worksheets("1").Range("A" & i).Value <> 0
    i = i + 1
wend

Now that you know the last row populated:

 Worksheets("1").Range("A" & i).Value = Date.Value
 Worksheets("1").Range("B" & i).Value = Name.Value
 Worksheets("1").Range("C" & i).Value = Amount.Value

It's just a way to solve it, and it works if your data set is not meant to be really big. Downside is, if you have a very long table, the while can take some time to track the last populated row.

Hope this helps you

Upvotes: 0

Related Questions