Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 7th, 2009, 01:40 PM
Authorized User
 
Join Date: Sep 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Question Error: Invalid or Unqualified Reference

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 :)
 
Old September 8th, 2009, 01:18 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default

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. :)
 
Old September 8th, 2009, 10:19 AM
Authorized User
 
Join Date: Sep 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you Sektor, worked perfectly :) sorry i'm a nub
 
Old September 9th, 2009, 12:26 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
invalid reference to property rashi Access 6 September 28th, 2007 11:39 AM
invalid reference NeoCat Access 4 September 20th, 2005 12:04 PM
Invalid use of property error. Bob Rupp BOOK: Beginning Access 2003 VBA 1 March 4th, 2005 09:09 AM
Invalid Use of Property Compile Error dubs70 Access VBA 8 March 9th, 2004 03:19 PM
Viewstate Invalid Error mahulda ASP.NET 1.0 and 1.1 Basics 0 February 20th, 2004 11:36 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.