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

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub Letter()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
Cells(1, 1) = "=proper(A" & a & ")"
Cells(a, 1) = Cells(1, 1)
Next a
Cells(1, 1) = ""
MsgBox "Completed"
End Sub

run the macro and see if this is what you are looking for.
Ravi

 

[timmy2uk]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub Letter()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
Cells(1, 1) = "=proper(A" & a & ")"
Cells(a, 1) = Cells(1, 1)
Next a
Cells(1, 1) = ""
MsgBox "Completed"
End Sub

run the macro and see if this is what you are looking for.
Ravi

sadly not...but i think it may be part way there. what it does is go through the columns changing the value to PROPER, but this causes a problem when it comes to say the value USA, as PROPER(USA) becomes Usa, where as i need it to be USA.

 

[Norie]

So what are the rules?

How does Excel/VBA know to do that?

 

[timmy2uk]

just thinking, would it be easier to do it along these lines:
iterate through column b
for each row in column b
case cell value = "Usa"
cell value = "USA"
and so on for all the cases that i currently have

???

 

[Norie]

That could be one way to go but how are you going to pick up on all the possibilities?

 

[timmy2uk]

thats the thing, i have no idea. i'm really not sure how to go through column b and look at the values in each row, and if need be change that row to the correct country. not up on my vba code knowledge

 

[Norie]

It's not really anything to do with VBA.

It's to do with the logic behind what you want to do.

eg why change Usa to USA and Ukraine - Mobile (Umc) to Ukraine - Mobile (UMC) etc

 

[timmy2uk]

for formatting reaons. later this will all be exported out of excel for billing purposes and the person who wants this wants it to look "proper". personally i am with you and in my mind it makes no difference. but it's the last little thing left to do.

i've managed to get a formula to do this, but that obviously only allows 7 if statements and there are a lot more than that.

it's uberly frustrating

but any help would be appreciated

 

[Norie]

I realise this is for formatting reasons.

What I'm trying to point out is that somehow you need to 'tell' Excel/VBA the rules/logic behind what you want to do.