VBA excel help please

emmcee · Sep 13, 2011

Hello All,

I need help with a homework that I've been working on, which is writing a code to find the factorial of a value where the constant include:

Const COL_N = 1 ' column for the n values
Const COL_NFACT = 2 ' column for the factorial values
Const ROW_START = 2 ' ignore header row

calculate factorials (n! = 1 * 2 * 3 * ... * (n–1) * n) using VBA and display them on a spreadsheet .

I have to create a subprogram that contains a loop that generates factorials in order, starting with 0! = 1, 1! = 1*0! = 1, 2! = 2*1! = 2, 3! = 3*2! = 6 and so on.

Notice that each step uses the next value of n and the previous factorial value to produce the next.

Given variables holding the row and column numbers, I have to assign a cell using one of these statements:

ActiveSheet.Cells(row, col).Value = myData

ActiveSheet.Cells(row, col) = myData

Some way of stopping the generation process. For this version there is a cell on the sheet named MaxFact. Pick up this value using the ActiveSheet.Range(name) notation, where name is a string constant or variable holding the name or address of the required cell. Stop the iteration when the factorial exceeds this value, but before displaying it (the algorithm structure should allow this).

Also if you are able to use the While structure to express this condition.

Thanks in Advance!

Andrew Poulsom · Sep 13, 2011

What have you tried? The WorksheetFunction object has a Fact function.

emmcee · Sep 13, 2011

I've tried researching and using different functions like :

 [FONT=&quot]' Simple program to compute factorials[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Function factorial(n as Long)[/FONT]
[FONT=&quot] If (n <= 0) Then[/FONT]
[FONT=&quot] factorial = 1[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] factorial = n * factorial(n - 1)[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End Function[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Dim fx(12) as Long[/FONT]
[FONT=&quot]For i = 1 to 12[/FONT]
[FONT=&quot] fx(i) = factorial(i)[/FONT]
[FONT=&quot]Next[/FONT]

But when I click on Run it wouldn't work

Andrew Poulsom · Sep 13, 2011

Try this:

Code:

Sub Test()
    Const COL_N = 1 ' column for the n values
    Const COL_NFACT = 2 ' column for the factorial values
    Const ROW_START = 2 ' ignore header row
    Dim i As Long
    Dim r As Long
    Dim Max As Long
    Dim myData As Long
    i = 0
    r = ROW_START
    With ActiveSheet
        Columns(COL_N).ClearContents
        Columns(COL_NFACT).ClearContents
        myData = WorksheetFunction.Fact(i)
        While myData < .Range("MaxFact").Value
            .Cells(r, COL_N).Value = i
            .Cells(r, COL_NFACT).Value = myData
            i = i + 1
            r = r + 1
            myData = WorksheetFunction.Fact(i)
        Wend
    End With
End Sub

dslt · Sep 13, 2011

So, I Copied and Paste it onto my module and ran it but it says that there is an error ?

It says "The macros in this page is disabled"

Sorry I can't help :/ I jsut wanted to try it !

emmcee · Sep 13, 2011

Heys, Yeah I just tried the code but I got the same message
I seem to always get that message with the other codes

Andrew Poulsom · Sep 13, 2011

You need to enable macros to run one:

http://office.microsoft.com/en-us/excel-help/enable-macros-to-run-HP001119579.aspx

dslt · Sep 13, 2011

Hey Andrew it still didn't work,

Do you know how to write a normal factorial code without the constants just if i'm using A2 as the number cell and B2 is the factorial with the equation, but not knowing the whole range and how far it can go in that column could you write a simple code for me so that i can work on it on my module tonight?

Thanks A lot

Andrew Poulsom · Sep 13, 2011

In what way didn't it work? Did you have a cell named MaxFact on the active sheet, other than in columns A:B?

dslt · Sep 13, 2011

Ok, the my active cell where the answer goes is B2 and the number where i'm trying to find the factorial of is in A2(with number 8)

I used = MAXFACT(A2)
but it didn't work

please let me know what im doing wrong?

VBA excel help please

emmcee

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

emmcee

New Member

Andrew Poulsom

MrExcel MVP

dslt

New Member

emmcee

New Member

Andrew Poulsom

MrExcel MVP

dslt

New Member

Andrew Poulsom

MrExcel MVP

dslt

New Member

Similar threads

Forum statistics

Share this page

VBA excel help please

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock