Trouble with String + RandBetween() VBA

Question

For i = i To nr_commercial
    Cells(i, 1) = commercial & RandBetween(1, 6)
Next i

Hi All - I have the above but I am not able to solve it. It returns a compiler error every time. I'm having issues putting a string plus a formula together.

commercial is defined as a string

I have even tried

Cells(i, 1) = commercial & = Randbetween(1,6)

and it returns error saying expecting expression. Is there something not right with the randbetween functionality?

I need to have output in cells such like:

commercial-1
commercial-5
commercial-3

Should For i = i To nr_commercial not be For i = 1 To nr_commercial? The problem is if i is 0 in Cells(i, 1), then this will throw the error. — Axel Richter
– Axel Richter, Commented Dec 5, 2017 at 12:39

Gary's Student · Accepted Answer · 2017-12-05 12:52:21Z

1

Try:

Application.WorksheetFunction.RANDBETWEEN(1,6)

For example:

Sub qwerty()
    Dim nr_commercial As Long, commercial As String

    nr_commercial = 10
    commercial = "whatever"
    For i = 1 To nr_commercial
        Cells(i, 1) = commercial & Application.WorksheetFunction.RandBetween(1, 6)
    Next i
End Sub

(also fixed the loop index)

edited Dec 5, 2017 at 12:52

answered Dec 5, 2017 at 12:36

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christopher Phillips Over a year ago

Thanks! I was playing around with the loops and must've left bad code before asking the question. But this works as intended! Thank you

nishit dey · Accepted Answer · 2017-12-05 12:43:18Z

0

Try this

For i = i To nr_commercial
  Cells(i, 1) = "commercial-" & Application.WorksheetFunction.RANDBETWEEN(1,6)
Next i

Edited : Application.WorksheetFunction.RANDBETWEEN(1,6) is the correct formula to be used

Also it is not advisable to use Cells try to use Sheet or Worksheet in your code.

edited Dec 5, 2017 at 12:43

answered Dec 5, 2017 at 12:37

nishit dey

4581 gold badge7 silver badges22 bronze badges

Comments

Issel · Accepted Answer · 2017-12-05 14:12:30Z

0

I think your issue is that you are using For i = i To nr_commercial which means every iteration it is changing "For 1 = 1 to nr_commercial" and then "2 = 2 to nr_commercial" which doesn't make sense. It should be For i = 1 to nr_commercial or whatever cell you want to start at, probably not in the first cell, as you are likely to have headers.

answered Dec 5, 2017 at 14:12

Issel

1416 bronze badges

Collectives™ on Stack Overflow

Trouble with String + RandBetween() VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related