If not found, create new column

[kwp004]

I've written a macro that has an InputBox appear, asks the user to enter a client name and company name, searches row 1 for the company name, and if a match is found, adds the client name into the first empty cell beneath a list of client names for that company. (At present, there are a few companies listed, with at least 1 client name under each company).

The problem occurs if a user is adding a client from a new company. If the company name is not found in row 1, I'd like to create a new column with the company name in row 1, and then have the client name appear under it. Any suggestions about what type of macro I need?

Sub CreateNew()


Dim myValue As Variant
myValue = InputBox("Enter client name:")


Dim myValue1 As Variant
myValue1 = InputBox("Enter client company:")


Range("A12").Value = myValue
Range("A13").Value = myValue1


'Select cells in row 1
Rows("1:1").Select


'Find the company name (directs you to the appropriate column)


Selection.Find(What:=Range("A13").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'Select the active cell
ActiveCell.Select

'Move to bottom cell in column with text
Selection.End(xlDown).Select

'Move down 1 cell
Selection.Offset(1, 0).Select


'Select the active cell
ActiveCell.Select


'Make cell value equal to A12
ActiveCell.FormulaR1C1 = Range("A12").Value

'Copy and paste values only
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

 

[tonyyy]

You might consider the following...

Sub CreateNew()
Dim myValue As String, myValue1 As String
Dim found As Range
Dim LastColumn As Long

myValue = InputBox("Enter client name:")
myValue1 = InputBox("Enter client company:")
Range("A12").Value = myValue
Range("A13").Value = myValue1
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
On Error GoTo errHandler
Set found = Rows(1).Find(What:=Range("A13").Value, After:=Cells(1, LastColumn), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
Cells(Cells(Rows.Count, found.Column).End(xlUp).Row + 1, found.Column) = Range("A12")

errHandler:
    If Err.Number = 91 Then
        Cells(1, LastColumn + 1) = Range("A13")
        Cells(2, LastColumn + 1) = Range("A12")
    ElseIf Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
End Sub

Essentially, if the macro finds the client company in Row 1, it adds the client name to that column. If it doesn't find the client company, an error is generated (Error 91) and the new client company is added via an error routine.

Cheers,

tonyyy

 

[kwp004]

That worked! Thanks -- you're my hero!!!

 

[tonyyy]

LOL! You're welcome. Glad it worked out...