PDA

View Full Version : How do I add values to cells at the end of a row I just inserted through my VB code



louvalterry
10-31-2013, 08:55 AM
I have code that copies and inserts a new row into my audit worksheet when the row is deleted from the original worksheet. Now I want to insert three new bits of data at the end if the just pasted row. The fileds are; Date, Time, and application.user. All the code works but I cannot find the correct code to add the three new fields, Here is my code:


Private Sub cmdDeleteRow_Click()
Dim ws As Worksheet
Dim lRow As Long
Dim LastRow As Long
Dim rn As Range
Set rn = ActiveCell.EntireRow
Dim audit As Worksheet
Set audit = Worksheets("Audit")

If Not IsInRange(ActiveCell, ActiveSheet.Range("ForecastTable")) Then
MsgBox "You can not delete lines here.", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
ActiveSheet.Unprotect
' MsgBox "You are about to copy the deleted data to the Audit tab "

rn.Copy
Worksheets("Audit").Range("A" & (Worksheets("Audit").UsedRange.Rows.Count + 1)).PasteSpecial xlPasteValuesAndNumberFormats
rn.Delete
'
' Code to add date and time stamp and user id
Worksheets("Audit").Range(0, 77)).Value = Date
Worksheets("Audit").Range(0, 78).Value = Time
Worksheets("Audit").Range(0, 79).Value = Application.UserName
' End of code add date and time stamp and user id

ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

p45cal
10-31-2013, 10:30 AM
RowToPasteTo = Worksheets("Audit").UsedRange.Rows.Count + 1
Worksheets("Audit").Range("A" & RowToPasteTo).PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Audit").Range(RowToPasteTo, 77).Value = Date
Worksheets("Audit").Range(RowToPasteTo, 78).Value = Time
Worksheets("Audit").Range(RowToPasteTo, 79).Value = Application.UserName

louvalterry
10-31-2013, 12:29 PM
p45cal,

Thanks for the code. I incoporated your code into my existing code as shown below. I ran the process in debug and got an error message "Run-time error 1004': Application-defined or object-defined error" on the third line of code. Did I do something wrong?


rn.Copy
Worksheets("Audit").Range("A" & (Worksheets("Audit").UsedRange.Rows.Count + 1)).PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 77).Value = Date
Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 78).Value = Time
Worksheets("Audit").Range(Worksheets("Audit").UsedRange.Rows.Count + 1, 79).Value = Application.UserName
rn.Delete

p45cal
10-31-2013, 04:35 PM
Yes.

Your quoted code in the last message bears no resemblance to mine. Stick to using a variable.

louvalterry
11-01-2013, 05:40 AM
p45cal,

Sorry,I get your point, so I cut and pasted your code between my code to copy and then delete in the original code. I add a Dim statement because when I first compiled the code I got a message that "RowToPasteTo" was not defined. It then compiled clean. In Debug I steped through the code and I get the same "Run-time error 1004'..." message on the same line where the vaiable is first used.
Not sure it makes a difference, my version of VBA is 7.0



Dim RowToPasteTo As String

rn.Copy ' my original code
RowToPasteTo = Worksheets("Audit").UsedRange.Rows.Count + 1
Worksheets("Audit").Range("A" & RowToPasteTo).PasteSpecial xlPasteValuesAndNumberFormats
Worksheets("Audit").Range(RowToPasteTo, 77).Value = Date
Worksheets("Audit").Range(RowToPasteTo, 78).Value = Time
Worksheets("Audit").Range(RowToPasteTo, 79).Value = Application.UserName
rn.Delete
' my original code

Kenneth Hobs
11-01-2013, 06:08 AM
Dim variables with a row number as Long. Columns can be integers. While you can use CStr() to convert a number to a string, it is generally not needed when using the string concatenation operator, &, with a variable dimmed as a number.

When using "row, column" syntax, use Cells() rather than Range().
e.g.

Msgbox Cells(1, 1).Value
MsgBox Cells(1, "A").Value
MsgBox Range("A1").Value

p45cal
11-01-2013, 06:33 AM
Dim RowToPasteTo As Long
or just:
Dim RowToPasteTo

louvalterry
11-01-2013, 09:03 AM
P45cal and Kenneth Hobbs

Thanks to both of you for the help you provided. My code now copies successfully from Worksheet "ForeastTable" to "Audit" and then adds a date and Time stamp and the Application Users name who deleted the row. The issue with the code did seem to be that VBA did not like the Range object and once I changed that to Cells it worked fine. All that is left is to clean up the code to remove the msgboxes I was using to debug the code.

Again, the help I received being a novice VBA coder has been a great learning experience and if were not for this form I would be working twice as much and not getting as much done.