Find and replace query

voutsy · Dec 13th 2004

Hi,
I have a large data table that contains a column indicating a persons age banding, the bandings are displayed as:
1, 2, 3, 4, and 5.
I want to write a sub to select column D (the age banding column) then to check the value in cell D2 and if value = 1 then replace value with text "Under 18"
if value = 2 then replace with 18-64, if value = 3 then replace with 65-74, if value = 4 then replace with 75+, and if value = 5 then replace with age unknown.

Once the sub has checked replaced cell D2 i want it to do the same for cell D3 and D4 etc until the end of the database (row 1600)

I know this shouldnt be too dificult to do but im new to VBA and need some help, so far my (incorrectly syntaxed) sub looks like this:

Code

Sub age_bander()


Columns("d:d").Select
Range("d2").Activate


If ActiveCell.Value = "1" Then
Replace ActiveCell.Value = "Under 18"




End Sub

Display More

**royUK** · Dec 13th 2004

Re: Find and replace query

Welcome to Ozgrid
Why use VBA ? Just use Ctrl>F and use Excel's Find & Replace!

voutsy · Dec 13th 2004

Re: Find and replace query

I have been using the CTRL+F method previously but have recently been on a VBA course and wanted to try to continue using VBA outside of the course.

I have to do this task (i.e problem in my original post) on a weekly basis and would like to automate the process.

I would appreciate any further help with this.

matt_boy · Dec 13th 2004

Re: Find and replace query

IS this any good?

Code

Sub age_bander()
     
Application.ScreenUpdating = False


     
i = 2


While i < 1601
     
    Columns("d:d").Select
    Range("d" & i).Activate
     
    If ActiveCell.Value = "1" Then
        ActiveCell.Value = "Under 18"
        
    End If


    i = i + 1
         
    Wend
         
Application.ScreenUpdating = True


End Sub

Display More

Hope this helps

Matt

**royUK** · Dec 13th 2004

Re: Find and replace query

Search VBA help for .Find.

**royUK** · Dec 13th 2004

Re: Find and replace query

Just found this example that I created a while ago. Recreates Find & Replace on a UserForm

matt_boy · Dec 13th 2004

Re: Find and replace query

Ammended above code slightly in order to get what u stated:

Code

Sub age_bander()
     
Application.ScreenUpdating = False


     
i = 2


While i < 1600
     
    Columns("d:d").Select
    Range("d" & i).Activate
     
    If ActiveCell.Value = "1" Then
        ActiveCell.Value = "Under 18"
    ElseIf ActiveCell.Value = "2" Then
        ActiveCell.Value = "18 - 64"
    ElseIf ActiveCell.Value = "3" Then
        ActiveCell.Value = "65 - 74"
    ElseIf ActiveCell.Value = "4" Then
        ActiveCell.Value = "75+"
    ElseIf ActiveCell.Value = "5" Then
        ActiveCell.Value = "Age unknown"
   
    End If
    


    i = i + 1
         
    Wend
         
Application.ScreenUpdating = True


End Sub

Display More

Hope this helps

voutsy · Dec 13th 2004

Re: Find and replace query

That worked exactly as i hoped, thank you very much
:thanx:

Will Riley · Dec 13th 2004

Re: Find and replace query

Matty (& voutsy)

Whilst Matty's code works just fine, you ought to consider using Select CAse staements as opposed to multiple IF statements as they are more efficient. Also, selecting & activating cells will slow this code down so it's best not to.

There is probably even more efficient ways to wrtie this but I managed to considerable reduce the process using the following

[vba]Sub age_bander()
Dim lrow As Long

Application.ScreenUpdating = False

For lrow = 2 To 1600
With Cells(lrow, 4)
Select Case .Value
Case Is = 1: .Value = "Under 18"
Case Is = 2: .Value = "18 - 64"
Case Is = 3: .Value = "65 - 74"
Case Is = 4: .Value = "75+"
Case Is = 5: .Value = "Age unknown"
Case Else: .Value = "error code entered"
End Select
End With
Next lrow

Application.ScreenUpdating = True

End Sub[/vba]

Hope you don't mind some constructive friendly criticism

matt_boy · Dec 13th 2004

Re: Find and replace query

No Will not at all, I appreciate the help, I myself am fairly new to VBA. For a relative novice like myself, it is just nice to have code that works! I appreciate the help in understanding good coding techniques. I am teaching myself at the moment pretty much from scrath using this website, so the only way I will learn is if you guys explain why certain methods are used as opposed to others. It is very nice to have the most efficient code possible however being new it is near on impossible to know what is best.

Find and replace query

Participate now!

Share