data transfer to worksheet

[monty toler]

Mr excel,

iam tring to send data from a vbuserform (textbox) to a xlworksheet which is no problem, but when i send the next set of data it overwrites the first. I need the second & third and all other entrys to move down to the next row. I have requested this before so if you have allready recieved this question disregard this one iam not sure if i send the first one the right way. Iam a novice so any help would be appreciated.


thanks monty Toler

 

[Smitty]

Welcome to the Board!

Here's a method for moving date to the next empty row:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> LastRow = Range("A65536").End(xlUp)
        
        <SPAN style="color:#00007F">With</SPAN> LastRow
            .Offset(1, 0) = TextBox1
            .Offset(1, 1) = TextBox2
            .Offset(1, 2) = TextBox3
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[monty toler]

thanks smitty i'll let know if i have any more problems which iam sure i will.


If their is any books you would recommend for a beginner please let know because i dont have anyone to ask question
or turn to help.


thanks
monty toler

 

[Smitty]

John Walkenbach's Excel Power Programming with VBA is a great resource. You can check it out and get some good VBA tips at www.j-walk.com.

Chip Pearson also has good tips at www.cpearson.com and www.vbaexpress.com is a good place for VBA questions, but you can almost always get a question answered here.

Smitty

 

[monty toler]

reset

smitty,
thanks for the help on moving to the next row just what i needed
i have another problem after entering data from A1 to A31 i need to reset or start back at A1 i can't seem to acheive this could you help me ?



thanks
monty toler

 

[Smitty]

Can you post the code that you've got so far?

I imagine you could use a change event to reset once you get to A32 but you'd need to give some specifics as to what's going to cause the reset.

Smitty

 

[monty toler]

reset

Smitty sorry i should have posted the code the first time.
Dim data as interger
data = textbox1
Dim lastrow as object
Set lastrow = Range("A31").End(xlup)
with lastrow
.offset(1,0) = data
End With


thanks
Monty Toler

 

[Norie]

How about adapting pennysaver's code:

Private Sub CommandButton1_Click()
Dim NextRow As Long

        NextRow = Range("A65536").End(xlUp).Row+1
        
        If NextRow = 32 Then NextRow = 1
        
        Range("A" & NextRow) = textbox1.Value               
End Sub