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

Without delving into this too much wouldn't it be easier to just use
datafilterautofilter 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

"ojackiec" wrote in
message ...

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

"Don Guillett" wrote in message
...
Without delving into this too much wouldn't it be easier to just use
datafilterautofilter 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

"ojackiec" wrote
in message ...

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" wrote in
message ...

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