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