What's wrong with my code?

jaclynresendez

New Member
Joined
Jan 20, 2009
Messages
19
What I want this code to do is find the date on Paste!B2 in Tracker! Range(A3:IQ2), stepdown 2 cells, and paste the data copied from a previous sub. It's prolly all messed up, I know. Everything works except finding the date. Right now, it pastes the data wherever the activecell is which is not good. Code below:

Sub Find_Last()
Dim FindString As String
Dim Rng As Range
FindString = Sheets("Paste").Range("B2").Value
If Trim(FindString) <> "" Then
With Sheets("Tracker").Range("A3:IQ3")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
ActiveCell.Offset(2, 0).Select
End If
End With
End If

Sheets("Tracker").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("LOOKUP").Visible = False
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
Check what is happening here:-

Code:
Application.Goto Rng, True
ActiveCell.Offset(2, 0).Select

have you selected what you want to copy?

then here:-

Code:
Sheets("Tracker").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

having switched to "Tracker" you paste wherever the cursor has landed it appears.

Hope this helps
 

jaclynresendez

New Member
Joined
Jan 20, 2009
Messages
19
Thank you for the response, Titian. Yes, I've selected what I wanted to copy. That part is ok. I had a feeling it was that second part you highlighted. I just don't know how to correct it. I know I have something out of order here. Looking at my code it doesn't even make sense to me (prolly as a result from writing it at 3am drowning in Starbucks).

I imagine I need something between those two lines of code to direct it where to paste (ie. find the date from Paste! in Tracker! and step down twice)
. Any suggestions?
 

jaclynresendez

New Member
Joined
Jan 20, 2009
Messages
19
I don't mean to repost, but I just sent the entire spreadsheet to a friend without modifications and Find_Last sub works for him. Why would it not work for me? If he just runs the Find_Last macro by itself, it'll find the date and step down twice. If I run it by itself, the activecell does not change.
 

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567

ADVERTISEMENT

Given what you posted above its difficult to say what is wrong with your version.

Is it possible for you to take a copy of exactly what you sent to your friend, rename and save it, the see if it works for you.

If it does then you're OK with that copy, if it doesn't then its a case of identifying the differences between your setup and his.
 

NetBoyz

New Member
Joined
Feb 15, 2007
Messages
3
Couple of things. The application.goto makes the sheets.select redundant, so I eliminated that in the code below. Also, if I read your comments right, you're running a prior sub that leaves data on the clipboard to be pasted in. I think the clipboard's getting trashed which is why the pastespecial fails. Assuming the data you're expecting on the clipboard is text, here are some mods to your code that may work for you:

In Tools > References, check "Microsoft Forms 2.0 Object Library" so you can access the clipboard. The following code checks the clipboard and retrieves text from it, then proceeds. It avoids use of pastespecial.

Public Sub Find_Last()
Dim FindString As String
Dim Rng As Range
Dim clipObject As MSForms.DataObject
Dim clipData As String

'Get clipboard content
Set clipObject = New MSForms.DataObject
clipObject.GetFromClipboard
'Ignore if clipboard data object contains other than text
If clipObject.GetFormat(1) Then
clipData = clipObject.GetText
FindString = Sheets("Paste").Range("B2").Value
If Trim(FindString) <> "" Then
With Sheets("Tracker").Range("A3:IQ3")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
ActiveCell.Offset(2, 0).Select

End If

End With

End If

ActiveCell.Value = clipData
Application.CutCopyMode = False
Sheets("LOOKUP").Visible = False

End If

End Sub
 

jaclynresendez

New Member
Joined
Jan 20, 2009
Messages
19
Thank you. Great suggestion. I did rewrite some of my code to simplify it, but I wasn't having a problem with the copy/paste part.

for some reason, on my computer, it is not "finding" the date [Sheets("Paste").Range("B2")] in the tracker, even though it is clearly there. if i create an input box that forces the user to manually input the date then it finds it on the spreadsheet, but i don't want the user to hafta do that. I dunno why the findstring is not working for me. i ran a simple test findstring in another workbook and it worked fine. I'm at a lost.
 

NetBoyz

New Member
Joined
Feb 15, 2007
Messages
3
One more thought then... is the date at Sheets("Paste").Range("B2").Value formatted the same as the dates in Sheets("Tracker").Range("A3:IQ3")? the Find seems sensitive to this.
e.g., 4/9/1999 won't match 9-Apr-99. db
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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
Top