Error: Invalid or Unqualified Reference

kiwidancer · September 7th, 2009, 01:40 PM

I'm trying to add values to a worksheet based on values in another worksheet.

My commissions worksheet has columns with salespeople, customer group, and product categories as well as an actual price and a minimum price.
My commission percents worksheet is essentially a table with commission percents based on these values. See http://img.photobucket.com/albums/v6.../commtable.jpg

I want to obtain the percentage from the table that corresponds to the salesperson, group, and category in each line of the commission spreadsheet.

Here is my code:

Code:

Sub calculateComm()
    Dim i As Integer
    Dim comms, pcts As Worksheet
    Dim sp, group, category As String
    Dim tbl, cel As Range
    Set comms = Workbooks("new comm macro").Worksheets("Commissions")
    Set pcts = Workbooks("new comm macro").Worksheets("Commission Percents")
    Set tbl = pcts.Cells(1, 1).CurrentRegion
    i = 4
    For i = 4 To comms.Cells(4, 1).CurrentRegion.Rows.Count + 4
        sp = Trim(comms.Cells(i, 3).Text)
        group = Trim(comms.Cells(i, 4).Text)
        category = Trim(comms.Cells(i, 5).Text)
        For Each cel In tbl.Cells
            If (pcts.Cells(.Row, 1) = sp And pcts.Cells(.Row, 2) = group And pcts.Cells(1, .Column) = category) Then
                If (comms.Cells(i, 12) <= comms.Cells(i, 10)) Then
                    comms.Cells(i, 18) = .Value
                  Else
                    comms.Cells(i, 18) = .Value / 2
                End If
                Exit For
            End If
        Next cel
        i = i + 1
    Next i
End Sub

anyone know why I'm getting this error?

Thx for all the help in advance :)

sektor · September 8th, 2009, 01:18 AM

If to take into account syntactic errors, as far as I see, you got two identical errors, namely: in line comms.Cells(i, 18) = .Value.

You use period notation, hence you gotta have "With object" statement. For example:

With Range("A1")
iRow = .Value
End With

I don't see qualifying object with With. I hope, second line you'd find yourself. :)

kiwidancer · September 8th, 2009, 10:19 AM

thank you Sektor, worked perfectly :) sorry i'm a nub

sektor · September 9th, 2009, 12:26 AM

Not at all! When I began programming, I made similar mistakes. :) Write if you need help! :)