Thread: If statement

I am trying to use a group of data that is names and account numbers, IF the name is = to A-D, E-J, K-T, U-Z copy the data to appropriate case manager,(example, Dave Handles names a-d, Bob handles E-J etc,) The intake can work any name. I just need to align the name with the case manager at the same time. Can it be done?

Hi

What do you want to do if there is more than 1 entry for a particular handler?

rylo

Hello Liamsdad,

The macro below has already been added to the attached workbook. It will fill in the case mangers table with the intake name using the name matching in your example. It will also alert you when a case manager's column is full.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim C As Long
  Dim CaseMgr As Range
  Dim Intakes As Range
  Dim LastRow As Long
  Dim R As Long
  Dim RowFull As Boolean
    
    RowFull = True
    Set Intakes = Range("B2:E9")
    Set CaseMgr = Range("G2:J9")
    
    If Not Intersect(Target, Intakes) Is Nothing Then
      Char = UCase(Left(Target, 1))
        If Char Like "[A-D]" Then C = 1
        If Char Like "[E-J]" Then C = 2
        If Char Like "[K-T]" Then C = 3
        If Char Like "[U-Z]" Then C = 4
    Else
      Exit Sub
    End If
    
  Application.EnableEvents = False
  
    LastRow = CaseMgr.Rows.Count
      For R = 1 To LastRow
        If CaseMgr.Cells(R, C) = "" Then
          RowFull = False
            CaseMgr.Cells(R, C) = Target.Text
          Application.EnableEvents = True
          Exit Sub
        End If
      Next R
    If RowFull = True Then
      MsgBox CaseMgr.Cells(1, C).Offset(-1, 0) & " column is full.", vbExclamation
    End If
    
  Application.EnableEvents = True
    
End Sub

Sincerely,
Leith Ross

I see where your going with this but for some reason its not doing anything? I admit I am not a macro / vb guy so I do not know what I am doing wrong? Any help would be welcome. Thanks

Disregard my last post. The part between the chair and the keyboard was at error. The one thing with the macro isthe times are not aligning If I schedule Zohan at 9:00 with any intake it moves it to the open 8:00 spot. What can I do to correct that?