PDA

View Full Version : For Each-Next code tidying up



KoE
04-20-2009, 08:06 PM
Guys,

This code copies from 1 column into the other; and for that it works fine.
However, I added some column & text formatting underneath and I believe it's not working as expected.



For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
With sh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "V").Value = "" Then
.Cells(i, "V").Value = .Cells(i, "O").Value
End If
Next i
Selection.Columns("V:V").ColumnWidth = 30
Selection.Columns("V:V").WrapText = True
End With

Application.ScreenUpdating = True

Next


Would be glad if someone could look, assess and/or propose tidying up of those statements in bold

Thanks :beerchug:

mikerickson
04-20-2009, 09:30 PM
It looks to me that those two lines will only effect the ActiveSheet rather than the sheets in the array.
They will effect the 22nd column to the right of the Active cell, not column V:V

Perhaps this
Sub test()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
With sh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "V").Value = "" Then
.Cells(i, "V").Value = .Cells(i, "O").Value
End If
Next i

With .Columns("V:V")
.ColumnWidth = 30
.WrapText = True
End With
End With
Next
Application.ScreenUpdating = True
End Sub

KoE
04-21-2009, 02:13 AM
mike,

excellent! many thanks :friends: