VBA: Finding from clipboard

[AirMaximus88]

Hi guys,

Another problem from me, but this time it's VBA based. I'm trying to find data from my clipboard in another spreadsheet. At the moment it's a laborious process of copying, changing windows, findings, etc.

This is the code I'm trying out, but i dont understand why it's producing an error [Runtime error 91: Object vairable or With block variable not set].


Sub FindInP7()
Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText

Windows("P7 Data.xlsx").Activate


Cells.Find(What:=strClip, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub




I also tried to change ActiveCell to a range of cells, but that didnt work [After:=Range("A1:D1"). If anyone could help me with that and also deciphering what error my noob-like coding has produced, I would appreciate it!

Max

 

[AirMaximus88]

Bump. Still need help with this.

 

[Jerry Sullivan]

Hi Max, If you're putting the search string into the clipboard by copying it from a cell, the resulting value of strClip will have two extra characters added to the end for Carriage Return and Line Feed (vbCrLf). In that case your code returns an error because it can't find a match to that string with the extra characters.

Try making the modifications below which will remove the vbCrLf if it is present at the end of the search string.

Sub FindInP7()
 Dim MyData As DataObject
 Dim strClip As String
 Dim rngFound As Range
 
 Set MyData = New DataObject
 MyData.GetFromClipboard
 strClip = MyData.GetText
 
 Windows("P7 Data.xlsx").Activate
 
 If Right(strClip, 2) = vbCrLf Then
   strClip = Left(strClip, Len(strClip) - 2)
 End If
 
 Set rngFound = Cells.Find(What:=strClip, LookIn:=xlFormulas, _
   LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
 If rngFound Is Nothing Then
   MsgBox strClip & " not found."
 Else
   rngFound.Select
 End If
End Sub

One other observation...your code assumes that when activating P7 Data.xlsx, the search should take place on the ActiveSheet of that workbook. If it's possible that the desired search sheet might not be active, your code should explicitly activate that sheet.

 

[AirMaximus88]

Hi Max, If you're putting the search string into the clipboard by copying it from a cell, the resulting value of strClip will have two extra characters added to the end for Carriage Return and Line Feed (vbCrLf). In that case your code returns an error because it can't find a match to that string with the extra characters.

Try making the modifications below which will remove the vbCrLf if it is present at the end of the search string.

Sub FindInP7()
 Dim MyData As DataObject
 Dim strClip As String
 Dim rngFound As Range
 
 Set MyData = New DataObject
 MyData.GetFromClipboard
 strClip = MyData.GetText
 
 Windows("P7 Data.xlsx").Activate
 
 If Right(strClip, 2) = vbCrLf Then
   strClip = Left(strClip, Len(strClip) - 2)
 End If
 
 Set rngFound = Cells.Find(What:=strClip, LookIn:=xlFormulas, _
   LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
 If rngFound Is Nothing Then
   MsgBox strClip & " not found."
 Else
   rngFound.Select
 End If
End Sub

One other observation...your code assumes that when activating P7 Data.xlsx, the search should take place on the ActiveSheet of that workbook. If it's possible that the desired search sheet might not be active, your code should explicitly activate that sheet.

Thanks a lot! Worked a treat. I couldn't understand why strClip wouldn't work and couldn't find the answer anywhere.

+2 VBA points to you, sir.