How can I expand this code

[Dougie1]

Hi all,

Using Excel 2003

I currently am using this module of code :

Sub ID
    
    Dim Rng1, c As Range, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng1 = Range("A1:A" & LastRow)
    For Each c In Rng1
    If UCase(c) = "1" Then
    c = c.Offset(0, 1)
    End If
    Next

End Sub

which looks at column A and finds every occurence of a 1 then offsets to pull the value from column B into column A where it has found a 1.

How would I expand this code so that it will also look at column A for every occurence of a 2, 3, 4, 5, 6, 7, 8, 9 or 10 but will offset to pull the value from column C into column A where it has found a 2, 3, 4, 5, 6, 7, 8, 9 or 10 ?

TIA

D

 

[the_original_invisible]

How about this?

Sub ID_2()
    
    Dim Rng1, c As Range, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng1 = Range("A1:A" & LastRow)
    For Each c In Rng1
    Select Case UCase(c)
    Case "1"
    c = c.Offset(0, 1)
    Case "2" To "10"
    c = c.Offset(0, 2)
    Case Else
    End Select
    Next

End Sub

Lee

 

[NdNoviceHlp]

Something like this? Dave

Sub ID()
    
    Dim Rng1, c As Range, LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng1 = Range("A1:A" & LastRow)
    For Each c In Rng1
    If UCase(c) = "1" Then
    c = c.Offset(0, 1)
    Else
    If UCase(c) > 1 And UCase(c) < 11 Then
    c = c.Offset(0, 2)
    End If
    End If
    Next

End Sub

 

[Dougie1]

Thanks guys - I'll give it a go and get back to you.

D

 

[Dougie1]

Ended up using Dave's solution and it worked a treat.

As ever - this site delivers.

Thanks again guys.

All the best

D