Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
i have been trying some samples with this:
http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
You have had a number of postings regarding this routine: it might be easier
if you send a copy of your w/book with explanation: toppers <atNOSPAMjohntopley.fsnet.co.uk remove NOSAM "kyoshirou" wrote: i have been trying some samples with this: http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
what your email?
sry for the mass spam which is something related. "Toppers" wrote: You have had a number of postings regarding this routine: it might be easier if you send a copy of your w/book with explanation: toppers <atNOSPAMjohntopley.fsnet.co.uk remove NOSAM "kyoshirou" wrote: i have been trying some samples with this: http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
Thanks!
how do i add so that user should enter at least 1 cell? btw is http://www.contextures.com/excelfiles.html gd enough for me to go through every single demo files? "Toppers" wrote: Remove NOSPAM from above "kyoshirou" wrote: what your email? sry for the mass spam which is something related. "Toppers" wrote: You have had a number of postings regarding this routine: it might be easier if you send a copy of your w/book with explanation: toppers <atNOSPAMjohntopley.fsnet.co.uk remove NOSAM "kyoshirou" wrote: i have been trying some samples with this: http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
1) how do i add so that user should enter at least 1 cell?
2) u know i have a search engine, is it possible to search more than 1 data? (example, we can have 3 same values for the 30 cells but different ABCD values.) "kyoshirou" wrote: Thanks! how do i add so that user should enter at least 1 cell? btw is http://www.contextures.com/excelfiles.html gd enough for me to go through every single demo files? "Toppers" wrote: Remove NOSPAM from above "kyoshirou" wrote: what your email? sry for the mass spam which is something related. "Toppers" wrote: You have had a number of postings regarding this routine: it might be easier if you send a copy of your w/book with explanation: toppers <atNOSPAMjohntopley.fsnet.co.uk remove NOSAM "kyoshirou" wrote: i have been trying some samples with this: http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
helpSample
1) how do i add so that user should enter at least 1 cell?
2) u know i have a search engine, is it possible to search more than 1 data? (example, we can have 3 same values for the 30 cells but different ABCD values.) 3) Is it possible to prevent adding number of values inside the 30cells. Example (The CONCATENATE (AI) should not be the same). "kyoshirou" wrote: 1) how do i add so that user should enter at least 1 cell? 2) u know i have a search engine, is it possible to search more than 1 data? (example, we can have 3 same values for the 30 cells but different ABCD values.) "kyoshirou" wrote: Thanks! how do i add so that user should enter at least 1 cell? btw is http://www.contextures.com/excelfiles.html gd enough for me to go through every single demo files? "Toppers" wrote: Remove NOSPAM from above "kyoshirou" wrote: what your email? sry for the mass spam which is something related. "Toppers" wrote: You have had a number of postings regarding this routine: it might be easier if you send a copy of your w/book with explanation: toppers <atNOSPAMjohntopley.fsnet.co.uk remove NOSAM "kyoshirou" wrote: i have been trying some samples with this: http://www.contextures.com/xlUserForm01.html and modified to this: Option Explicit 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 = "C3,C4,C5,D3,D4,D5,E3,E4,E5" Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") 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, "AM") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "AN").Value = Application.UserName oCol = 5 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 I have some enquires: 1) can i modified so that users do not need to enter all cells. Those cells without values will be save default as 0 value. 2) As my new enterly values will be save into col 5 (E), is it possible to configure there will be auto-count no from col 1 to 4 (A to D). Like: 0 0 0 1, 0 0 0 2, 0 0 0 3... go on... to 0 0 1 0, 0 0 1 1, 0 0 1 2 to 9 9 9 9? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|