UserForm To A Range
 

Hello.

I am trying to take a value from a UserForm Combo box and place it into a
range. The UserForm may be chosen many times, so I need a procedure that
places the value in the next empty space below the previous value.

Getting the value into the range isn't a problem. It's getting to step to
the next cell that seems to be causing me grief.
Here's what I have so far:

'Place Value in first cell
If Sheets("Data").Cells(Row????, 144).Value = "" Then
Sheets("Data").Cells(Row????, 144).Value = SKU.Value

Else

'If first cell is full, move down to next cell.
If Sheets("Data").Cells(Row????, 144).Value < "" Then
Sheets("Data").Cells(Row???, 144).Value = SKU.Value
End If
End If

I need to know how to define the Row variable so it will work. (Or perhaps
some kind of Loop or For Next Statement would be better.... don't know.) Am
I making any sense?

I do not want to use the ActiveCell Property at all, since this sheet will
not be active. (I also can do this procedure with ActiveCell without a
problem.)

Can anyone help? I'd appreciate it.

Thanks.

Dan

UserForm To A Range
 

assuming that the data goes into a growing column

Sheets("Data").Cells(1, 144).End(xlDown).Offset
(1,0).Value = _
SKU.Value


This says that starting from row 1 go down to the last
cell in the column, then in the cell below that, add the
combo value.

Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Hello.

I am trying to take a value from a UserForm Combo box
and place it into a
range. The UserForm may be chosen many times, so I need
a procedure that
places the value in the next empty space below the
previous value.

Getting the value into the range isn't a problem. It's
getting to step to
the next cell that seems to be causing me grief.
Here's what I have so far:

'Place Value in first cell
If Sheets("Data").Cells(Row????, 144).Value = "" Then
Sheets("Data").Cells(Row????, 144).Value = SKU.Value

Else

'If first cell is full, move down to next cell.
If Sheets("Data").Cells(Row????, 144).Value < "" Then
Sheets("Data").Cells(Row???, 144).Value = SKU.Value
End If
End If

I need to know how to define the Row variable so it will
work. (Or perhaps
some kind of Loop or For Next Statement would be
better.... don't know.) Am
I making any sense?

I do not want to use the ActiveCell Property at all,
since this sheet will
not be active. (I also can do this procedure with
ActiveCell without a
problem.)

Can anyone help? I'd appreciate it.

Thanks.

Dan


.

UserForm To A Range
 

Just to add:
Unless you know row1 and row2 have values, this may produce unexpected
results. You would need to check those conditions.

With Userform1
if Application.CountA(Sheets("Data").Cells(1,144).Res ize(2)) = 2 then
Sheets("Data").Cells(1,144).End(xldown)(2) = .Textbox1.Text
elseif IsEmpty(Sheets("Data").Cells(1,144)) then
Sheets("Data").Cells(1,144).Value = .Textbox1.Text
else
sheets("Data").Cells(2,144).Value = .Textbox2.Text
End if
End With

--
Regards,
Tom Ogilvy


"Patrick Molloy" wrote in message
...
assuming that the data goes into a growing column

Sheets("Data").Cells(1, 144).End(xlDown).Offset
(1,0).Value = _
SKU.Value


This says that starting from row 1 go down to the last
cell in the column, then in the cell below that, add the
combo value.

Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
Hello.

I am trying to take a value from a UserForm Combo box
and place it into a
range. The UserForm may be chosen many times, so I need
a procedure that
places the value in the next empty space below the
previous value.

Getting the value into the range isn't a problem. It's
getting to step to
the next cell that seems to be causing me grief.
Here's what I have so far:

'Place Value in first cell
If Sheets("Data").Cells(Row????, 144).Value = "" Then
Sheets("Data").Cells(Row????, 144).Value = SKU.Value

Else

'If first cell is full, move down to next cell.
If Sheets("Data").Cells(Row????, 144).Value < "" Then
Sheets("Data").Cells(Row???, 144).Value = SKU.Value
End If
End If

I need to know how to define the Row variable so it will
work. (Or perhaps
some kind of Loop or For Next Statement would be
better.... don't know.) Am
I making any sense?

I do not want to use the ActiveCell Property at all,
since this sheet will
not be active. (I also can do this procedure with
ActiveCell without a
problem.)

Can anyone help? I'd appreciate it.

Thanks.

Dan


.