Excel 2019 running adding data records (I'm not a programmer, but this should be easy enough if the DATA ENTRY FORM function from older excel version was not taken out) I've created a data input sheet to update a running database (on another sheet) created a macro sub that add's the initial record When I need to add the next record, it replaces the previous record and adds duplicate record.
I'm able to create the 1st record with success. adding the next distinct record is where I am failing.
Code below is revised from research:VBA Entering userform data at next blank row correctly
My macro as follows:
Sub UpdateComplaintsTest()
' UpdateComplaintTest Macro
Set ws = Sheets("ACH Complaints 2019")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("A" & LastRow).Value = "=ACHComplaintsForm!B3" 'Inserts the Date Col A
ws.Range("A" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B4" 'Inserts Time Col B
ws.Range("B" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B5" 'Inserts Name of Complainant Col C
ws.Range("C" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B6" 'Sender's Contact No Col D
ws.Range("D" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B7" 'Sender's Email Col E
ws.Range("E" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B8" 'Date of Transaction Col F
ws.Range("F" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B9" 'Time of Transaction Col G
ws.Range("G" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B10" 'Transaction Ref No Col H
ws.Range("H" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B11" 'Mode of Tran / Online/Mobile Col I
ws.Range("I" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B12" 'Name of Clearing House Col J
ws.Range("J" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B13" 'Sending Bank Col K
ws.Range("K" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B14" 'Receiving Bank Col L
ws.Range("L" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B15" 'Amount Col M
ws.Range("M" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B16" 'Receiver Name Col N
ws.Range("N" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B17" ' Receiver Contact No Col O
ws.Range("O" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B18" 'Receiver Email Col P
ws.Range("P" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B19" 'Receiver AccountNo Col Q
ws.Range("Q" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B20" 'History of Trans Col R
ws.Range("R" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B21" 'Action 1 Col S
ws.Range("S" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B22" 'Action 2 Col T
ws.Range("T" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B23" 'Action 3 Col U
End Sub
Expected Result: additional entries from the data input sheet should CREATE a new record on the next row.
Possibly simple transposition
Assuming that you add new userform data into an extra form sheet's right most wandering column and you just want to write the collected data back horizontally to a target sheet, you could use the following approach via Application.Transpose in order to interchange rows and column(s) of an intermediate formdata array.
Option Explicit ' declaration head of Code module
Sub UpdateComplaintsTest()
' [1] assign vertical data column to 2-dimensioned 1-based array formdata
Dim formdata() As Variant
formdata = getFormData("ACHComplaintsForm")
' [2] write data horizontally (i.e. transpose data column from variant array formdata)
nextTargetRange("ACH Complaints 2019", UBound(formdata), "A").Value = Application.Transpose(formdata)
End Sub
Helper function getFormData() called by section [1]
It's possible to assign an entire range to a variant array by one code line,
e.g. via formdata = Thisworkbook.Worksheets("XY").Range("B3:Z1000").Value.
As the right assignment part in section [1] gets executed by the following function calculating the most right values in the form data sheet, you are coding formdata = getFormData("ACHComplaintsForm") instead.
Furthermore the function resizes the returned data range to 1 column, i.e. the most right column in source data ACHComplaintsForm (where the sheet name is passed as a string argument and the starting row defaulting to 3 can be indicated optionally).
Function getFormData(ByVal DataSheet As String, Optional ByVal StartRow As Long = 3) As Variant()
' Purpose: return 2-dim 1-based array containing latest data column (i.e. most right column)
' Note: Function assumes data start at 3rd row
With ThisWorkbook.Worksheets(DataSheet)
'[a] define number of most right column
Dim nextCol As Long
nextCol = .Cells(StartRow, .Columns.Count).End(xlToLeft).Column
'[b] define number of items in this data column
Dim Itemscount As Long
Itemscount = .Cells(.Rows.Count, nextCol).End(xlUp).Row - StartRow + 1
'[c] return column data as variant 2-dim 1-based array
getFormData = .Cells(StartRow, nextCol).Resize(Itemscount, 1).Value
'Debug.Print "Form Data Range " & .Cells(StartRow, nextCol).Resize(Itemscount, 1).Address
End With
End Function
Helper function nextTargetRange() called by section [2]
This function simply resizes the target row range to the necessary size to receive the indicated number of source items.
Function nextTargetRange(ByVal TargetSheet As String, Itemscount As Long, Optional ByVal StartCol As String = "A") As Range
' Purpose: return next free row range to receive needed data starting at a given column
With ThisWorkbook.Worksheets(TargetSheet)
' [a] define next free row
Dim nextFreeRow As Long
nextFreeRow = .Range(StartCol & Rows.Count).End(xlUp).Row + 1
' [b] return function result, i.e. the receiving target range
Set nextTargetRange = .Range(StartCol & nextFreeRow).Resize(1, Itemscount)
'Debug.Print "Target Range " & nextTarget.Address
End With
End Function