Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
if i entered some values at some columns, these values are not inside the
database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
Maybe you want something like this:
http://contextures.com/xlForm02.html From Debra Dalgleish's site. kyoshirou wrote: if i entered some values at some columns, these values are not inside the database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
Thanks for the link.
i have no idea how to use marco. Do i copy: Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D5,D7,D9,D11,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PartsData") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub when i hit Alt+F11? "Dave Peterson" wrote: Maybe you want something like this: http://contextures.com/xlForm02.html From Debra Dalgleish's site. kyoshirou wrote: if i entered some values at some columns, these values are not inside the database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm kyoshirou wrote: Thanks for the link. i have no idea how to use marco. Do i copy: Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D5,D7,D9,D11,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PartsData") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub when i hit Alt+F11? "Dave Peterson" wrote: Maybe you want something like this: http://contextures.com/xlForm02.html From Debra Dalgleish's site. kyoshirou wrote: if i entered some values at some columns, these values are not inside the database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
let say i got these:
the ? sign reprsents empty cell. M1 M2 M3 Low K ? ? ? Lower k ? ? ? Lowest k ? ? ? If user enter M1 M2 M3 Low K 1 1 ? Lower k ? ? 2 Lowest k ? ? 5 it will capture inside another worksheet called "Data" The Data sheet is sort by M1_Lowk, M1_Lowerk, M1_Lowestk, M2_Lowk, M2_Lowestk, M2_Lowestk, M3_Lowk, M3_Lowerk, M3_Lowestk The user can enter any value within that 9 empty cells. Be it fill out all or fill 1. Is it possible? "Dave Peterson" wrote: You may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm kyoshirou wrote: Thanks for the link. i have no idea how to use marco. Do i copy: Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D5,D7,D9,D11,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PartsData") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub when i hit Alt+F11? "Dave Peterson" wrote: Maybe you want something like this: http://contextures.com/xlForm02.html From Debra Dalgleish's site. kyoshirou wrote: if i entered some values at some columns, these values are not inside the database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoCreateData
let say i got these:
the ? sign reprsents empty cell. M1 M2 M3 Low K ? ? ? Lower k ? ? ? If user enters: M1 M2 M3 Low K 1 1 ? Lower k ? ? 2 it will capture inside another worksheet called "Data" The Data sheet is sort by Above enters will print: M1_Lowk, M1_Lowerk, M2_Lowk,M2_Lowestk, M3_Lowk, M3_Lowerk, 1 1 3 The user can enter any value within that 6 empty cells. Be it fill out all or fill 1. The capture will need a solid forumula since is using 2-D array. Is it possible? "kyoshirou" wrote: let say i got these: the ? sign reprsents empty cell. M1 M2 M3 Low K ? ? ? Lower k ? ? ? Lowest k ? ? ? If user enter M1 M2 M3 Low K 1 1 ? Lower k ? ? 2 Lowest k ? ? 5 it will capture inside another worksheet called "Data" The Data sheet is sort by M1_Lowk, M1_Lowerk, M1_Lowestk, M2_Lowk, M2_Lowestk, M2_Lowestk, M3_Lowk, M3_Lowerk, M3_Lowestk The user can enter any value within that 9 empty cells. Be it fill out all or fill 1. Is it possible? "Dave Peterson" wrote: You may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm kyoshirou wrote: Thanks for the link. i have no idea how to use marco. Do i copy: Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D5,D7,D9,D11,D13" Set inputWks = Worksheets("Input") Set historyWks = Worksheets("PartsData") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub when i hit Alt+F11? "Dave Peterson" wrote: Maybe you want something like this: http://contextures.com/xlForm02.html From Debra Dalgleish's site. kyoshirou wrote: if i entered some values at some columns, these values are not inside the database, is it possible to so-called auto-create into the database? is macro required? A B C D 1 1 1 1 If these values are not found in sheet 2(i rename as "data"), it will auto capture and save inside sheet data. Maybe there's a button called "Capture" ? Regards, kyo -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|