# Excel VBA - A couple of questions from newbie



## bbrotherton (Sep 12, 2007)

I am trying to write a macro that cleans up some data that we are getting in. One of the things I am trying to do is split up some data in the cells. I have been inserting new columns and using them. Instead, I would like to find the last column of data. I found some code, but it returns a number and doesn't work in the Range() function. 

Also, is there another way to loop through the cells other than using a select? I have been told that this will slow down the code, but I don't know how else to do it.

Any information would be greatly appreciated.

Thanks

PS - here is a sample of my loop using Select - There are a couple other If statements, but you get the idea.


```
Do Until x = 60
Range("F" & x).Select
        If InStr(ActiveCell.Value, "(") <> 0 Then
            ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Value, InStr(ActiveCell.Value, "(") + 1, Len(Trim(ActiveCell.Value)) - 1)
            ActiveCell.Offset(0, 1).Value = Left(ActiveCell.Offset(0, 1), InStr(ActiveCell.Offset(0, 1), ")") - 1)
            ActiveCell.Value = Left(ActiveCell.Value, InStr(ActiveCell.Value, "(") - 1)
        End If
x=x+1
Loop
```


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

You are correct about Select - you don't actually need to select cells to work with them. An although there's not much wrong with your looping, I prefer to set your range and then simply loop through the set range using a For Each...Next construct. Also assign variables - it's easier to code and quicker for Excel. Once it knows to what the variable has been assigned it will retain that information until the routine finishes. In your code, Excel has to work out to what ActiveCell refers - and it has to do that each time it finds such a reference. Here's my version of your code

```
Sub Testing()
Dim myRng As Range
Dim c As Range

Set myRng = Sheets("Sheet1").Range("F1:F60")

For Each c In myRng
    If InStr(c.Value, "(") <> 0 Then
        c.Offset(0, 1).Value = Mid(c.Value, InStr(c.Value, "(") + 1, Len(Trim(c.Value)) - 1)
        c.Offset(0, 1).Value = Left(c.Offset(0, 1), InStr(c.Offset(0, 1), ")") - 1)
        c.Value = Left(c.Value, InStr(c.Value, "(") - 1)
    End If
Next c
    
End Sub
```
Remember, in VBA there are many ways of doing the same thing!


----------



## bbrotherton (Sep 12, 2007)

I eventually did replace the counter with a variable. I know that is bad, but at the time, I hadn't figured out how to find the last used row. Now I know and replace the number with a variable. I also have a range now instead of using the select. I have whiddled it down to only one thing that I am having problems with logic wise.....I will figure it out!!

I am sure that there are different and possibly better ways to do my entire code, but I am pretty impressed with myself for figuring out so much on my first Excel programming day.  I know how to program in Access, just not any other office programs.


----------



## Glaswegian (Sep 16, 2005)

Well if that was your first effort, then it was pretty good. Although the macro recorder produces much superfluous code, it can be useful for showing Objects and Properties that you can then use in written code.


----------

