Creating random subset of list then retreiving other columns

tutu1201

New Member
Joined
Jun 27, 2006
Messages
15
Can't think of any other way to describe this.

I Have Excel 2010 and a voter registration list in 1 sheet. A blank sheet will contain a random selection from the VR sheet. Since it only pulls the name, I am trying to write VB code to:
1. Loop through the random sheet, picking the next name and storing to a variable.
2. Activate the VR sheet and read down the list to find a match of name.
3. Once located, copy the entire row data.
4. Return to the random sheet and paste the contents of the var into the row from which just the name was originally retrieved.
5. Move to the next row in the random sheet and repeat until all have been taken care of.

You know, if I just had my trusty, good old dBase III+ or even IV, I'd have this knocked out in no time. Visual Basic -- not my forte.

Can anyone help me code this?

Thanks.
 
No worries!

If that works then I'd make a couple of minor changes to make it a little more robust:

Code:
    ' Find the last column, last row
    With shtSourceSheet
        intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        dblLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
     [COLOR=red]' Line removed[/COLOR]
    End With
[COLOR=yellowgreen]   If dblLastRow < 11 Then[/COLOR]
[COLOR=yellowgreen]       MsgBox "Not enough data to continue.", vbExclamation, "Not enough data"[/COLOR]
[COLOR=yellowgreen]       GoTo cleanup[/COLOR]
[COLOR=yellowgreen]   End If[/COLOR]

... and ...

Code:
    With shtRandoms
        .PasteSpecial
        ' Name and date/time stamp the sample sheet
        [COLOR=yellowgreen].Name = Left(shtSourceSheet.Name, 8) & " Sample " & Format(Now(), "yyyymmdd hhmmss")[/COLOR]
    End With
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top