Userform data to the next available row

[michaelberrier]

I use this code to enter the data from a Userform into the next empty row on
a sheet:

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

I would like to be able to use two columns to maintain two seperate
databases on the same sheet and would like to find the next empty "row" in
each column based on a conditional value. The code now looks like this:
If Me.HITCHBOX.Value = "A" Then
ws.Cells(iRow, 1).Value = Me.LASTNAME.Value
ws.Cells(iRow, 2).Value = Me.FIRSTNAME.Value
ws.Cells(iRow, 3).Value = Me.RANKBOX.Value
End If

I would like to enter the same values in Columns D-F if the value in
HITCHBOX is "B", even if Columns A-C on the same row are full.

thanks to all.

[Dave Peterson]

You have a reply at your other post.

But that said, I think you'll end up being happier putting the data in 4
columns--add another column that you can use as an indicator (HitchBox). You'll
be able to do more things later (sort, mail merge, autofilter, ...).



michaelberrier wrote:
>
> I use this code to enter the data from a Userform into the next empty row on
> a sheet:
>
> iRow = ws.Cells(Rows.Count, 1) _
> .End(xlUp).Offset(1, 0).Row
>
> I would like to be able to use two columns to maintain two seperate
> databases on the same sheet and would like to find the next empty "row" in
> each column based on a conditional value. The code now looks like this:
> If Me.HITCHBOX.Value = "A" Then
> ws.Cells(iRow, 1).Value = Me.LASTNAME.Value
> ws.Cells(iRow, 2).Value = Me.FIRSTNAME.Value
> ws.Cells(iRow, 3).Value = Me.RANKBOX.Value
> End If
>
> I would like to enter the same values in Columns D-F if the value in
> HITCHBOX is "B", even if Columns A-C on the same row are full.
>
> thanks to all.

--

Dave Peterson

[michaelberrier]

Not sure I understand what you mean by adding the 4th column as the
indicator. Are you saying scrap the two colunm approach and just list it all
in the same four columns?

"Dave Peterson" wrote:

> You have a reply at your other post.
>
> But that said, I think you'll end up being happier putting the data in 4
> columns--add another column that you can use as an indicator (HitchBox). You'll
> be able to do more things later (sort, mail merge, autofilter, ...).
>
>
>
> michaelberrier wrote:
> >
> > I use this code to enter the data from a Userform into the next empty row on
> > a sheet:
> >
> > iRow = ws.Cells(Rows.Count, 1) _
> > .End(xlUp).Offset(1, 0).Row
> >
> > I would like to be able to use two columns to maintain two seperate
> > databases on the same sheet and would like to find the next empty "row" in
> > each column based on a conditional value. The code now looks like this:
> > If Me.HITCHBOX.Value = "A" Then
> > ws.Cells(iRow, 1).Value = Me.LASTNAME.Value
> > ws.Cells(iRow, 2).Value = Me.FIRSTNAME.Value
> > ws.Cells(iRow, 3).Value = Me.RANKBOX.Value
> > End If
> >
> > I would like to enter the same values in Columns D-F if the value in
> > HITCHBOX is "B", even if Columns A-C on the same row are full.
> >
> > thanks to all.
>
> --
>
> Dave Peterson
>

[Dave Peterson]

It looked like you were putting somethings in A:C and some things in D:F based
on the Hitchbox.

I'd put the data in A:C and the hitchbox code in column D.

(or some variation of that)

But yep, I'm saying that life will be simpler if you keep each "record" on its
own row--with nothing else on that row (and try to keep all the data for each
record on a single row. Spanning rows is a pain, too.)

michaelberrier wrote:
>
> Not sure I understand what you mean by adding the 4th column as the
> indicator. Are you saying scrap the two colunm approach and just list it all
> in the same four columns?
>
> "Dave Peterson" wrote:
>
> > You have a reply at your other post.
> >
> > But that said, I think you'll end up being happier putting the data in 4
> > columns--add another column that you can use as an indicator (HitchBox). You'll
> > be able to do more things later (sort, mail merge, autofilter, ...).
> >
> >
> >
> > michaelberrier wrote:
> > >
> > > I use this code to enter the data from a Userform into the next empty row on
> > > a sheet:
> > >
> > > iRow = ws.Cells(Rows.Count, 1) _
> > > .End(xlUp).Offset(1, 0).Row
> > >
> > > I would like to be able to use two columns to maintain two seperate
> > > databases on the same sheet and would like to find the next empty "row" in
> > > each column based on a conditional value. The code now looks like this:
> > > If Me.HITCHBOX.Value = "A" Then
> > > ws.Cells(iRow, 1).Value = Me.LASTNAME.Value
> > > ws.Cells(iRow, 2).Value = Me.FIRSTNAME.Value
> > > ws.Cells(iRow, 3).Value = Me.RANKBOX.Value
> > > End If
> > >
> > > I would like to enter the same values in Columns D-F if the value in
> > > HITCHBOX is "B", even if Columns A-C on the same row are full.
> > >
> > > thanks to all.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[michaelberrier]

Dave,
Thanks for the help. Because keeping the two list independant was important
to me, I moved the second group to another sheet and that seems to work very
well for me.

"Dave Peterson" wrote:

> It looked like you were putting somethings in A:C and some things in D:F based
> on the Hitchbox.
>
> I'd put the data in A:C and the hitchbox code in column D.
>
> (or some variation of that)
>
> But yep, I'm saying that life will be simpler if you keep each "record" on its
> own row--with nothing else on that row (and try to keep all the data for each
> record on a single row. Spanning rows is a pain, too.)
>
> michaelberrier wrote:
> >
> > Not sure I understand what you mean by adding the 4th column as the
> > indicator. Are you saying scrap the two colunm approach and just list it all
> > in the same four columns?
> >
> > "Dave Peterson" wrote:
> >
> > > You have a reply at your other post.
> > >
> > > But that said, I think you'll end up being happier putting the data in 4
> > > columns--add another column that you can use as an indicator (HitchBox). You'll
> > > be able to do more things later (sort, mail merge, autofilter, ...).
> > >
> > >
> > >
> > > michaelberrier wrote:
> > > >
> > > > I use this code to enter the data from a Userform into the next empty row on
> > > > a sheet:
> > > >
> > > > iRow = ws.Cells(Rows.Count, 1) _
> > > > .End(xlUp).Offset(1, 0).Row
> > > >
> > > > I would like to be able to use two columns to maintain two seperate
> > > > databases on the same sheet and would like to find the next empty "row" in
> > > > each column based on a conditional value. The code now looks like this:
> > > > If Me.HITCHBOX.Value = "A" Then
> > > > ws.Cells(iRow, 1).Value = Me.LASTNAME.Value
> > > > ws.Cells(iRow, 2).Value = Me.FIRSTNAME.Value
> > > > ws.Cells(iRow, 3).Value = Me.RANKBOX.Value
> > > > End If
> > > >
> > > > I would like to enter the same values in Columns D-F if the value in
> > > > HITCHBOX is "B", even if Columns A-C on the same row are full.
> > > >
> > > > thanks to all.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>