Code not finding records

My bit of code is not finding any records. It is supposed to search column G in the Master Equipment List and then place certain rows from the matching record into another sheet called Monthly Inspection Log. The Inspection Log generates correctly and the code finished with the message box asking if I want to print. However, it doesn't find any records. I know that they exist in my sample spreadsheet I'm using for testing. Column G will only contain one of three letters: M, Q, or A.
Also for background purposes, my macro is contained in one sheet and a second sheet is opened that contains the Master Equipment List. The macro places a new menu on the bar that allows the user to generate several different reports for whichever file is active.
Any help is much appreciated!! I'm sure my code is a little messy. I've only been coding in VBA for about 3 weeks and am an environmental specialits, not a programmer!
Here's where I define:

Please Login or Register  to view this content.

And here's the loop that isn't proding any records:

Please Login or Register  to view this content.

Thanks again for any help!

Without delving into this too much wouldn't it be easier to just use
data>filter>autofilter on "m" and copy the resulting rows or range of the
row?

or look in vba help index for findnext. There is a good example of using
find
also borders can be done easier.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"ojackiec" <ojackiec.20ebez_1135178103.0052@excelforum-nospam.com> wrote in
message news:ojackiec.20ebez_1135178103.0052@excelforum-nospam.com...
>
> My bit of code is not finding any records. It is supposed to search
> column G in the Master Equipment List and then place certain rows from
> the matching record into another sheet called Monthly Inspection Log.
> The Inspection Log generates correctly and the code finished with the
> message box asking if I want to print. However, it doesn't find any
> records. I know that they exist in my sample spreadsheet I'm using for
> testing. Column G will only contain one of three letters: M, Q, or A.
> Also for background purposes, my macro is contained in one sheet and a
> second sheet is opened that contains the Master Equipment List. The
> macro places a new menu on the bar that allows the user to generate
> several different reports for whichever file is active.
> Any help is much appreciated!! I'm sure my code is a little messy. I've
> only been coding in VBA for about 3 weeks and am an environmental
> specialits, not a programmer!
> Here's where I define:
>
> Code:
> --------------------
>
> 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 the loop that isn't proding any records:
>
> Code:
> --------------------
> 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 for any help!
>
>
> --
> ojackiec
> ------------------------------------------------------------------------
> ojackiec's Profile:
> http://www.excelforum.com/member.php...o&userid=29811
> View this thread: http://www.excelforum.com/showthread...hreadid=495184
>

ToSheet.Range("A" & ToRow, "H" & ToRow). _
BorderAround LineStyle:=xlContinuous, Weight:=xlThin

>ToSheet.Cells(ToRow, 1).Value = .Cells(FromRow, 1).Value
tosheet.range(cells,torow,1),cells(torow,5)).value= _
range(cells(fromrow,1),cells(fromrow.5).value

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Don Guillett" <donaldb@281.com> wrote in message
news:egp8kPkBGHA.3496@TK2MSFTNGP11.phx.gbl...
> Without delving into this too much wouldn't it be easier to just use
> data>filter>autofilter on "m" and copy the resulting rows or range of the
> row?
>
> or look in vba help index for findnext. There is a good example of using
> find
> also borders can be done easier.
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "ojackiec" <ojackiec.20ebez_1135178103.0052@excelforum-nospam.com> wrote
> in message news:ojackiec.20ebez_1135178103.0052@excelforum-nospam.com...
>>
>> My bit of code is not finding any records. It is supposed to search
>> column G in the Master Equipment List and then place certain rows from
>> the matching record into another sheet called Monthly Inspection Log.
>> The Inspection Log generates correctly and the code finished with the
>> message box asking if I want to print. However, it doesn't find any
>> records. I know that they exist in my sample spreadsheet I'm using for
>> testing. Column G will only contain one of three letters: M, Q, or A.
>> Also for background purposes, my macro is contained in one sheet and a
>> second sheet is opened that contains the Master Equipment List. The
>> macro places a new menu on the bar that allows the user to generate
>> several different reports for whichever file is active.
>> Any help is much appreciated!! I'm sure my code is a little messy. I've
>> only been coding in VBA for about 3 weeks and am an environmental
>> specialits, not a programmer!
>> Here's where I define:
>>
>> Code:
>> --------------------
>>
>> 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 the loop that isn't proding any records:
>>
>> Code:
>> --------------------
>> 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 for any help!
>>
>>
>> --
>> ojackiec
>> ------------------------------------------------------------------------
>> ojackiec's Profile:
>> http://www.excelforum.com/member.php...o&userid=29811
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=495184
>>
>
>

If the Master Equipment List isn't the active sheet, then you could be
searching the wrong sheet:

With FromSheet
Set rng = Range("G2", Range("G5000").End(xlUp))
End With

should be

With FromSheet
Set rng = .Range("G2", .Range("G5000").End(xlUp))
End With

--
Regards,
Tom Ogilvy


"ojackiec" <ojackiec.20ebez_1135178103.0052@excelforum-nospam.com> wrote in
message news:ojackiec.20ebez_1135178103.0052@excelforum-nospam.com...
>
> My bit of code is not finding any records. It is supposed to search
> column G in the Master Equipment List and then place certain rows from
> the matching record into another sheet called Monthly Inspection Log.
> The Inspection Log generates correctly and the code finished with the
> message box asking if I want to print. However, it doesn't find any
> records. I know that they exist in my sample spreadsheet I'm using for
> testing. Column G will only contain one of three letters: M, Q, or A.
> Also for background purposes, my macro is contained in one sheet and a
> second sheet is opened that contains the Master Equipment List. The
> macro places a new menu on the bar that allows the user to generate
> several different reports for whichever file is active.
> Any help is much appreciated!! I'm sure my code is a little messy. I've
> only been coding in VBA for about 3 weeks and am an environmental
> specialits, not a programmer!
> Here's where I define:
>
> Code:
> --------------------
>
> 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 the loop that isn't proding any records:
>
> Code:
> --------------------
> 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 for any help!
>
>
> --
> ojackiec
> ------------------------------------------------------------------------
> ojackiec's Profile:
http://www.excelforum.com/member.php...o&userid=29811
> View this thread: http://www.excelforum.com/showthread...hreadid=495184
>