If not found, create new column

kwp004

Board Regular
Joined
Dec 27, 2016
Messages
91
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
You might consider the following...

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top