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

Problem with VBA code in sub routine

Posted on 2014-02-17
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.
Question by:Frank Freese
  • 3
LVL 29

Expert Comment

ID: 39865295
what is the quotient function ?
LVL 29

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

LVL 10

Assisted Solution

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)

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)

Open in new window

But all this needs quotient to be a function. Look at this:
LVL 29

Accepted Solution

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

Author Closing Comment

by:Frank Freese
ID: 39865404
thank you both

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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