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
Medium Priority
231 Views
``````For I = 3 To 14
C(I) = Quotient(A3, B3)
Next I
``````

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
Question by:Frank Freese
• 3

LVL 31

Expert Comment

ID: 39865295
what is the quotient function ?
gowflow
0

LVL 31

Expert Comment

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

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
``````

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
``````

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

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
``````

Check out the file
gowflow
Quotient.xls
0

Author Closing Comment

ID: 39865404
thank you both
0

Featured Post

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.
Suggested Courses
Course of the Month12 days, 21 hours left to enroll