PDA

View Full Version : Solved: populating a worksheet



maryam
02-13-2007, 07:09 PM
there are different input forms with which user input different data. I want to save all data together in a worksheet. So for each form I need one column in a worksheet. what are the codes to make clear which column is empty. I mean from A to Z and ... . So the first data will be saved in column A and then when A is full it will go to B and so on.

lucas
02-13-2007, 07:55 PM
Maryam,
I don't know if it will help or not but attached is an example of a simple data input form using textboxes. I think it would be easier to upload it than to try to explain how to insert a form and add the textboxes and code. Mostly look at the code for the userform button click event. Should get you started and comboxes, etc. can be used. Hope this helps.

maryam
02-13-2007, 09:39 PM
can we change the codes for last column. I want to save in columns.

maryam
02-13-2007, 10:00 PM
How to change these for column?
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text

IV is the last column, but when i replace a65536 with IV is gives debug!

mdmackillop
02-14-2007, 01:04 AM
Set LastColumn = Sheet1.Range("IV1").End(xlToleft)

maryam
02-14-2007, 01:53 AM
yes.
Set LastRow = Sheet1.Range("IV1").End(xlToLeft)

LastRow.Offset(0, 1).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(2, 1).Value = TextBox3.Text

Thank you

mdmackillop
02-14-2007, 06:01 AM
Hi Maryam,
Using a variable called LastRow to refer to columns looks like a recipe for disaster!

lucas
02-14-2007, 07:03 AM
Hi Maryam,
I agree with Malcolm. Since you have changed from rows to columns you should change your variable in your code from lastrow to lastcolumn or something simialar. Just do a find and replace on the sub.

maryam
02-14-2007, 09:49 PM
lol! sorry. Yes:

Set LastColumn = Sheet1.Range("IV1").End(xlToLeft)

LastColumn.Offset(0, 1).Value = TextBox1.Text
LastColumn.Offset(1, 1).Value = TextBox2.Text
LastColumn.Offset(2, 1).Value = TextBox3.Text

Edit by Lucas: code tags added

lucas
02-14-2007, 09:52 PM
Be sure to mark your thread solved Maryam using the thread tools at the top of the page. You can select your code and hit the button marked VBA to make your post look like it does in the vbe like Malcolms code in post #5 of this thread.

gjavier
03-29-2007, 10:47 AM
Thank you guys this is just what I was looking for....