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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

timmy2uk

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
So what are the rules?

How does Excel/VBA know to do that?
 

timmy2uk

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
That could be one way to go but how are you going to pick up on all the possibilities?
 

timmy2uk

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

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
Top