YellowLabPro
09-14-2007, 07:26 AM
I want to run the trim function on two sheets.
The following code does not make it to the second sheet, it gets to the next Ai, "ArrayInteger", but that does not tell it to perform the trim on the second sheet, rather it starts over again and runs on the first sheet.
My variable counters are also a problem, they contain the value of the last variable count, "which is to be expected". What is the best way to reset them, place another "i = 4" after the Next i, and place this line a second time after "Next j"-
lrwSource = .Cells(Rows.Count, 1).End(xlUp).Row
Sub RemoveSpaces()
Dim Ws As Worksheet
Dim i As Integer, j As Integer, lrwSource As Long, Ai As Long
Dim ArrayWs
ArrayWs = Array("TGFF", "TGVB")
Set Ws = Worksheets(ArrayWs(Ai))
With Ws
For Ai = LBound(ArrayWs) To UBound(ArrayWs)
lrwSource = .Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 4
For i = 4 To lrwSource
.Cells(i, j).Value = WorksheetFunction.Trim(.Cells(i, j).Value)
Next i
Next j
Next Ai
End With
MsgBox "Done"
End Sub
The following code does not make it to the second sheet, it gets to the next Ai, "ArrayInteger", but that does not tell it to perform the trim on the second sheet, rather it starts over again and runs on the first sheet.
My variable counters are also a problem, they contain the value of the last variable count, "which is to be expected". What is the best way to reset them, place another "i = 4" after the Next i, and place this line a second time after "Next j"-
lrwSource = .Cells(Rows.Count, 1).End(xlUp).Row
Sub RemoveSpaces()
Dim Ws As Worksheet
Dim i As Integer, j As Integer, lrwSource As Long, Ai As Long
Dim ArrayWs
ArrayWs = Array("TGFF", "TGVB")
Set Ws = Worksheets(ArrayWs(Ai))
With Ws
For Ai = LBound(ArrayWs) To UBound(ArrayWs)
lrwSource = .Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 4
For i = 4 To lrwSource
.Cells(i, j).Value = WorksheetFunction.Trim(.Cells(i, j).Value)
Next i
Next j
Next Ai
End With
MsgBox "Done"
End Sub