Several If Conditions In a Userform

[Dean England]

I've written a simple Userform to enter Daily Sales for our restaurant. It works to a point, but I have a condition that I can't get my head around, with my limited knowledge. I have a couple of books on VBA on order, but that doesn't help much now.

My first active cell in the worksheet is C7 and column C is the daily lunch head count. My problem is that we don't serve lunch every day, but we always serve dinner. That dinner count is in column D, so I need to test column C and column D in order to enter the figures. I thought of swapping the columns, but my little buddies are always leary of changes. Here's my code and it's simple. One of these days, when I learn more, it might be more complex.

Private Sub cmdEntry_Click()

ActiveWorkbook.Sheets("Sales").Activate

Range("C7").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = tboLunch.Value

ActiveCell.Offset(0, 1) = tboDinner.Value

ActiveCell.Offset(0, 2) = tboLFood.Value

ActiveCell.Offset(0, 3) = tboDFood.Value

ActiveCell.Offset(0, 4) = tboDayBar.Value

ActiveCell.Offset(0, 5) = tboNiteBar.Value

ActiveCell.Offset(0, 6) = tboDWine.Value

ActiveCell.Offset(0, 7) = tboNWine.Value

ActiveCell.Offset(0, 8) = tboSTax.Value

ActiveCell.Offset(0, 9) = tboEDisc.Value

Range("C7").Select

End Sub

I'd really appreciate any help and then I'll know a little more than I know now.

Thanks in advance,

Dean

[Mallycat]

My problem is that we don't serve lunch every day, but we always serve dinner. That dinner count is in column D, so I need to test column C and column D in order to enter the figures.

So what are you trying to do? Do you want to leave the C column blank if there is no lunch, and still fill the D column? That would mean your D column will be full of data, but there will be blanks in the C column.

To do this, make a few simple changes to your code.

1. Search down the D column for the last row of data
change Range("C7").Select to Range("D7").Select

2. Move back to column C
after the line Loop Until IsEmpty(ActiveCell) = True, enter the following line

selection.offset(0,-1).select

This will put you back in column C.

Everthing else should be OK.

Matt

[Bryan Hessey]

I've written a simple Userform to enter Daily Sales for our restaurant. It works to a point, but I have a condition that I can't get my head around, with my limited knowledge. I have a couple of books on VBA on order, but that doesn't help much now.

My first active cell in the worksheet is C7 and column C is the daily lunch head count. My problem is that we don't serve lunch every day, but we always serve dinner. That dinner count is in column D, so I need to test column C and column D in order to enter the figures. I thought of swapping the columns, but my little buddies are always leary of changes. Here's my code and it's simple. One of these days, when I learn more, it might be more complex.



I'd really appreciate any help and then I'll know a little more than I know now.

Thanks in advance,

Dean

Try

Please Login or Register  to view this content.

[Dean England]

Thanks Matt and Bryan for the responses. Used your solution Matt. Works fine. Thanks again.