New Row

zaahir

Board Regular
Joined
Oct 11, 2006
Messages
55
Hi
I use code to paste info off a userform into a spreadsheet, save the data and close the sheet again. Now i need to the code to select the next row to the data to a new row i.e.

!st entry goes into Col A, b, c, d, e Row 1. I need the code to select row2 & add the data there i.e ColA, b, c, d, e row2 there after row3 then 4 etc.

i now keep overwriting row1...
Please Help!!!!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,561
Office Version
  1. 365
Platform
  1. Windows
zaahir

Are you able to show us your existing code?
 

zaahir

Board Regular
Joined
Oct 11, 2006
Messages
55
hi
Sure.....

Private Sub cmdAccept_Click()
Workbooks.Open Filename:= _
"\\User-reception\all cmf docs\CMFS Electronic System\DataBase\CMFClientInfo.xls"
Sheets("CMFClientInfo").Cells(3, 1) = Me.txtName.Text
ActiveWorkbook.Save
ActiveWorkbook.Close
txtName.Text = ""
Unload Me
frmNewClientInfo.Hide
End Sub

The offset Code returns "= missing"
pls help me with a correct code to select the next row & enter data.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,561
Office Version
  1. 365
Platform
  1. Windows
zaahir

For the second and subsequent entries, instead of
Sheets("CMFClientInfo").Cells(3, 1) = Me.txtName.Text
try
Sheets("CMFClientInfo").Cells(1, 1).End(xlDown).Offset(1, 0) = Me.txtName.Text

The offset Code returns "= missing"
I cannot see what this is referring to. ??
 

zaahir

Board Regular
Joined
Oct 11, 2006
Messages
55

ADVERTISEMENT

thanx
but as i said before, i need this to function to execute and run thru afew columns.
Using the above off-set moves from row1 col A to row2 colA, but not in the other colums. it diverts back to row1col b then overwrites all the previous data.

here is the entire code!!!!
Sheets("CMFClientInfo").Cells(1, 1).End(xlDown).Offset(1, 0) = Me.txtName.Text
Sheets("CMFClientInfo").Cells(1, 2).End(xlDown).Offset(1, 0) = Me.txtStreet1.Text
Sheets("CMFClientInfo").Cells(1, 3).End(xlDown).Offset(1, 0) = Me.txtStreet2.Text
Sheets("CMFClientInfo").Cells(1, 4).End(xlDown).Offset(1, 0) = Me.txtStreet3.Text
Sheets("CMFClientInfo").Cells(1, 5).End(xlDown).Offset(1, 0) = Me.txtStreetCode.Text
Sheets("CMFClientInfo").Cells(1, 6).End(xlDown).Offset(1, 0) = Me.txtPost1.Text
Sheets("CMFClientInfo").Cells(1, 7).End(xlDown).Offset(1, 0) = Me.txtPost2.Text
Sheets("CMFClientInfo").Cells(1, 8).End(xlDown).Offset(1, 0) = Me.txtPost3.Text
Sheets("CMFClientInfo").Cells(1, 9).End(xlDown).Offset(1, 0) = Me.txtPostCode.Text
Sheets("CMFClientInfo").Cells(1, 10).End(xlDown).Offset(1, 0) = Me.txtContact.Text
Sheets("CMFClientInfo").Cells(1, 11).End(xlDown).Offset(1, 0) = Me.txtVAT.Text
Sheets("CMFClientInfo").Cells(1, 12).End(xlDown).Offset(1, 0) = Me.txtTel.Text
Sheets("CMFClientInfo").Cells(1, 13).End(xlDown).Offset(1, 0) = Me.txtFax.Text

Please help!!!!!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,561
Office Version
  1. 365
Platform
  1. Windows
zaahir

Not tested, but see if this helps

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> myText(13) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> myRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

myText(1) = Me.txtName.Text
myText(2) = Me.txtStreet1.Text
myText(3) = Me.txtStreet2.Text
<SPAN style="color:#007F00">' Add the other 10 values here</SPAN>

myRow = Sheets("CMFClientInfo").Cells(1, 1).End(xlDown).Row + 1
<SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> 3 <SPAN style="color:#007F00">'Change this to 13 when all the text entries above have been added</SPAN>
    Cells(myRow, c).Value = myText(c)
<SPAN style="color:#00007F">Next</SPAN> c</FONT>
 

Forum statistics

Threads
1,137,335
Messages
5,680,893
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top