Creating an excel Database from input on Row one

%3CLINGO-SUB%20id%3D%22lingo-sub-1959708%22%20slang%3D%22en-US%22%3ECreating%20an%20excel%20Database%20from%20input%20on%20Row%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959708%22%20slang%3D%22en-US%22%3E%3CP%3EI%20an%20trying%20to%20create%20a%20database%20that%20has%208%20columns%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20complicated%20part%20is%2C%20I%20want%20to%20enter%20the%20data%20on%20line%201%20ONLY%20(%20the%20first%205%20columns%20of%20line%201)%20%2C%20and%20have%20it%20add%20to%20an%20ongoing%20database.%20So%20every%20time%20I%20hit%20%22Enter%22%20for%20example%20%2C%20it%20would%20add%20that%20new%20entry%20to%20the%20end%20of%20my%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20can%20figure%20out%20that%2C%20then%20I%20want%20to%20be%20able%20to%20only%20put%20data%20in%20cell%20A1%20and%20A2%2C%20(the%20following%204%20columns%20of%20data%20will%20be%20a%20Vlookup%20of%20cell%20A2)%26nbsp%3B%20and%20those%20will%20be%20added%20to%20the%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20file%2C%20Line%2014%20is%20the%20info%20I%20want%20for%20my%20Database%20records%2C%20the%20only%20input%20I%20want%20to%20put%20in%20is%20the%20yellow%202%20cells.%20After%20entering%20the%202%20cells%20I%20want%20all%20the%20info%20on%20line%2014%20added%20to%20the%20end%20of%20my%20dbase.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20it%20can%20be%20done%2C%20my%20son%20was%20in%20High%20performance%20waterpolo%20and%20the%20spreadsheet%20waterpolo%20canada%20used%20did%20this%20exact%20thing%2C%20except%20I%20think%20they%20just%20used%201%20cell%20to%20do%20the%20input%2C%20for%20example%202525D1%2C%20where%202525%20would%20be%20input%201%20and%20D1%20would%20be%20the%20other%20input.%20they%20separated%20it%20somehow%2C%20I%20think%20with%20macro.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I%20did%20a%20good%20job%20explaining%20it.%20If%20anyone%20can%20send%20Ideas%20my%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks.%26nbsp%3B%20%26nbsp%3BI%20attached%20the%20file%20incase%20it%20was%20confusing.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1959708%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1960067%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20excel%20Database%20from%20input%20on%20Row%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1960067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F892299%22%20target%3D%22_blank%22%3E%40dlav2001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version%20(now%20a%20.xlsm%20workbook%20since%20it%20contains%20VBA%20code).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1965577%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20excel%20Database%20from%20input%20on%20Row%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1965577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthat%20is%20Excellent%2C%20That%20is%2099%25%20of%20what%20I%20wanted.%20I%20was%20hoping%20to%20do%20it%20without%20the%20Add%20record%20button%20but%20that%20is%20not%20the%20end%20of%20the%20world.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20exactly%20as%20designed%20for%20a%20Computer%2C%20however%20VBA%20will%20not%20run%20on%20my%20Excel%20on%20my%20Cell%20phone.%20which%20is%20what%20I%20had%20originally%20intended.%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20this%20is%20perfect.%20If%20only%20I%20can%20figure%20out%20how%20you%20did%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1966695%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20excel%20Database%20from%20input%20on%20Row%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1966695%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F892299%22%20target%3D%22_blank%22%3E%40dlav2001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20it's%20possible%20to%20do%20what%20you%20want%20without%20VBA%20code%2C%20so%20it%20will%20only%20work%20on%20Windows%20and%20MacOS%2C%20not%20on%20Android%20or%20iOS.%3C%2FP%3E%0A%3CP%3EThe%20command%20button%20runs%20the%20following%20macro%20when%20clicked%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20AddRecord()%0A%20%20%20%20Dim%20d%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20If%20Range(%22B14%22).Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Range(%22B14%22).Select%0A%20%20%20%20%20%20%20%20MsgBox%20%22Please%20enter%20the%20bus%20number!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20m%20%3D%20Range(%22C13%22).End(xlUp).Row%20-%202%0A%20%20%20%20If%20m%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20Range(%22C3%22).Select%0A%20%20%20%20%20%20%20%20MsgBox%20%22Please%20enter%20information%20in%20rows%203%20and%20below!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20d%20%3D%20Val(Range(%22C14%22).Value)%0A%20%20%20%20If%20d%20%26lt%3B%201%20Or%20d%20%26gt%3B%20m%20Then%0A%20%20%20%20%20%20%20%20Range(%22C14%22).Select%0A%20%20%20%20%20%20%20%20MsgBox%20%22Please%20enter%20a%20valid%20day!%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20r%20%3D%20Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%20%2B%201%0A%20%20%20%20Range(%22A%22%20%26amp%3B%20r).Resize(1%2C%2010).Value%20%3D%20Range(%22A14%22).Resize(1%2C%2010).Value%0A%20%20%20%20Range(%22B14%3AC14%22).ClearContents%0A%20%20%20%20Range(%22B14%22).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20can%20view%20the%20code%20by%20activating%20the%20Visual%20Basic%20Editor%20(press%20Alt%2BF11)%20and%20opening%20Module1%20under%20Modules.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I an trying to create a database that has 8 columns of data.

 

