For each...next statement

[tina_303]

I am working an application for renting dvds. Right now I'm trying to create an event procedure for Checking Out movies, and would like some help.

What I would like it to do is the following:
After the movie(s) is select from the first list box, the result will display in the second list box. Then the user will clicks on the CALCULATE TOTAL amount command button and a user form will open up to do the following. (Up to here, I am good).

'find last cell in customer file worksheet
'if optRent is selected when Calculate Total button is click:
'input box to get Customer ID
'then take that customer Id and the movies from the MoviesSelect list box (aka: lstMoviesSelect)
'and make it the value of the last cell in Customer file worksheet for record
'in addition, the appriate amount owe should be calculated and display in the lblTotal
'BUT if optBuy is selected when Calculate Total button is click:
'Display the total amount owe only


Below is what I got going so far. I know that there are a lot of errors at the moment, so I'm hoping someone could help me out.

Private Sub cmdCalculate_Click()
Dim strCustomerId As String, rngData As Range, rngCell As Range, shtCustomerFile As Worksheet
Set shtCustomerFile = Application.Workbooks("project-let").woksheets("customer file")
FinalRow = shtCustomerFile.Cells(65536, 1).End(xlUp).Row
Set rngData = _
Application.Workbooks("project-let.xlsm").Worksheets("movie list").Range("a11").CurrentRegion
strMovieName = lstMoviesSelect.Value
For Each strMovieName In lstMoviesSelect
If optRent.Value = True Then
'input box for customer Id
strCustomerId = InputBox(prompt:="Enter customer ID", Title:="Customer ID")
shtCustomerFile.Cells(FinalRow, 1).End(xlUp).Row.Value = strMovieName And strCustomerId
lblTotal = rngData.Offset(columnoffset:=4).Value
Exit For
Else
If optBuy.Value = True Then
lblTotal = rngData.Offset(columnoffset:=5).Value
End If
End If
End Sub

I don't know if my description is clear enough to understand so I have attached a screen shot of what I got going so far. Please give me some help.
Thanks,
TL

[Bob Phillips]

Can you post your workbook to work on?

[tina_303]

Please see attachment.

[Bob Phillips]

Take a look at this and see if the check-out option works correctly.

[tina_303]

Wow, thank you so much. For the most part, things look good but I still get a few minor errors.

Under the "Check In" procedure, there's a "compile error: variable not define" for the line

For i = 0 To lstMoviesSelect.ListCount - 1

When "checking out", it displayed the Total Amount for "Buy (column 5's values)" even though optRent was selected. It also displayed 0 if optBuy was selected, it should display the values in column 5. "nTotal = nTotal + rngData.Offset(, 5).Value" looks right but I don't know why it's not working.

[tina_303]

For the "variable undefined" error, I had try adding: Dim i As Long

but then I get another error: Invalid use of Null for:

strMovieName = lstMoviesSelect.Value

[Bob Phillips]

I haven't done the Check-in bit yet, just out, I'll get onto it today.

[Bob Phillips]

Here is another update for you to try.

Note that you can select more than one movie at a time from the list.

[tina_303]

Wow, unbelievable. It worked great. I really don't know how I can thank you for all the time and effort you put in. Thanks a lot, and hope you have a great Thanksgiving.

Tina

[tina_303]

The cmdCalculate_Click() right now works perfectly fine, but I was if I can use vLookup to find nTotal.

So, instead of:

Private Sub cmdCalculate_Click()
'declare variables and assign address
Dim strCustomerId As String
Dim rngData As Range
Dim rngCell As Range
Dim nTotal As Double
Dim i As Long

FinalRow = wsCustomer.Cells(wsCustomer.Rows.Count, "A").End(xlUp).Row
'Calculate total amount owe for all check out movies if rent or buy was selected
For Each strMovieName In arySelected
Set rngData = wsMOvies.Columns(1).Find(strMovieName)
If optRent.Value = True Then
nTotal = nTotal + rngData.Offset(, 3).Value 'Total if optRent selected
FinalRow = FinalRow + 1
Else
nTotal = nTotal + rngData.Offset(, 4).Value 'total if optBuy selected
End If
Next strMovieName
Me.lblTotal.Caption = Format(nTotal, "#,##0.00")
End Sub


Can I do something like:

Private Sub cmdCalculate_Click()
'declare variables and assign address
Dim strCustomerId As String
Dim rngData As Range
Dim rngCell As Range
Dim nTotal As Double
Dim i As Long
Dim rng As Range
FinalRow = wsCustomer.Cells(wsCustomer.Rows.Count, "A").End(xlUp).Row
Set rng = wsMOvies.Range("a11").CurrentRegion
'Calculate total amount owe for all check out movies if rent or buy was selected
For Each strMovieName In arySelected
Set rngData = wsMOvies.Columns(1).Find(strMovieName)
If optRent.Value = True Then
nTotal = Application.WorksheetFunction.VLookup(strMovieName, rng, 3, False)
FinalRow = FinalRow + 1
Else
nTotal = Application.WorksheetFunction.VLookup(strMovieName, rng, 4, False) 'total if optBuy selected
End If
Next strMovieName
Me.lblTotal.Caption = Format(nTotal, "#,##0.00")
End Sub

[tina_303]

Never mind. I figured it out.