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!

Please Login or Register  to view this content.

Autofill if blank from random cell in chosen column

LinkBack

Thread Tools

Rate This Thread

Display

Autofill if blank from random cell in chosen column

Re: Autofill if blank from random cell in chosen column

Re: Autofill if blank from random cell in chosen column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions