Search column for non-empty cell and display results?

Greetings all,

New to this forum, and a (very) novice Excel Functions/VBA user. I work with a sales team that runs various incentive programs, many of which have prize drawings. Entrants often get multiple entries in the drawing.

Before I came on board in this job, people were running a physical drawing and hand-writing out tickets. I'm trying to automate the entire process.

So, I've added a few worksheets to the original. One simply mirrors data fields to give a simple two-column display of entrants and # of tickets.

The next sheet functions as a copy/paste/sort area (I know there is probably a cleaner way to do this, but as I said, I'm a novice).

The last sheet actually does the drawing. Column C shows the ticket range for each entrant, and cell C8 actually selects the ticket at random. The winner is displayed in the appropriate row in Column D.

My problem is: the people who will be conducting the drawing would like the winner's name to be displayed in D7, so as to not have to scroll down and look for it. (Picky, I know, but my boss's idiosyncracies are my responsibilities).

I figure that once the winner's name is displayed in Column D, I should be able to search the column for the one cell with text in it (there should be only one), and display the text within it in cell D7.

The problem is, I can't figure out how to do this. I looked at VLOOKUP, figuring I would look at the values from A10:A83 and search D10:D83 for any of the same strings. Nope, get a VALUE? error in D7 when I try that.

Is there a function that will do this? If so, can someone point me in the right direction? If not a function, am I looking at creating some sort of loop search in VBA? File attached with .doc extension, but is .xls file.

Many thanks for any assistance.

That worked perfect, thanks!

I realized I left out one detail (one step in the manual process), in that once the data's been sorted, only the entrants with positive values need to be copied.

I tried using the code you provided (which worked great) and adding a loop to search for the first row with a 0 value, and clear it out with all remaining rows. However, my range method keeps failing. Any ideas here?

Sub CopySort()
  Dim CopySH As Worksheet, SortSH As Worksheet, OutSH As Worksheet
  Dim Z As Integer, RowNum As Integer, BCount As Integer
    Set CopySH = Sheets("COPY FROM HERE (DO NOT CHANGE)")
  Set SortSH = Sheets("PASTE TO HERE")
  Set OutSH = Sheets("DRAWING")
  'clean out any existing data
  OutSH.Range("A14:B214").ClearContents
  OutSH.Range("B4").Value = ""
  SortSH.Range("A2:B" & SortSH.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
  
  'copy across data
  CopySH.Range("A2:B202").Copy
  SortSH.Range("A2").PasteSpecial (xlPasteValues)
  SortSH.Range("A:B").Sort , order1:=xlDescending, key1:=SortSH.Range("B1"), header:=xlYes
    Z = 0
    RowNum = 2
    For Z = 1 To 500
        If SortSH.Range("B""RowNum") <> 0 Then Exit For
        RowNum = RowNum + 1
    Next Z
  SortSH.Range("A2:B" & SortSH.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
  SortSH.Range("A2:B202").Copy
  OutSH.Range("A14").PasteSpecial (xlPasteValues)
End Sub

Hi

I had realized that, but thought it wouldn't make any difference to the way things worked. Then realized that if the max number came up, it wouldn't select the right name.

Revised the code to only copy across the entries where the number of tickets is > 0. Down side: there is no error checking to make sure that it doesn't bring across more items than the formulas you have can copy.

Sub DoIt()
  Dim CopySH As Worksheet, SortSH As Worksheet, OutSH As Worksheet
  Dim LastRow As Long
  Set CopySH = Sheets("COPY FROM HERE (DO NOT CHANGE)")
  Set SortSH = Sheets("PASTE TO HERE")
  Set OutSH = Sheets("DRAWING")
  'clean out any existing data
  OutSH.Range("A10:B83").ClearContents
  OutSH.Range("B4").Value = ""
  SortSH.Range("A2:B" & SortSH.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
  
  'copy across data
  CopySH.Range("A2:B500").Copy
  SortSH.Range("A2").PasteSpecial (xlPasteValues)
  SortSH.Range("A:B").Sort , order1:=xlDescending, key1:=SortSH.Range("B1"), header:=xlYes
  LastRow = WorksheetFunction.Match(0, SortSH.Range("B:B"), 0) - 1
  SortSH.Range("A2:B" & LastRow).Copy
  OutSH.Range("A10").PasteSpecial (xlPasteValues)
End Sub

rylo

Thank you very, Very, VERY MUCH!! Works great!

Can you recommend a good textbook for the novice Excel/VBA user to use to get to the next level? Thanks!

Kiltman