VBA Code for writing Userform data to the next available column

[JPMCK4713]

Hi all,

First time poster, thanks for having me.

I've created an excel database that uses userforms to gather information from individuals. This userform information is then added into cells on a separate worksheet.

The way that the information is being added on the worksheet is in rows. I need it to write the information in columns.

So I have me JPMCK4713. I would complete the userform. Once completed it then converts that information under a heading on my worksheet in rows only. So a second person completing the Userform would complete their information and that would appear in the row under my previous submission.

I'm trying to get it so that the information is placed in the column next to mine. So my information goes in say Column D, the next persons information would then be placed in Column E.

This is a brief view of the code I'm currently using.

Sub Report()


Dim LastRow As Long, ws As Worksheet


Set ws = Sheets("Sheet1")


LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row


ws.Range("A" & LastRow).Value = UserForm1.TextBox1.Text
ws.Range("B" & LastRow).Value = UserForm1.TextBox2.Text
ws.Range("C" & LastRow).Value = UserForm1.TLSelection.Text
ws.Range("D" & LastRow).Value = UserForm3.ComboBox1.Text

Any advice, tips or solutions, greatly appreciated.

I'm also looking for code that would input a date into my worksheet, without the user having to input the date. So a code that automatically picks up the date and pastes that information into a cell in each users column?

Thank you all.

 

[PhilS2520]

Hi JPMCK4713,

And welcome to this forum. Great post with a lot of details.

I would suggest changing/ adding to this line

 LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row 

to move to the right and Find the next empty column.

LastCol = ws.Range("A" & Columns.Count).End(xlToRight).Column + 1 'Finds the last blank column 

might do the trick?

Let us know if that works.

 

[PhilS2520]

Sorry to add the date you can use the NOW() and format it the way you like.

 

[JPMCK4713]

Thanks Phils2520, I'm not sure why but it's not writing anything now?

I've tinkered with the code a little earlier on to see if that changed and still nothing;

Sub Report()


Dim LastCol As Long, ws As Worksheet


Set ws = Sheets("Raw Data")


LastCol = ws.Range("A" & Columns.Count).End(xlToRight).Column + 1 'Finds the last blank column

ws.Range("A" & LastCol).Value = UserForm1.TextBox1.Text
ws.Range("B" & LastCol).Value = UserForm1.TextBox2.Text
ws.Range("C" & LastCol).Value = UserForm1.TLSelection.Text
ws.Range("D" & LastCol).Value = UserForm3.ComboBox1.Text
ws.Range("E" & LastCol).Value = UserForm3.ComboBox2.Text

The first possible cell it can write in is Cell D1, the previous cells have headings in them and need to remain in place, cell D1 is where the date information needs to be inputted.

The date point, would it be best to put that code below the code for the first box on my very first userform, albeit not actually displayed to the user?

hope that makes sense and thank you for the help

Jon


Thanks

Jon

 

[JPMCK4713]

Is anyone able to help with this please? I feel like i'm

thank you

 

[Fluff]

Change xlToRight to xlToLeft

 

[JPMCK4713]

Change xlToRight to xlToLeft

Thanks Fluff, it's still writing horizontally not vertically within a column.

The first cell that information needs to appear in is cell D1 and then the rest of the userform data appearing underneath it in that column. Once that column down to say row 200 has been filled, I need for the code to then write in cell E1 and down, then cell F1 and down and so on after each person has completed the userform.

Really grateful for your help.

 

[Fluff]

Try it like

ws.Cells(1, lastcol).Value = UserForm1.TextBox1.Text
ws.Cells(2, lastcol).Value = UserForm1.TextBox2.Text