PDA

View Full Version : Solved: For Each Loop Through Sheet Collection Getting Wrong Results



YellowLabPro
09-12-2007, 10:18 AM
The following loop works as it is intended until I add this line-
'.Cells(i, "J").Value = Application.WorksheetFunction.Proper(Cells(i, "J").Value)
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.


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

YellowLabPro
09-12-2007, 10:25 AM
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
09-12-2007, 10:34 AM
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
09-12-2007, 10:36 AM
You want to reference both cells
.Cells(i, "J").Value = Application.WorksheetFunction.Proper(.Cells(i, "J").Value)

YellowLabPro
09-12-2007, 11:00 AM
That took care of it...... thanks :clap:

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

mdmackillop
09-12-2007, 11:14 AM
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. :beerchug:

YellowLabPro
09-12-2007, 12:01 PM
Back at you