6

What I want: I've got a lot of sheets whith different devices. Let's call one of these sheets "WS1".

And I've got a seperate sheet with all existing devices and the appropriate OS next to it. This one we call "list".

Now I want the other sheets (e.g. the "WS1") to check the "list", find the right device, and copy the right OS into the WS1-sheet.

the manual way would be:

  • select cell "C3" of WS1 and copy it.
  • open the "list"-Sheet and find the copied entry
  • select the cell left to the found entry and copy it
  • open the WS1 again, select the left cell right next to the active cell and paste the new clipboard (which contains the OS)
  • select the next cell which is under and on the right side of the active cell.
  • loop until every device in WS1 is filled with an OS

What I've got so far:

Dim DataObj As New MSForms.DataObject
Dim strCliBoa As String
'strCliBoa = DataObj.GetText
DataObj.GetFromClipboard

Range("C3").Select
Selection.Copy
strCliBoa = DataObj.GetText
Sheets("list").Select
Range("A1").Select
Cells.Find(What:=strCliBoa, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Selection.Copy
strCliBoa = DataObj.GetText
Sheets("WS1").Select
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 1).Select

My issue: "Runtime Error 91: Object variable or with block variable not set" and it marks the cells.find-method.

Can someone tell me what I'm doing wrong?^^ Thanks in advance!

(oh, almost forgot: I'm using ms excel 2010 on Win7)

JvdV
  • 70,606
  • 8
  • 39
  • 70
detail
  • 69
  • 1
  • 1
  • 2

5 Answers5

9

If the string you're looking for isn't found you'll get that error. The find function returns "Nothing" if nothing is found

    Dim r As Range

    Set r = Cells.find(What:=strCliBoa, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False)

    If r Is Nothing Then
        'handle error
    Else
        'fill in your code
    End If
Sobigen
  • 2,038
  • 15
  • 23
1

I'll provide you an answer using the VLOOKUP() function. So Sheet1 contains several devices and I need to find the correct OS. Sheet2 contains the matching between device and OS.

On Sheet1 enter this formula in the cell next to device and pull it down (of course edit to your specific needs).

=VLOOKUP(A2;Sheet2!$A$1:$B$20;2;0)

EDIT: the VLOOKUP function will only work if the OS is in second column. Either switch around the columns or use a helper column at the end to contain the OS.

CustomX
  • 9,948
  • 30
  • 85
  • 115
  • 2
    He has the value he wants to return in the column **to the left** (3rd bullet in OP) of value he is looking up, he needs to use Index+Match combo. Vlookup can be used, if he switches the columns. – kolcinx Mar 17 '15 at 15:11
  • Ooooh, must have looked over that! – CustomX Mar 17 '15 at 15:13
  • great! this looks way smarter^^ (i'm a total newbie, i'm sorry) i'll try it out, if i dont get it to work, i will ask again in this thread. thanks (: – detail Mar 17 '15 at 15:14
  • @detail, make sure you read my extra edit I added to my answer, otherwise it won't work. – CustomX Mar 17 '15 at 15:16
0

In the sheet where you have the Device name (WS1) put formula:

=INDEX(List!$A$2:$B$10;MATCH('WS1'!C3;List!$B$2:$B$10;0);1)

Where :
List!$A$2:$B$10 is a range where you have the Devices + OS in the list
'WS1'!C3 is the Device you want to search for in the list ("WS1" in your case)
List!$B$2:$B$10 is the column on Sheet List, where the devices are listed.

Edit 1 - VBA code

If you want to use VBA then use this :

Sub FindDevicePasteOS()

'Find corresponding OS for the device

Dim intRow As Integer
Dim wsht As Worksheet

For Each wsht In Worksheets
    If wsht.Name <> "List" Then 'add more sheets you want to exclude using OR (e.g. ... Or wsht.Name <> "Cover Sheet" Then)
        For intRow = 3 To wsht.Cells(Rows.Count, 3).End(xlUp).Row 'presuming there is nothing else in the column C below the devices
            If Not Worksheets("List").Cells.Find(what:=wsht.Cells(intRow, 3)) Is Nothing Then
                wsht.Cells(intRow, 2) = Worksheets("List").Cells.Find(what:=wsht.Cells(intRow, 3)).Offset(0, -1)
            End If
        Next intRow
    End If
Next wsht

End Sub
kolcinx
  • 2,183
  • 1
  • 15
  • 38
0

So I used a psuedo solution where I added the If x is nothing block to the code to skip over the err'd pieces. I was able to process about 80% of the data which is good for me. I still can't understand why Find would return nothing.

Another interesting and maybe related problem occurred in a different computer running the same macro - after I ran into this problem a few times, my computer gave me a blue screen with a 'thread stuck in driver' message. Could they be related? Excel processing to much to fast and get's mixed in the thread processing?

Food for though, I dunno why the find won't just work every-time.

rawmud
  • 70
  • 9
0

In Sobigen post I had to switch the part LookAt:=xlPart to LookAt:=xlWhole to get it to work because If r Is Nothing Then was throwing an error when it found partial matches. Other than that the code worked great thanks!

tripleee
  • 175,061
  • 34
  • 275
  • 318
Marc Guvenc
  • 53
  • 5