Getting error Sub of Function not defined

[UltimateNeo]

Please Login or Register  to view this content.

It has only done it after I wanted to do the if part, as I want to do an if .Shapes("MonOn").Visible = msoCTrue do that function , if .Shapes("TuesOn").Visible = msoCTrue then do slightly different function looking at a different range of cells.

Thank you for your help in advance

[YasserKhalil]

Hello
Not clear. Can you upload sample of your workbook with your desired output?

[UltimateNeo]

Ok think of it "MonOn " "TuesOn" are shapes that are used for Tabs so there is a "MonOff" "TuesOFF"

As MonOn is Visible then from sheet 3 where for example you have all the numbers of the lottery 1 - 45 from that selection (Sheet3 B5:I10 with blanks) you want to select 6 numbers randomly without duplicates in the same row in sheet 1 C5:H23
But if Tues on is Visible then the ranges for the data and the destination change and is randomly 7 numbers not 6.

Hope this helps if not I will try and upload my excel file . That is if my keyboard hasn't stopped working

[bakerman2]

Where does this come from ?

Please Login or Register  to view this content.

And if you want your range to come from Sheet3 you shoul do like this.

Please Login or Register  to view this content.

[UltimateNeo]

The Set Cell = RdmCell(Rng) is to randomly select cells from rng(B5:I10)

changing to .range did not make any difference to the error

[bakerman2]

Please Login or Register  to view this content.

I don't know where you got that but when I run your code it's that line that's going into the error that you discribe.

[UltimateNeo]

Please Login or Register  to view this content.

I don't know where you got that but when I run your code it's that line that's going into the error that you discribe.

Twinnyfo from here wrote the original code and it did work in my other excel file.

[bakerman2]

Can you post an example file with all your code so we can test.

As I thought, RmdCell is a seperate UDF.
For future helpers.

Please Login or Register  to view this content.

PS
Works fine for me like this.

Please Login or Register  to view this content.

[daboho]

Perhab

Please Login or Register  to view this content.

[UltimateNeo]

I forgot to add this code, sorry my mind was somewhere else. But it is solved now

Function RdmCell(rng As Range) As Range
Set RdmCell = rng.Cells(Int(Rnd * rng.Cells.Count) + 1)
End Function