Conditional Find

ntown88

New Member
Joined
May 12, 2002
Messages
3
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, :cool:.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 #....
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi

<pre>
If Findcell Is Nothing Then
dummy = 1
'shouldn't you close the active workbook here?
'Workbooks(newshtname).Activate here?

</pre>

Tom
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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, :cool:.Value = comment
End If

Next counter
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top