VBA Search for non-empty cell. insert random number on next cell

[AaronBallard]

Hello All,

I have had a good forum/Google search to try and work this problem out, but had no luck.

It has been too long since I have used VB and I've forgotten how to do basic things.

I'm looking for VBA code to do the following:
Search Column B (after B1) for non-empty cells. If the cells have text, then insert a random number between the range of 10000-90000 in the A column next to the cell. e.g.


Col A Col B
123441 Mon
<blank> <blank>
312312 Mon
<blank> <blank>
<blank> <blank>
<blank> <blank>
213123 Tues
<blank> <blank>

Many Thanks.</blank></blank></blank></blank></blank></blank></blank></blank></blank></blank>

 

[asif_raza]

Sub randnum()


Dim Lrow As Long
Dim x As Long
Lrow = Range("B" & Rows.Count).End(xlUp).Row
For x = 1 To LastRow
If IsEmpty(Range("B" & x).Value) = False Then
Range("C" & x).Value = Int((90000 - 10000 + 1) * Rnd + 10000)
End If
Next x
End Sub

 

[AaronBallard]

Thank you for the quick reply, however this code did not work for me.

 

[asif_raza]

Welcome. I inserted random numbers in Column C. you can replace Range("C" & x).Value with Range("A" & x).Value. This way it will insert random values in A column.

 

[AaronBallard]

I did change it to 'A' but I'm not getting any values in column A. I'm using Excel 2003

 

[asif_raza]

I rechacked it and it did not run here too... I found mistake though...but still not sure if it will run at 2003


Dim Lrow As Long
Dim x As Long
Lrow = Range("B" & Rows.Count).End(xlUp).Row
For x = 1 To Lrow
If IsEmpty(Range("B" & x).Value) = False Then
Range("A" & x).Value = Int((90000 - 10000 + 1) * Rnd + 10000)
End If
Next x
End Sub

 

[AaronBallard]

That's Great. Thanks

But I can't work out why it is changing cell A2, it is generating a random number in cell A2
A2 = _event_id
b2 = _day_of_week