Autofill if blank from random cell in chosen column

**jason117** · 08-26-2011, 04:36 PM

Hi all,

This is my first post so bear with me. I'm usually good at finding out how to mold a formula to my suiting but i'm at bit of an impass:

I would like a formula to autofill all blank cells within (Data!J,1) to (Data!J, LR) with a random, non-empty cell from column DATA!B. Here is what I have so far:

Dim i As Integer

For i = 1 To LR

If Range("DATA!J" & i).Value = "" Then Range("DATA!J" & i).Value =xxxx

next i

end sub

I don't know what to put in for "xxxx". "xxxx" should be a value of a random, *non-empty* cells in the Data!B column

for the non-empty condition i think i should use: Range("DATA!B" & Rows.Count).End(xlUp).Row) in order to set a range of cells with data in them

I don't know where to go from there though.

Please help!

Thanks, and I'm happy to join this forum!

-J

**jason117** · 08-26-2011, 05:54 PM

I worked on the troublesome line a little more:

It is still incorrect, but my best attempt.

If Range("DATA!J" & i).Value = ""
Then Range("DATA!J" & i).Value = cells([Int((Range(.Range("Fill!A2:Fill!A65535").End(xlUp)).Count) * Rnd) + 1], [Fill!A]).Value

I'm still coming up with errors. i'm trying to use an absolute reference with a random variable...[row, column] where "row" is found by generating a random number that lies between the number of rows within column, and "column" is fixed as "A". I'm sure this will work but I can't get the syntax correct

If it helps, I need this to work in order to autofill a large database, where occasionally the user does not specify certain parameters and I will therefore give him a randomly assigned one from a list I have made.

I'm using this function to create an open-source email parser which inputs data into excel and exports to a processing application. Thanks for your help guys!

**jason117** · 08-26-2011, 11:49 PM

I solved it. I made the code highly flexible for varying column sizes. enjoy!


Sub FillEmptyFromSelection()

Dim LR as Long
Dim CR As Long


LR = Range("DATA!C" & Rows.Count).End(xlUp).Row
CR = Range(Range("RANDOM!C2"), Range("RANDOM!C65535").End(xlUp)).Count

'***LR is the range of the target data set with the blanks, CR the count of the rows from the column with all till the data, from which a cell will be randomly selected and inserted.

Dim i As Integer

For i = 1 To LR

If Range("DATA!C" & i).Value = "" Then Range("DATA!C" & i).Value = Worksheets("RANDOM").Cells((CR) * Rnd + 1, 3)

Next i

'*** In layman's terms, if there is an empty cell in column C in the DATA sheet, it will be  '*** replaced by a random cell in column B in the RANDOM sheet.


End Sub