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
'.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