Solved Find multiple words/values in Excel and add date

Tags:
excel 2007
August 16, 2013 at 07:48:29
Specs: Windows 7
Hi

I am currently using the code below to find multiple values and the copy/paste into another sheet. What I would like to know is if it is possible to add the now date to a column in sheet 1 for the value found in sheet 3?

Option Explicit
Sub BBIB()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column A, copy it top the next row in Sheet2
With Sheets(1).Columns("A")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Sub

Any help much appreciated.


See More: Find multiple words/values in Excel and add date

Report •

#1
August 16, 2013 at 08:38:58
Hi!

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Then please repost your code, as copied from the VBE, so that it is easier for us to read.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 16, 2013 at 12:26:39
I am confused as to where you want the date on Sheet(1). So here is the code I would have used:

Add this to what you have at the top of your code:

Dim nowdate As Date
nowdate = DateValue(Now)

Now you can use 'nowdate' wherever you'd like. For example, to test the code, I just had it pop up in a message box.

MsgBox("Today's Date Test: " & nowdate)

Hope this helps.

Law of Logical Argument: Anything is possible if you don't know what you're talking about.

message edited by Newbie10


Report •

#3
August 16, 2013 at 12:35:00
✔ Best Answer
That looks like more work than is required. There's no need to create another variable nor extract the Date from Now.

Just adding this line inside the If-Then loop will accomplish the goal. (I randomly choose Column B for the Date)

Sheets(1).Cells(g.Row, "B") = Date

However, I'd still like to see the code posted in the requested manner. (It's a teaching moment.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question