I am trying to automate a process on a spreadsheet. Where Cell D2 begins
with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process then
I want it to label everything else on column E "INTL". Any help is greatly
appreciated. thanks.

The code would look like the following:

Sub AAA()
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.Intersect(ActiveSheet.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Case Else
End Select
End Sub

If you want it to run automatically when a cell is changed, use the
Change event instead. In the worksheet's code module, use

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.EnableEvents = False
Application.Intersect(Me.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Application.EnableEvents = True
Case Else
End Select
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 22 Feb 2010 11:15:07 -0800, "dizzl3e"
wrote:

I am trying to automate a process on a spreadsheet. Where Cell D2 begins
with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process then
I want it to label everything else on column E "INTL". Any help is greatly
appreciated. thanks.

The answer to your question depends on whether the rest of Column E should
fill with "INTL" only if D2 is 00, 11 or CA or if it should be filled with
"INTL" no matter if D2 is 00, 11 or CA or not. For the first condition...

Sub DOMorINTL()
If InStr("*00*11*CA*", "*" & Range("D2").Value & "*") Then
Range("E2").Value = "DOM"
Range("E3:E" & ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row).Value = "INTL"
End If
End Sub

For the second condition...

Sub DOMorINTL()
Range("E2:E" & ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row).Value = "INTL"
If InStr("*00*11*CA*", "*" & Range("D2").Value & "*") Then
Range("E2").Value = "DOM"
End If
End Sub

--
Rick (MVP - Excel)


"dizzl3e" wrote in message
...
I am trying to automate a process on a spreadsheet. Where Cell D2 begins
with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process
then I want it to label everything else on column E "INTL". Any help is
greatly appreciated. thanks.

Thanks, but that didn't work. It labeled E2 as "DOM" which is right, but
the rest on column E was labeled "INT" including the E1 which was the
header.

"Chip Pearson" wrote in message
...
The code would look like the following:

Sub AAA()
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.Intersect(ActiveSheet.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Case Else
End Select
End Sub

If you want it to run automatically when a cell is changed, use the
Change event instead. In the worksheet's code module, use

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.EnableEvents = False
Application.Intersect(Me.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Application.EnableEvents = True
Case Else
End Select
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 22 Feb 2010 11:15:07 -0800, "dizzl3e"
wrote:

I am trying to automate a process on a spreadsheet. Where Cell D2 begins
with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process
then
I want it to label everything else on column E "INTL". Any help is
greatly
appreciated. thanks.

the rest on column E was labeled "INT" including the E1 which was the
header.

Well, you said you wanted everything else in column E to be "INTL",
and you didn't mention that E1 was a header. Try the following.

Sub AAA()
Dim RR As Range
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.EnableEvents = False
With ActiveSheet
Set RR = .Cells(2, 5).Resize(.UsedRange.Rows.Count)
RR.Value = "INTL"
End With
Range("E2").Value = "DOM"
Application.EnableEvents = True
Case Else
End Select
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 22 Feb 2010 11:48:26 -0800, "dizzl3e"
wrote:

Thanks, but that didn't work. It labeled E2 as "DOM" which is right, but
the rest on column E was labeled "INT" including the E1 which was the
header.

"Chip Pearson" wrote in message
.. .
The code would look like the following:

Sub AAA()
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.Intersect(ActiveSheet.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Case Else
End Select
End Sub

If you want it to run automatically when a cell is changed, use the
Change event instead. In the worksheet's code module, use

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Left(Range("D2").Text, 2))
Case "00", "11", "CA"
Application.EnableEvents = False
Application.Intersect(Me.UsedRange, _
Range("E2").EntireColumn).Value = "INTL"
Range("E2").Value = "DOM"
Application.EnableEvents = True
Case Else
End Select
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 22 Feb 2010 11:15:07 -0800, "dizzl3e"
wrote:

I am trying to automate a process on a spreadsheet. Where Cell D2 begins
with "00" or "11" or "CA" then Cell E2="DOM". Once its run its process
then
I want it to label everything else on column E "INTL". Any help is
greatly
appreciated. thanks.