Solved

Problem with VBA code in sub routine

Posted on 2014-02-17
5
221 Views
Last Modified: 2014-02-17
For I = 3 To 14
    C(I) = Quotient(A3, B3)
Next I

Open in new window


The above code returns a compile error Sub or Function not defined.
There are values in A3:A14 and B3:B14 and I'm teaching the use of the Quotient function.
I've tried to hide the results using the ";;;" but that does not work on Errors. Instead I am used the ClearContents. When the user selects a command button labeled Display Data I need to repopulated C(3):C(14) with the Quotient function. which is the code you see above.
I thought of creating a user-defined function but that failed.
0
Comment
Question by:Frank Freese
  • 3
5 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39865295
what is the quotient function ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39865303
for sure you will get an error as Quotient is not defined as a function or Sub

what do you want to accomplish ?
A divide by B and take the full part ? that is the quotient

gowflow
0
 
LVL 10

Assisted Solution

by:acbxyz
acbxyz earned 250 total points
ID: 39865315
You can't use the cells as they are. In this case A3 would be a variable or function name but it is not. Neither does C() exist in the way you want.

Try this:
For I = 3 To 14
    Range("C"&I).Value = Quotient(Range("A"&I).Value, Range("B"&I).Value)
Next

Open in new window


Instead of Range("B"&I).Value you can use Cells(2, I).Value if you prefer.

For I = 3 To 14
    Cells(3, I).Value = Quotient(Cells(1, I).Value, Range(Cells(2, I).Value)
Next

Open in new window


But all this needs quotient to be a function. Look at this:
http://www.excelforum.com/excel-programming-vba-macros/507268-is-there-a-quotient-function-in-vba-thats-like-mod.html
0
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 39865336
I have added reminder and here is the full sub for you the syntax is wrong

Sub test()
For I = 3 To 14
    Range("C" & I) = Int(Range("A" & I) / Range("B" & I))
    Range("D" & I) = Range("A" & I) Mod Range("B" & I)
Next I
End Sub

Open in new window


Check out the file
gowflow
Quotient.xls
0
 

Author Closing Comment

by:Frank Freese
ID: 39865404
thank you both
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now