EXCEL VBA for RandBetween (2007)

[Hagrid1]

Hi,

I am new to VBA and hope that someone can help with this:

Issue - I wish to create a random row number which I will use to compare two spread sheets.

1) when I type RandBetween(1, NoRow1) I get

"Runtime error '1004':
Unable to get the RandBetween property of the worksheet function class


(NoRow1 is dimed as a string or a double and is the last row with data present) This is placed in a cell on the sheet - say G7

2) I also wish the random number obtained to be placed in for example - cell B7

Overall-

I have 2 files which I wish to compare
I am comparing the 1st & last row and a random row

The 3 rows are copied from sheet 2 & sheet 3 and placed in sheet 1 for comparison.

All this works however I cant get the VBA correct to create the correct coding for the randomisation of the random row to be selected for copying.


Coding:

Dim NoRows1, Nocolumn1 As String
NoRows1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Nocolumn1 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Sheets("Comparator sheet").Select
' Sheets("Comparator sheet").Select

Dim DR As Double

DR = WorksheetFunction.RandBetween(1, NoRow1)

Range("b7").Value = DR

 

[steve the fish]

You cant call it NoRows1 then use it as NoRow1. They arent the same.

 

[Marcelo Branco]

Thoughts
1. You should Dim both as Long because the property Row of a range returns a long value.

2. Using
Dim NoRows1, Nocolumn1 As String
only the second variable is being "dimmed" as String; vba assumes the first as Variant
So try
Dim NoRows1 as Long, Nocolumn1 As Long

3. As already pointed out by Steve: DR = WorksheetFunction.RandBetween(1, NoRows1)
To avoid such errors (spelling) use Option Explicit. See
http://www.cpearson.com/excel/declaringvariables.aspx

M.

 

[Hagrid1]

Thanks folks,

I eventually noticed the lack of the "s" in the variable name it now works and I get a random number between the 1st and last row.

My other question was how do I now get the VBA to copy the data from that row to a defined row in another sheet.

e.g.

Sheet 1 = comparator sheet
Sheet 2 = original data which contains the information pulled through from file No 1
Sheet 3 = original data which contains the information pulled through from file No 2

I have the:
Row number displayed in say cell G7
Random number stored in say Cell B7

I have coding -
Sheets("Original data").Select
Rows("1:1").Select
Selection.Copy
Sheets("Comparator sheet").Select
Rows("11:11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

which pulls through the data form row 1 and places it in to the comparator sheet in row11

I now want to pull through the randomly selected row number data

How do I do this (What coding do I need to change the red bold above) to reflect the row number in Cell B7 using VBA to place it in to say row 18 on the comparator sheet

 

[Marcelo Branco]

Maybe something like this (no need of Select)

Sheets("Original Data").Rows(Sheets("Comparator sheet").Range("B7").Value).Copy
Sheets("Comparator sheet").Rows(11).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

M.