Unwanted data displayed when running macro

[mystie]

Hello everybody!

I have a sheet which looks like an invoice. There is a City value in cell H2. Other data of concern are: Customer in E1, Address in H1, Phone in H3, Total E18, Amount Due in J15.

Now the invoice repeats itself in the same sheet. So there are multiple invoices in same sheet. Each invoice will contain different values.

I have a second sheet where I want to fill in data from the invoices based on a City value entered in this second sheet.

Algorithm wise:
Enter City in second sheet
Find every occurrence of that City in Invoices
For each invoice with that City read the Customer, Address, Phone, Total, Amount Due data and populate the second sheet.

My research on the net led to the following code which I modified to adapt to my needs.

Dim srchCity As String
  Dim shInvoice As Worksheet
  Dim fnd As String, FirstFound As String
  Dim FoundCell As Range, rng As Range
  Dim myRange As Range, LastCell As Range
  Dim nextRow As Long
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  
  srchCity = ActiveSheet.Range("B3").Value
  Set shInvoice = ThisWorkbook.Worksheets("Sales Invoice")
  Set shDebtor = ThisWorkbook.Worksheets("Debtor List")
  ActiveSheet.UsedRange.Offset(4).EntireRow.Clear
  
  Set myRange = ThisWorkbook.Worksheets("Sales Invoice").Range("H:H")
  Set LastCell = myRange.Cells(myRange.Cells.Count)
  Set FoundCell = myRange.Find(what:=srchCity, after:=LastCell, LookIn:=xlValues)

  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
    Do
      nextRow = shDebtor.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row + 1
      
      shDebtor.Cells(nextRow, "A").Value = FoundCell.Offset(-1, -3).Value
      shDebtor.Cells(nextRow, "B").Value = FoundCell.Offset(-1, 0).Value
      shDebtor.Cells(nextRow, "C").Value = FoundCell.Offset(1, 0).Value
      shDebtor.Cells(nextRow, "D").Value = FoundCell.Offset(16, -3).Value
      shDebtor.Cells(nextRow, "E").Value = FoundCell.Offset(13, 2).Value
      
      nextRow = nextRow + 1
      Set FoundCell = myRange.FindNext(FoundCell)

    Loop While (FoundCell.Address <> FirstFound)
  End If

  Set rng = FoundCell

  Application.ScreenUpdating = True

This code actually does its job of finding the City and getting the data into the second sheet. However, in addition to the data it's also adding 2 labels there are in the invoice sheet in cells E5 and H5. I can't find the error in the code which is making this happen.

[/IMG]

Can you please help?

Thank you in advance.

 

[MARK858]

Without seeing the actual workbook it is hard to assess what is actually happening but before we get to that stage have you stepped through the code with F8 so that you can see where the issue is?

 

[mystie]

Here's a picture of the Invoice Sheet.

[/IMG]

And yes, I've tried debugging but I can't find the error.

 

[Fluff]

What if you use

 Set foundCell = myrange.Find(srchCity, LastCell, xlValues, xlWhole, xlByRows, xlNext, False, False)

 

[mystie]

What if you use

 Set foundCell = myrange.Find(srchCity, LastCell, xlValues, xlWhole, xlByRows, xlNext, False, False)

Works perfect!

Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.