Hello,
I was wondering if anyone could help me with my problem.
I want to randomly select a cell in column F from row 2 to value in B9 with conditions or <=B7.value and >=B8.value
I then want to copy that row from B:F to another worksheet and then repeat this process as many times as the value in B10
any help appreciated, thank you for your time
Clarify what you mean by "with conditions or <=B7.value and >=B8.value" Is that OR or AND? And an example file would be helpful.
Bernie Deitrick
Excel MVP 2000-2010
Yeah it is and so it would make it between two values.Originally Posted by Bernie Deitrick
I will try and get a sample file later, as i have my sister's funeral tomorrow. So it might be a few days for me to get back on it
So sorry to hear that: my condolences.
Thank you, I hope that she is now not in any pain, and at peace. At least she had her family around her at the time of death and although she couldn't talk we hope she heard us talking to her in her final moments.
I shall try and get something together over the weekend or next week.
I wonder if this makes it easier to understand.
I have data generated in columns E:L, I want to randomly select a cell in column L as the first row are headers I want to start from row 2. The data generated has a number of rows this value is in B10 so that gives me the how many rows from row 2 the data will be. If the randomly selected cell does not meet the criteria of >= value in B2 AND <= value in B3 then I want it to select again. If criteria is met then I want it to copy that row from E:L to a new worksheet with no blanks.
I want this process to repeat until the value of cell B12 is reached then finish.
Or is it best to select the cells at once until the value of B12 is reached and then copy them all at the same time, instead of copying them separately.
Is this a random sampling kind of problem? Are you interested in Excel formula based rather than VBA solutions? If so, you might find this thread of interest. https://www.excelforum.com/excel-for...ging-data.html I suggested a "shuffle" where one would assign unique random numbers to each value in the list and then lookup the smallest n random numbers from that list. Someone else came up with a different way to shuffle the list with a single cell array formula that returns a random sample of the list.
If I have correctly understood your task and you are not required to use VBA, one of those approaches might work for you.
Hello MrShorty, No I had a look at the formula based solutions and it is not what i am after.
I have some code without the if >= and <= in for the moment
This currently has an error with the line a compile error invalid or unqualified reference at the .rows.count
Please Login or Register to view this content.
Thank you for any help providedPlease Login or Register to view this content.
This isn't the kind of VBA programming I do, so I'm not sure I can help with all the details. In this case, though, it looks like that statement is using an unqualified Cells() object. When unqualified, VBA assumes you mean ActiveSheet.Cells(i,5), which will refer to a cell in column E of the active sheet. Then you ask VBA to go left 8 columns, which, of course, is not on the spreadsheet.
The solution to this error is to figure out what range you want to be looking in at this point and use that range object.where range is a reference to the desired range (somewhere to the right of column H).Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks