Modify an unknown range of cells

timmy2uk

New Member
Joined
Jul 25, 2007
Messages
12
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi
Paste the following codes in the macro window ( Alt F11)

Code:
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
 
Upvote 0
Hi
Paste the following codes in the macro window ( Alt F11)

Code:
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.
 
Upvote 0
So what are the rules?

How does Excel/VBA know to do that?
 
Upvote 0
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

???
 
Upvote 0
That could be one way to go but how are you going to pick up on all the possibilities?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,222,118
Messages
6,164,072
Members
451,870
Latest member
Nikhil excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top