Userform finding next row to place data

Hi there
I'm new to VBA. I know just enough to be dangerous to myself.
I have been creating a work book over the last 10 years that I use in my
business and now want to make it user friendly by way of user forms.

Having created a excel userform in vba, I want to use this form for placing
data into a worksheet(s) Each time I open the form I want to place new data
onto the next available empty row.
My form has a mixture of some 20 text boxes, links to other userforms &
lists and I want the information to go into a seperate column but on the same
row. however not all the text boxes etc will have data in them.

Also I want to be able to use the form to edit previous data that has been
entered. For example row 6 may need to have the data edited and I want to be
able to select that by way of code, say Ext002, in the first column.

Your help would be much appreciated.

Hello again,

To find last row with data:

Lastrow=Cells(rows.count,"A").end(xlup).row

Or Nextrow=Cells(rows.count,1).end(xlup).row+1

Both look at column A

To Find row for existing data you could use FIND, MATCH or VLOOKUP depending
on what you want to do.

e.g


myValue="Ext002"
myRow=Application.Match(Myvalue,Range("A:A"),0) <=== Look for MyValue in Col A
If iserror(myrow) then
msgbox "No match for " & myvalue
Else
...... your code
Cells(myrow,3)=textbox3.value <=== update col C row "myrow" with
textbox3 value
End if


HTH


"BrianW" wrote:

> Hi there
> I'm new to VBA. I know just enough to be dangerous to myself.
> I have been creating a work book over the last 10 years that I use in my
> business and now want to make it user friendly by way of user forms.
>
> Having created a excel userform in vba, I want to use this form for placing
> data into a worksheet(s) Each time I open the form I want to place new data
> onto the next available empty row.
> My form has a mixture of some 20 text boxes, links to other userforms &
> lists and I want the information to go into a seperate column but on the same
> row. however not all the text boxes etc will have data in them.
>
> Also I want to be able to use the form to edit previous data that has been
> entered. For example row 6 may need to have the data edited and I want to be
> able to select that by way of code, say Ext002, in the first column.
>
> Your help would be much appreciated.

Hi there Toppers
Nice to hear from you again.
Thankyou. Once again your help is much appreciated.
Cheers
"Toppers" wrote:

> Hello again,
>
> To find last row with data:
>
> Lastrow=Cells(rows.count,"A").end(xlup).row
>
> Or Nextrow=Cells(rows.count,1).end(xlup).row+1
>
> Both look at column A
>
> To Find row for existing data you could use FIND, MATCH or VLOOKUP depending
> on what you want to do.
>
> e.g
>
>
> myValue="Ext002"
> myRow=Application.Match(Myvalue,Range("A:A"),0) <=== Look for MyValue in Col A
> If iserror(myrow) then
> msgbox "No match for " & myvalue
> Else
> ...... your code
> Cells(myrow,3)=textbox3.value <=== update col C row "myrow" with
> textbox3 value
> End if
>
>
> HTH
>
>
> "BrianW" wrote:
>
> > Hi there
> > I'm new to VBA. I know just enough to be dangerous to myself.
> > I have been creating a work book over the last 10 years that I use in my
> > business and now want to make it user friendly by way of user forms.
> >
> > Having created a excel userform in vba, I want to use this form for placing
> > data into a worksheet(s) Each time I open the form I want to place new data
> > onto the next available empty row.
> > My form has a mixture of some 20 text boxes, links to other userforms &
> > lists and I want the information to go into a seperate column but on the same
> > row. however not all the text boxes etc will have data in them.
> >
> > Also I want to be able to use the form to edit previous data that has been
> > entered. For example row 6 may need to have the data edited and I want to be
> > able to select that by way of code, say Ext002, in the first column.
> >
> > Your help would be much appreciated.