Excel VBA - A couple of questions from newbie

bbrotherton · 09-12-2007, 10:12 AM

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.

Code:

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** · 09-12-2007, 01:57 PM

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

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 · 09-13-2007, 08:23 AM

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** · 09-13-2007, 03:23 PM

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.

09-12-2007, 10:12 AM	#1 (permalink)
bbrotherton Registered User Join Date: Sep 2007 Posts: 2 OS: XP	Excel VBA - A couple of questions from newbie 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. Code: 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

09-12-2007, 01:57 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,503 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel VBA - A couple of questions from newbie 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 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! __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

09-13-2007, 08:23 AM	#3 (permalink)
bbrotherton Registered User Join Date: Sep 2007 Posts: 2 OS: XP	Re: Excel VBA - A couple of questions from newbie 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.

09-13-2007, 03:23 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,503 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel VBA - A couple of questions from newbie 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner