Help adding quantity to a Data Entry Excel/VBA

[DMA123]

Hey all,

First thanks in advance for all the help

SHORT VERESION:
I've created (from contextures "partsdata" http://www.contextures.com/xlForm02.html) my own form which has a data entry sheet which I need to add a quantity to (EXCEL LOCATED THERE: http://forum.chandoo.org/attachments/updatelog-xlsm.6061/) I need the quantity to correspond in the appropriate column, so if I enter Record 2 for type and 5 for quantity, a "5" will be placed int he "record 2" column.


Long Version of question:
Name
Subname
Type (3 choices from drop-down)
Quantity (1-100)

The name and subname are fine, it's when I put in the Type and Quantity it gets screwy.

I took out the quantity originally because I thought it would not be needed, but now, I need it to place the quantity of the specific Type in the corresponding column. With the current code, it is only copying the Quantity to the first Type column in the partsdata page. I'd like it to copy the Type + Quantity to the appropriate Type column (e.g. if I put in Type 2/40, I want it to put in 40 in the type 2 column)

I'll post the VBA code i have below where C7 = Quantity and C6 = Type of record

Sub UpdateLogWorksheet()
'http://www.contextures.com/xlForm02.html
'code by Dave Peterson and modified by cdbauer1
'Make sure that your column headers on the PartsData tab go in the order of the Data Validation list and that they are identically named
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 = "C2,C3,C4,C5,C6,C7"

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"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
If myCell.Value = "No Record" Then
historyWks.Cells(nextRow, oCol + 1).Value = 1
historyWks.Cells(nextRow, oCol + 2).Value = 0
historyWks.Cells(nextRow, oCol + 3).Value = 0
End If
If myCell.Value = "Record" Then
historyWks.Cells(nextRow, oCol + 1).Value = 0
historyWks.Cells(nextRow, oCol + 2).Value = 1
historyWks.Cells(nextRow, oCol + 3).Value = 0
End If
If myCell.Value = "Record Redacted" Then
historyWks.Cells(nextRow, oCol + 1).Value = 0
historyWks.Cells(nextRow, oCol + 2).Value = 0
historyWks.Cells(nextRow, oCol + 3).Value = 1
End If
oCol = oCol + 1
Next myCell

End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

End Sub

 

[DMA123]

bump

 

[DMA123]

bump tumbleweed

 

[Jerry Sullivan]

Hi DMA, Passing the Qty to the correct field could be done more simply and reliably by Matching the values in your headers.

I'd be glad to suggest some code, but between your example file and your description above it's unclear to me how many fields you're currently using for each data item and how those are arranged.

Here's a guess-please clarify....
Sheet: Input
C2 = Name?
C3 = Sub Name?
C4 = ?
C5 = ?
C6 = Type of record
C7 = Quantity

Sheet: PartsData:
A=Time
B=User Name
C=Name?
D=Sub Name?
E=?
F=?
G ?=No Record
H ?=Record
I ? = Record Redacted

 

[DMA123]

Thank you so much for the reply, Jerry.

So C4, C5 are just values that correspond to C2, C3. Right now I have a data validation set up so if I enter:

0031313 in C2, it will automatically place a value in C3, C4, C5. These are just benign titles/labels. I really just need the quantity and the type of record (C6) to match in the output (partsdata sheet) so if I type in values in C2, it will automatically place a value in C3,C4,C5 on the input sheet, and then I choose the type of record (c6) and place a # in quantity (c7). Currently it will work but only for the first type, no record. If i type in a quantity and choose "record" it places the quantity in the "No record" column and a "1" as it's coded, in the "record" column.

I hope this helps please let me know if more is needed.

 

[Jerry Sullivan]

That clarifies the Input Sheet. I'm not sure which, if any, of those SubName/Title/Label fields you are logging. The code I've suggested below assumes this layout. You can modify as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	Time	User Name	Name	Sub Name	No Record	Record	Record Redacted
2	05/07/2014	JDoe	0031313	mySubName		5
3	05/07/2014	MSmith	0041314	mySubName2			3
Sheet

I hope you don't mind that I've significantly reworked the code. The example you had worked well for the scenario that each input cell will be written to the log sheet in the order listed. Since your scenario is more complex, the approach below seems a better fit.

It employs a technique of having your VBA code reference defined names in your worksheets. A benefit of this is that the layout of the sheet can be modified to some degree without the need to change cell addresses that have been written in your code.

To set up, start by running this one-time macro to add the defined names.

Sub CreateDefinedNames()
'--use this to create or modify the locations of
'  the named ranges that are referenced by
'  the AddRecordToLog sub.

'--these names have worksheet scope
 With Worksheets("Input").Names
   .Add Name:="inpName", RefersTo:="=Input!$C$2"
   .Add Name:="inpSubName", RefersTo:="=Input!$C$3"
   .Add Name:="inpRecordType", RefersTo:="=Input!$C$6"
   .Add Name:="inpQty", RefersTo:="=Input!$C$7"
 End With

 With Worksheets("PartsData").Names
   .Add Name:="hdrTime", RefersTo:="=PartsData!$A$1"
   .Add Name:="hdrUserName", RefersTo:="=PartsData!$B$1"
   .Add Name:="hdrName", RefersTo:="=PartsData!$C$1"
   .Add Name:="hdrSubName", RefersTo:="=PartsData!$D$1"
 End With
 
'--this name has workbook scope to allow it use in
'  a data validation list.
 With ActiveWorkbook.Names
   .Add Name:="hdrRecordTypes", RefersTo:="=PartsData!$E$1:$G$1"
 End With

End Sub

Next, modify your Data Validation list to reference: =hdrRecordTypes

Finally, paste this macro into a standard code module and assign it to the button on your Input sheet.

Sub AddRecordToLog()
'--This sub validates a record entered in a range of input cells
'     then adds the record to the next line in the log worksheet.

'  The procedure uses defined names to identify input cells and
'     the field headers of the log sheet to allow some changes
'     in layout without the need to modify this code.

 Dim lNextRow As Long
 Dim rInput As Range
 Dim sRecordType As String
 Dim vTypeIndex As Variant
 Dim wksHistory As Worksheet, wksInput As Worksheet
 
 Set wksInput = Sheets("Input")
 Set wksHistory = Sheets("PartsData")

 '--validate input-in addition to data validation
 '  applied to input cells
 With wksInput
 '--input range-defined by first and last input cells
   Set rInput = .Range(.Range("inpName"), .Range("inpQty"))
   
   If Application.CountA(rInput) <> rInput.Count Then
      MsgBox "Please fill in all the cells!"
      GoTo ExitProc
   End If
   
   '--validate record type
   sRecordType = .Range("inpRecordType").Value
   
   '--find the record type in output header
   vTypeIndex = Application.Match(sRecordType, _
      ActiveWorkbook.Names("myList").RefersToRange, 0)
   'hdrRecordTypes
   If Not (IsNumeric(vTypeIndex)) Then
      MsgBox "Record Type not found."
      GoTo ExitProc
   End If
 End With
 
 With wksHistory
 End With

 With wksHistory
   lNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
   
   '--add time stamp
   With .Cells(lNextRow, .Range("hdrTime").Column)
      .Value = Now
      .NumberFormat = "mm/dd/yyyy"
   End With
   
   With .Cells(lNextRow, .Range("hdrUserName").Column)
      .Value = Application.UserName
   End With

   '--transfer input values to log
   With .Cells(lNextRow, .Range("hdrName").Column)
      .Value = wksInput.Range("inpName").Value
   End With

   With .Cells(lNextRow, .Range("hdrSubName").Column)
      .Value = wksInput.Range("inpSubName").Value
   End With
          
   '--use index from validation step to place qty in
   '  corresponding recordType field
   With .Cells(lNextRow, .Range("hdrRecordTypes") _
      (1, CLng(vTypeIndex)).Column)
      .Value = wksInput.Range("inpQty")
   End With
 End With
 
 '--clear input cells that contain constants
 On Error Resume Next
 With rInput.Cells.SpecialCells(xlCellTypeConstants)
   .ClearContents
   Application.Goto .Cells(1)
 End With
 On Error GoTo 0

ExitProc:
   '---place any clean up steps here
End Sub