Modify an unknown range of cells

[timmy2uk]

hey all, been trying this for a while...i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...

for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay

so values in column a becomes like those shown above. any help would be greatly appreciated

cheers
timmy

 

[brian.wethington]

Would something like this do?

Option Compare Text
Sub Letter()

x = Cells(Rows.Count, 1).End(xlUp).Row
For Each Ce In Range("A2:A" & x)
    Select Case Ce.Value
        Case "usa"
            Ce.Value = "USA"
        Case Else
            Cells(1, 1) = "=proper(" & Ce.Address & ")"
            Ce.Value = Cells(1, 1).Value
    End Select
Next Ce
Cells(1, 1).Clear
MsgBox "Completed"

End Sub

I don't know what other types of unusual scenarios or how many there might be but you could work your way through them perhaps.



~Edit, wow alot happens while a person might be away.

 

[timmy2uk]

ok, i think i may be getting at what you're saying...but then again i may not (having one of those days lol)

are you saying that i need to tell VBA/Excel a rule saying that say
If cell value equals "Usa", then change cell value to "USA"
if thats what you are trying to say then i get that i'm gonna have to put these many rules somewhere, but not sure where. if i'm going to do it through VBA i would type multiple "If" statements...Correct?
And, again through a formula, multiple "If" statements...Correct?

 

[Norie]

I think you've got what I mean.

There are a few ways to go about this sort of thing.

You could list all the possibilities and what they should be changed to somewhere and use that in either code or formula.

If using code, yes you could use multiple ifs, but the Select Case approach as illustrated by Brian is probably a better way to go.

The major problem with this sort of thing is the multiple possibilities/rules etc.

If this is a one-off I'm sure code can be created to do it, but what if the 'rules' change?

A better solution might be to sort the problem at the source.

How you would do that would really depend on how the data is being received/input.

 

[timmy2uk]

Would something like this do?

Option Compare Text
Sub Letter()

x = Cells(Rows.Count, 1).End(xlUp).Row
For Each Ce In Range("A2:A" & x)
    Select Case Ce.Value
        Case "usa"
            Ce.Value = "USA"
        Case Else
            Cells(1, 1) = "=proper(" & Ce.Address & ")"
            Ce.Value = Cells(1, 1).Value
    End Select
Next Ce
Cells(1, 1).Clear
MsgBox "Completed"

End Sub

I don't know what other types of unusual scenarios or how many there might be but you could work your way through them perhaps.



~Edit, wow alot happens while a person might be away.

Brian, that didn't work. it iterates through each cell in row a1 and converts the text to PROPER, it just doesn't match the specific cases. appreciate the effort though =)

 

[timmy2uk]

You could list all the possibilities and what they should be changed to somewhere and use that in a formula.

i've tried this approach, but am hampered by excel only allowing 7 if statements. i'm guessing there's no way around that?

How you would do that would really depend on how the data is being received/input.

data is being exported from a spreadsheet from NTL. I was hoping just to export the list of countries, but NTL isn't so "fussy" with the formatting, but sadly i need to be (grrrrr)

 

[brian.wethington]

it iterates through each cell in row a1

Huh?????
do you mean it iterates through each cell in column A except for A1?

it just doesn't match the specific cases

You're right, I only match one specific cases (as I don't know any of your other specific cases, usa). If the cell value is not "usa", "Usa", "USa", "USA", or any variation of that sort than it uses the proper function. You will have to write in some more cases for it to work for you I am sure.


~edit fixed my quote tags.

 

[Norie]

Why use IF?

There are plenty of other functions eg VLOOKUP.

 

[timmy2uk]

it iterates through each cell in row a1

Huh?????
do you mean it iterates through each cell in column A except for A1?

Sorry, thats what i meant. it ends up deleting the data in cell 1

it just doesn't match the specific cases

You're right, I only match one specific cases (as I don't know any of your other specific cases, usa). If the cell value is not "usa", "Usa", "USa", "USA", or any variation of that sort than it uses the proper function. You will have to write in some more cases for it to work for you I am sure.

my mistake on the cases, i typed in a slightly wrong test case. whoops

thanks again for this, it is greatly appreciated =)

 

[timmy2uk]

Why use IF?

There are plenty of other functions eg VLOOKUP.

not sure how to use VLOOKUP and wouldn't mind checking to see if it's faster. i've had a brief look at VLOOKUP on excel help but to be honest, couldn't make much heads or tails of it. any help on how to use it for this scenario would be greatly appreciated

 

[brian.wethington]

Sorry, thats what i meant. it ends up deleting the data in cell 1

If A1 contains data that needs to be checked as well this is a simple change you can make

Option Compare Text
Sub Letter()

x = Cells(Rows.Count, 1).End(xlUp).Row
For Each Ce In Range("A1:A" & x)
    Select Case Ce.Value
        Case "usa"
            Ce.Value = "USA"
        Case Else
            Cells(x + 1, 1) = "=proper(" & Ce.Address & ")"
            Ce.Value = Cells(x + 1, 1).Value
    End Select
Next Ce
Cells(x + 1, 1) = vbNullString
MsgBox "Completed"

End Sub

But, I truly think that vlookup might work out better for you

Basically you would make a table that in column 1 contained the possible entries that you would need to change, column 2 would contain what you want the data in column 1 changed to. Probably naming that table then use this as your formula.

=Vlookup(A2, Lookuptable, 2, 0)
dragging down as needed.