-
Solved: For Each Loop Through Sheet Collection Getting Wrong Results
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]
my site: www.ecboardco.com
was built w/ a majority of the assistance from the board members here... thanks VBAX.
Just because I see something, doesn't mean that what's actually happening is what I see.
You don't get from 0-90 by standing still!
-
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?
my site: www.ecboardco.com
was built w/ a majority of the assistance from the board members here... thanks VBAX.
Just because I see something, doesn't mean that what's actually happening is what I see.
You don't get from 0-90 by standing still!
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
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).
Matt
-
You want to reference both cells
[VBA].Cells(i, "J").Value = Application.WorksheetFunction.Proper(.Cells(i, "J").Value)[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
That took care of it...... thanks
I don't drink, I think I should start.
my site: www.ecboardco.com
was built w/ a majority of the assistance from the board members here... thanks VBAX.
Just because I see something, doesn't mean that what's actually happening is what I see.
You don't get from 0-90 by standing still!
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
my site: www.ecboardco.com
was built w/ a majority of the assistance from the board members here... thanks VBAX.
Just because I see something, doesn't mean that what's actually happening is what I see.
You don't get from 0-90 by standing still!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules