O
ojackiec
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
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