The complicated part is, I want to enter the data on line 1 ONLY ( the first 5 columns of line 1) , and have it add to an ongoing database. So every time I hit "Enter" for example , it would add that new entry to the end of my database.

 

If I can figure out that, then I want to be able to only put data in cell A1 and A2, (the following 4 columns of data will be a Vlookup of cell A2)  and those will be added to the database. 

 

I attached the file, Line 14 is the info I want for my Database records, the only input I want to put in is the yellow 2 cells. After entering the 2 cells I want all the info on line 14 added to the end of my dbase. 

 

I know it can be done, my son was in High performance waterpolo and the spreadsheet waterpolo canada used did this exact thing, except I think they just used 1 cell to do the input, for example 2525D1, where 2525 would be input 1 and D1 would be the other input. they separated it somehow, I think with macro. 

 

I hope I did a good job explaining it. If anyone can send Ideas my way.

 

thanks.   I attached the file incase it was confusing. 

4 Replies

@dlav2001 

See the attached version (now a .xlsm workbook since it contains VBA code).

@Hans Vogelaar that is Excellent, That is 99% of what I wanted. I was hoping to do it without the Add record button but that is not the end of the world.  

 

It works exactly as designed for a Computer, however VBA will not run on my Excel on my Cell phone. which is what I had originally intended.    

 

but this is perfect. If only I can figure out how you did it. 

@dlav2001 

I don't think it's possible to do what you want without VBA code, so it will only work on Windows and MacOS, not on Android or iOS.

The command button runs the following macro when clicked:

 

Sub AddRecord()
    Dim d As Long
    Dim m As Long
    Dim r As Long
    If Range("B14").Value = "" Then
        Range("B14").Select
        MsgBox "Please enter the bus number!", vbExclamation
        Exit Sub
    End If
    m = Range("C13").End(xlUp).Row - 2
    If m = 0 Then
        Range("C3").Select
        MsgBox "Please enter information in rows 3 and below!", vbExclamation
        Exit Sub
    End If
    d = Val(Range("C14").Value)
    If d < 1 Or d > m Then
        Range("C14").Select
        MsgBox "Please enter a valid day!", vbExclamation
        Exit Sub
    End If
    r = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Range("A" & r).Resize(1, 10).Value = Range("A14").Resize(1, 10).Value
    Range("B14:C14").ClearContents
    Range("B14").Select
End Sub

You can view the code by activating the Visual Basic Editor (press Alt+F11) and opening Module1 under Modules.

@Hans Vogelaar 

 

 

Thank you again for your help.