Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with VBA code in sub routine

Posted on 2014-02-17
5
Medium Priority
?
231 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 31

Expert Comment

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

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 1000 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 31

Accepted Solution

by:
gowflow earned 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

972 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