No records found

[ojackiec]

I'm having a problem with a piece of my code. It is supposed to find the records in the Master Equipment List that have an M in column G. Column G only contains one of three letters: M, Q, or A. The code generates a spreadsheet called the Monthly Inspection Log and places certain columns from the row containing an M into the sheet. For background, my macro is contained in one location. It is opened and then another file is opened that contains the Master Equipment List. The macro places a new menu on the bar that allows the user to create different reports.
The code completes itself but doesn't display any records. I know that they exist in my sample file I'm using to debug my code. I get the message box at the end asking if I want to print but it hasn't found any records. Any ideas would be VERY welcome!! I have only been coding in VBA for about 3 weeks and I know my code is probably sloppy so bear with me. I'm also not a programmer but an environmental specialist! Thanks again for any help!!

Here's where I define:

Sub Monthly()
Dim ws As Worksheet
Dim FromSheet As Worksheet, ToSheet As Worksheet
Dim FromRow As Long, ToRow As Long
Dim FindThis As Variant
Dim rng As Range, FirstAddress As String, FoundCell As Object
Dim obj As Object, cellsDone$
Dim result As Variant


And here's where I'm having problems:

Application.Calculation = xlCalculationManual
Set FromSheet = ActiveWorkbook.Worksheets("Master Equipment List")
Set ToSheet = ActiveWorkbook.Worksheets("Monthly Inspection Log")
ToRow = 2
FindThis = "M"
With FromSheet.Cells
With FromSheet
Set rng = Range("G2", Range("G5000").End(xlUp))
End With

Set FoundCell = rng.Find(FindThis, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
FromRow = FoundCell.Row
Do
ToSheet.Cells(ToRow, 1).Value = .Cells(FromRow, 1).Value
ToSheet.Cells(ToRow, 2).Value = .Cells(FromRow, 2).Value
ToSheet.Cells(ToRow, 3).Value = .Cells(FromRow, 3).Value
ToSheet.Cells(ToRow, 4).Value = .Cells(FromRow, 4).Value
ToSheet.Cells(ToRow, 5).Value = .Cells(FromRow, 5).Value
ToSheet.Cells(ToRow, 6).Value = .Cells(FromRow, 11).Value


With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With ToSheet.Range("A" & ToRow, "H" & ToRow).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ToRow = ToRow + 1

Set FoundCell = rng.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And _
FoundCell.Address <> FirstAddress

End If

End With

result = MsgBox("Print Monthly Inspection Log?", vbYesNo)
If result = vbYes Then ws.PrintOut

With ws
.Name = company.ReportingMonth.Value & "Inspection Log"
End With
End Sub
Thanks again!!

[lucas]

Hi ojackiec,
I copied this code to a new workbook and named 2 worksheets
Master Equipment List
and
Monthly Inspection Log

when I ran the code it copied the data to the Monthly Inspection Log sheet ok.

You imply in your discussion that

The code generates a spreadsheet called the Monthly Inspection Log

but this line of code implies that it just looks for that sheet in the active workbook....


Set ToSheet = ActiveWorkbook.Worksheets("Monthly Inspection Log")



I don't see any code that refers to a different workbook. If you could clean up(take out any propriotory info) your workbook or workbooks and attach them to a post here so we could take a look at them maybe someone could help you get this sorted out.

have attached what I was able to figure out from your code to this post.

[ojackiec]

I create the sheet prior to running the search. I just didn't want to consume space with the whole macro. I'm attaching a sample dataset. I'll post another message with the macro file. I don't understand why your's works and mine doesn't.
I know the code is messy. I haven't been doing this long.
Thanks for the help

[ojackiec]

Here's the macro file.

Thanks again.

[lucas]

Hi ojackiec,
A couple of things I noticed in your garys_macro new.xls
in the macro Monthly which is the one you run from your drop down menu ...it is asking for information that is in a different closed workbook, that is the sample.xls

also it is asking for a specific sheet in the sample.xls

right off the bat I would say that you need to call your from sheet something like this:

Set FromSheet = Workbooks.Open("f:\Temp\sample.xls").Worksheets("Master Equipment List")


of course you will have to change the path. Also your sample.xls does not have a sheet named Master Equipment List and so I would say you need to change the name of sheet one in the example.xls to Master Equipment List

I didn't have much time to look at this so these are just some initial observations....another thing you might look at is deleting your menu when you close the workbook....but thats another issue altogether.

Hope this gets you started in the right direction. Will try to stop by when I can to see how your doing.

[ojackiec]

I appreciate it. The sample I sent is just a few rows from the actual sheet I'm working with to debug. The actual sheet has about 1000 rows of data so I just took a few rows for the sample. I guess I forgot to rename the sample sheet.
Also, what we do is open Gary_macro new and then open the workbook for whichever client we want to run reports for. That way both files are open. This code will be used to run reports for many different files which is why I've left the menu up there.

[ojackiec]

Something else, I've been using code similar to this except with a For i and Next i to find numerical data for another report. It works just fine. If you'll look at Garys_macro new, it's in the LDAR code. I understand why I can't use the same exact code but I don't understand why looking for a letter isn't working.
I was hoping it was just something small I was misisng. I guess not:(

[ojackiec]

I've made a couple of small changes to my code. Now I get the first record 7 times and then it quits. So it still isn't looping right. But at least it's finding a record now! Here's the code I changed:

With FromSheet
Set rng = .Range("G2", .Range("G5000").End(xlUp)) 'added periods before Range
End With

So again the question becomes, why isn't it looping correctly?

Thanks again for any help.

[ojackiec]

I'm still having problems if anyone has any suggestions.
Thanks!!