If cell D2 begins with 00 then E2 =

  • Thread starter Thread starter dizzl3e
  • Start date Start date
D

dizzl3e

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
 
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
 
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.
 
the rest on column E was labeled "INT" including the E1 which was the
header.
Click to expand...

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
 
Back
Top