Solved

Problem with VBA code in sub routine

Posted on 2014-02-17
5
223 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

14 Experts available now in Live!

Get 1:1 Help Now