Conditional Find

[ntown88]

Hello, thanks in advance to anyone who can assist me. I have a spreadsheet that gets updated every week. Each week a new sheet is created and formatted by pulling data from a database, and then running macros to format the data. After that I would like to add a macro that will pull data from "last weeks" spreadsheet. The macro I have searches for a number, (in this case, an invoice #), I would like it to cut and paste relative data, IF it finds the same number in the previous worksheet. This is the code I have:


Sub Update_Comments()

'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.

Dim counter As Integer
Dim Findcell, stringtofind As String
Dim dummy As Integer

newshtname = Application.InputBox(Prompt:= _
"Please enter this weeks worksheet name", Type:=2)

oldshtname = Application.InputBox(Prompt:= _
"Please enter the previous weeks worksheet name", Type:=2)

For counter = 7 To 250

Cells(counter, 4).Select
Selection.Copy
stringtofind = Selection
Workbooks.Open Filename:=(oldshtname)
Workbooks(oldshtname).Activate

Set Findcell = Cells.Find(what:=stringtofind)


If Findcell Is Nothing Then
dummy = 1

Else
Findcell.Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate
Selection.Copy
Workbooks(newshtname).Activate
Cells(counter, .Select
ActiveSheet.Paste
End If

Next counter
End Sub


This works half way. It will copy the correct data if it finds the invoice # on the previous sheet, however, if it runs into the situation where it does not find it, it handles the error and continues, but then copies data not
relevant to the current invoice number being looked for.

Any help is appreciated, I can't seem to write the correct code to handle not finding the invoice #....

[Tom Schreiner]

Hi

If Findcell Is Nothing Then 

    dummy = 1 

    'shouldn't you close the active workbook here?

    'Workbooks(newshtname).Activate here?

Tom

[ntown88]

I was using the 'dummy = 1' to set a variable I could in another if statement, had been set after the findcell.select statement, to check to
make sure that it only continued to move the data if the number had been found, but that had not worked properly either..

[Tom Schreiner]

Hi again
I gotta run.
Post all of your code and maybe someone else will help you.
Irregardless, I'll save your thread for later.
Tom

[ntown88]

I had a friend help me with this, anyway, thanks, here is the code, could be useful.

Sub Update_Comments()

'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.

Dim counter As Integer
Dim Findcell, stringtofind, comment As String
Dim dummy As Integer
Dim NewSheet, OldSheet As Worksheet

newshtname = Application.InputBox(Prompt:= _
"Please enter this weeks worksheet name", Type:=2)

oldshtname = Application.InputBox(Prompt:= _
"Please enter the previous weeks worksheet name", Type:=2)

Set OldSheet = Workbooks.Open(Filename:=(oldshtname)).Worksheets(1)
Set NewSheet = Workbooks.Open(Filename:=(newshtname)).Worksheets(1)

For counter = 7 To 250
stringtofind = NewSheet.Cells(counter, 4).Value

Set Findcell = OldSheet.Cells.Find(what:=stringtofind)

If Not (Findcell Is Nothing) Then
comment = OldSheet.Cells(Findcell.Row, Findcell.Column + 4).Value
NewSheet.Cells(counter, .Value = comment
End If

Next counter
End Sub