Request for explaination - Functions - Loops in VBA

gazdzid
gazdzid used Ask the Experts™
on
This is in Excel.

The following piece of code works perfect if all I want to do is select every cell on each page 1 at a time.

However if the line below is activated (un-comment) then on the first pass (using F8), it goes right to the biginning of the function. (once hitting the line below)

'Cells(RCI, CCI).Value = Trim(Cells(RCI, CCI).Value)


Code is as follows:

Function Trim()
Worksheets(1).Select
SC = ThisWorkbook.Sheets.Count
SCI = 1
    For SCI = 1 To SC
        Worksheets(SCI).Select
        RC = Range("A1").CurrentRegion.Rows.Count
        cc = Range("A1").CurrentRegion.Columns.Count
            For RCI = 2 To RC     'loops through rows
                For CCI = 1 To cc ' Loops through columns
                    Cells(RCI, CCI).Select
                    'Cells(RCI, CCI).Value = Trim(Cells(RCI, CCI).Value)
                Next CCI
            Next RCI
   Next SCI
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
This could be nothing to do with looping, it could be because you've used the name Trim for the sub.

That's a VBA function, in fact you appear to be using the function in the code.

Try changing the sub name to MyTrim, or something like that.

By the way, you don't need to select cells so you can get rid of this line.
Cells(RCI, CCI).Select

Open in new window

You also don't need to select worksheets, but the code would need a little more work to deal with that.

Something like this.
Function MyTrim()
Dim ws As Worksheet
Dim SC As Long
Dim SCI As Long
Dim RC As Long
Dim RCI As Long
Dim CC As Long
Dim CCI As Long

    SC = ThisWorkbook.Sheets.Count


    For SCI = 1 To SC
        Set ws = Worksheets(SCI)
        RC = ws.Range("A1").CurrentRegion.Rows.Count
        CC = ws.Range("A1").CurrentRegion.Columns.Count
        For RCI = 2 To RC     'loops through rows
            For CCI = 1 To CC    ' Loops through columns

                ws.Cells(RCI, CCI).Value = Trim(ws.Cells(RCI, CCI).Value)
            Next CCI

        Next RCI
    Next SCI
End Function

Open in new window

Author

Commented:
Hey Awesome,

That solved the problem!!

the select was for analysis only.

for visual purposes, I will select the sheets, it gives me some sanity.

Again Thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial