Solved: For Each Loop Through Sheet Collection Getting Wrong Results

[YellowLabPro]

The following loop works as it is intended until I add this line-
[VBA]'.Cells(i, "J").Value = Application.WorksheetFunction.Proper(Cells(i, "J").Value)[/VBA]
Once I stick this guy in, the code loops through but it is using the data from the first sheet. I sort of understand why, but not really and I don't know how to correct.

[VBA]
For Each wsSourceSheet In Workbooks("TGSProductsAttribPrep.xls").Worksheets
With wsSourceSheet
lLrws = lr(wsSourceSheet, "A")
For i = 2 To lLrws
If Not IsEmpty(.Cells(i, "G").Value) Then
.Cells(i, "J").Value = "; " & .Range("G1").Value & .Cells(i, "G").Value
End If

If Not IsEmpty(.Cells(i, "H").Value) Then
.Cells(i, "J").Value = .Cells(i, "J").Value & "; " & .Range("H1").Value & .Cells(i, "H").Value
End If

If Not IsEmpty(.Cells(i, "I").Value) Then
.Cells(i, "J").Value = .Cells(i, "J").Value & "; " & .Range("I1").Value & .Cells(i, "I").Value
End If

'.Cells(i, "J").Value = Application.WorksheetFunction.Proper(Cells(i, "J").Value)
Next i
i = 2
wsSourceSheet.Columns("A:K").AutoFit
End With
Next wsSourceSheet

End Sub
[/VBA]

[YellowLabPro]

I deleted the "." prior to cells to stop the reference.
Cells(i, "J").Value = Application.WorksheetFunction.Proper(Cells(i, "J").Value)

This works, but why? Is it not supposed to refenrence the sheet that it is looping through?

[mvidas]

The . does qualify it to the sheet within the With block, however you need it before both references to "Cells", not just the first. It is putting the data from the first sheet into all sheets because the first sheet must be the active one (without it being qualified with the . it is taking from the Default qualifier, or ActiveSheet).

[mdmackillop]

You want to reference both cells
[VBA].Cells(i, "J").Value = Application.WorksheetFunction.Proper(.Cells(i, "J").Value)[/VBA]

[YellowLabPro]

That took care of it...... thanks

I don't drink, I think I should start.

[mdmackillop]

I've found that computers work better when there is a low level of alcohol vapour in the atmosphere. Excessive levels though, cause interference between the wireless keyboard and the receiver, resulting in inexplicable errors.

[YellowLabPro]

Back at you