TheSwamp
Code Red => VB(A) => Topic started by: Reinaldo Sampaio on February 28, 2007, 06:04:09 PM
-
I am new in this site and in VBA too.
I am creating a routine to fill an excell spread sheet with a bill of materials.
I need to perform some calculations.
Now I need to discover the great commom denominator betwen some numbers
Any function to do it in VBA for Excel?
Thanks for your help.
-
From Excel Help:
Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.
If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.
How?
Syntax
GCD(number1,number2, ...)
Number1, number2, ... are 1 to 29 values. If any value is not an integer, it is truncated.
Remarks
If any argument is nonnumeric, GCD returns the #VALUE! error value.
If any argument is less than zero, GCD returns the #NUM! error value.
One divides any value evenly.
A prime number has only itself and one as even divisors.
Examples
GCD(5, 2) equals 1
GCD(24, 36) equals 12
GCD(7, 1) equals 1
GCD(5, 0) equals 5
-
I have done all procedures to use GCD in Excel.
When I open a new instance of excel, and I create a formula like
=gcd (A2:A4), it works fine.
There is still a problem.
When the program opens an instance of Excel, the formula in the cell returns
#NAME?, although "analisis tool" box In add ins options is checked.
This is the command line to put the formula in Excel.
ExcelSheet.Cells(StartRow, 7).Formula = "= gcd(G" & StartRow + 1 & ":G" & EndRow & ")"
- If I copy the fine working formula from its sheet and paste into the bad working cell,
it works fine, too. !!!!!
- If I copy the bad working to the good working, it doesn´t work too (???)
How can I solve this problem?
Thanks for help.
-
Please post the bad formula that the code generated so we can try to figure out what is wrong.
-
The formula is = gcd (g7:g15)
I am using a template sheet.
When I open it and create the formulas manualy, it works fine.
But when the program does it, the cell shows a #Name? error.
I guess the problem is Excel is not configured in the rigth way, and is not founding the Analisis Tools .
Or, maybe some configuration in the references on Visual BAsic Editor.
Thanks for your reply.
-
The formula you are creating with VBA is wrong. There is a space between the = and gcd. Remove that space and it should work fine.
ExcelSheet.Cells(StartRow, 7).Formula = "=gcd(G" & StartRow + 1 & ":G" & EndRow & ")"