Solved

Problem with VBA code in sub routine

Posted on 2014-02-17
5
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 30

Expert Comment

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

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 30

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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