Move or offset one space if certain criteria is met.

[Livin404]

Hello, I have a large database and one of the columns represents a Country or a State within the US. I'm using the three letter code for the country and the two letter code for the state.
After using a pivot table I intend on using a map. Since I cannot have both countries and states in one column, I need to have two MAPS. One for the US and one for everything else.
All my location codes (countries/state) go in Column D within the Database.

I need a macro will it will go through all of Column D that has data

Range(Range("D2"), Range("D2").End(xlDown))

. then I need the code that will specifically look for date within only two letters in column D. If there is I need letters move to Column E using the offset code.

Thank you,

 

[StephenCrump]

Do you mean like this?

With Range(Range("D2"), Range("D2").End(xlDown))
    .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
    .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
End With

	A	B	C	D	E
1				BEFORE
2				AZ
3				FL
4				AUS
5				GBR
6				SUI
7				CAN
8				UT
9				CA
10				AUS
11				AUS
12
Sheet1

	A	B	C	D	E
1				AFTER
2					AZ
3					FL
4				AUS
5				GBR
6				SUI
7				CAN
8					UT
9					CA
10				AUS
11				AUS
Sheet1

 

[Livin404]

The example you provided was perfect. Obviously I think I'm missing something. I used exactly what you had, and it clears out everything. I think I need a cut command in there some place.

This is what I used and it clears everything out in Column D.

Thank you so much for your input thus far.

Sub Location()
With Range(Range("D2"), Range("D2").End(xlDown))
    .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
    .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
End With
       
End Sub

 

[StephenCrump]

This is what I used and it clears everything out in Column D.

The code relies on the data length in column D being either two or three, as you've stated.

I suggest you check the LEN(). Do you perhaps have leading or trailing space or non-printing characters?

 

[Livin404]

There is no leading spaces that I am aware of. I however, have a vlookup code in Column D.

=IFERROR(VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),"")

I need that just to make sure I have the right 3 letter codes (ISO) for the countries and two letter for the state. That is where I want your macro to takeover.
Thank you,

 

[StephenCrump]

If you have ruled out LEN() as the problem, my next question would be whether you have other code running?

Based on the file you uploaded in this thread: Trying to use Index with Match but I get error code 2042 it appears that your Sub Worksheet_Change may be the culprit.

Try wrapping my code:

Application.EnableEvents = False
Sub Location()
With Range(Range("D2"), Range("D2").End(xlDown))
    .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
    .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
End With
Application.EnableEvents = True

 

[Livin404]

I added the change you suggested, and ended up with a a compile error.

I provided a truncated version of the Excel file. I hope it may help you to see the full picture.

Dropbox

www.dropbox.com

Thank you,

 

[StephenCrump]

'You have
Application.EnableEvents = False
Sub Location()
    
    With Range(Range("D2"), Range("D2").End(xlDown))
        .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
        .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
    End With
    Application.EnableEvents = True

End Sub

'You need
Sub Location()

    Application.EnableEvents = False
    With Range(Range("D2"), Range("D2").End(xlDown))
        .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
        .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
    End With
    Application.EnableEvents = True

End Sub

But given your data is in column E, not D, just change to:

Sub Location()

    Application.EnableEvents = False
    With Range(Range("E2"), Range("E2").End(xlDown))
        .Offset(, 1).Value = Evaluate("IF(LEN(" & .Address & ")=2," & .Address & ","""")")
        .Value = Evaluate("IF(LEN(" & .Address & ")=3," & .Address & ","""")")
    End With
    Application.EnableEvents = True

End Sub

(Or you could use a VLOOKUP formula in columns E and F, returning a value to column E if the length of the lookup was 3, or column F if length 2)

 

[Livin404]

You're right, I referred to the incorrect column. I think my issues would have been good if I got it right in the first place. You were spot on .

Thank you!

 

[StephenCrump]

No problem. I'm glad you got it working.