Rnd() 1004 error

[Shahin Musayev]

Hi everyone!

I have one old workbook and it contains following piece of code:

Cells(6, Sel_C + 2) = "=" & VBA.Rnd() & "*" & Cells(5, Sel_C +
2).Address

On my machine it works fine, but when I send this workbook to my
colleague above mentioned line generates 1004 error. So I replaced
previous code with following one:

Cells(6, Sel_C + 2) = "=" & (Application.WorksheetFunction.RandBetween
(0, 10000000) / 10000000) & "*" & Cells(5, Sel_C + 2).Address

And now everything works fine.

Does this all mean that my colleague does not have VBA library at all?
Is it possible? Any thoughts?

Thanks!

Shahin

 

[Rick Rothstein]

Is your colleague using a Mac with Excel 2008 by any chance?

 

[Shahin Musayev]

Is your colleague using a Mac with Excel 2008 by any chance?

No. Windows Vista + Office 2007

 

[Dave Peterson]

When I see VBA.Rnd (or any VBA.xxx) in someone's code, I think that they're
doing something to avoid another problem.

I would have expected to see:

Cells(6, Sel_C + 2).Formula = "=" & Rnd & "*" & Cells(5, Sel_C + 2).Address

I'd look at the colleague's pc and do this:

Open the excel and your workbook
Open the VBE and select your workbook's project.
Then click on: Tools|References
Look for MISSING reference.

Uncheck that missing reference.

The missing reference may not (usually doesn't) have anything to do with the
line that caused the error.

If you're not using that reference, you can go back to your pc and remove it and
then redistribute the workbook.

If you're using that reference, you'll have more work to do for the troublesome
pc's.

 

[Shahin Musayev]

It will be difficult to look at colleague's pc: we are in the
different offices, different countries

Thing is that workbook has only basic references: VBA, Excel, Office,
stole + MSForms . I don't think that they can become missing. Is it
possible?
Thing is that IT policy is the same in all offices thus my colleague's
pc configuration should not differ from my pc configuration.

Also despite the fact that line:

Cells(6, Sel_C + 2) = "=" & VBA.Rnd() & "*" & Cells(5, Sel_C +
2).Address

looks not very accurate (it is an old workbook) the problem is not in
the missing ".Formula" property. This property is omitted all around
the code but only above mentioned line cause error. And as for VBA.Rnd
()... well at the beginning it was just Rnd() and I added "VBA." just
in case, because I didn't have any other thoughts

Any other ideas?

Thanks.

Shahin

 

[Dave Peterson]

Call the other user and ask them to look for missing references.

Maybe you can have remote access to see for yourself.

 

[Shahin Musayev]

Finally the problem is solved.

Thing is that colleague has regional settings that differ from US
English: comma for decimal symbol and dot for digit grouping.
It appears that Rnd() return is in local format (decimal symbol -
comma!!!), while Cells(x,y) = or Cells(x,y).Formula expects formula to
be entered in US English.

The solution is simple: add .FormulaLocal where applicable

BTW Previous solution with WorksheetFunction.RandBetween also didn't
work. I was misinformed by my colleague.

Hope this will be useful.

Regards,
Shahin