YellowLabPro
09-05-2007, 12:56 PM
In this loop, I need to change the focus from the ActiveSheet to the rest of the sheets in the workbook.
I refer to the current sheet, the activesheet w/ the variable oWst.
lLrws is the last row variable, LR(oWst, "A") is the function to find the last row.
Can I still use the function, or do I need to use a variable instead?
Function LR(Ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = Ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function
Set oWst = ActiveSheet
For Each Ws In Workbooks("TGSProductsAttrib.xls").Worksheets
lLrws = LR(oWst, "A")
For i = 2 To lLrws
If Not IsEmpty(Cells(i, "D").Value) Then
'If IsEmpty(Cells(i, "G").Value) And Not IsEmpty(Cells(i, "D").Value) Then 'Or Not IsEmpty(Cells(i, "E").Value) Or Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Range("D1").Value & Cells(i, "D").Value & "; "
End If
If Not IsEmpty(Cells(i, "E").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("E1").Value & Cells(i, "E").Value & "; "
End If
If Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("F1").Value & Cells(i, "F").Value & "; "
End If
Next i
i = 2
Columns("A:G").AutoFit
Next Ws
I refer to the current sheet, the activesheet w/ the variable oWst.
lLrws is the last row variable, LR(oWst, "A") is the function to find the last row.
Can I still use the function, or do I need to use a variable instead?
Function LR(Ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = Ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function
Set oWst = ActiveSheet
For Each Ws In Workbooks("TGSProductsAttrib.xls").Worksheets
lLrws = LR(oWst, "A")
For i = 2 To lLrws
If Not IsEmpty(Cells(i, "D").Value) Then
'If IsEmpty(Cells(i, "G").Value) And Not IsEmpty(Cells(i, "D").Value) Then 'Or Not IsEmpty(Cells(i, "E").Value) Or Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Range("D1").Value & Cells(i, "D").Value & "; "
End If
If Not IsEmpty(Cells(i, "E").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("E1").Value & Cells(i, "E").Value & "; "
End If
If Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("F1").Value & Cells(i, "F").Value & "; "
End If
Next i
i = 2
Columns("A:G").AutoFit
Next Ws