Thanks very much for your help, if I had additional lines I assume I would just include the corresponding number of If statements?
Thanks very much for your help, if I had additional lines I assume I would just include the corresponding number of If statements?
![]()
Sub MoveRecord() with sheets("SalesData").Cells(Rows.Count, 1).End(xlUp) for j=1 to 4 if cells(j+20,3).value<>"" then .offset(j).Resize(1, 10) = Array([F6], [F6], [F5], [A18], [b18], [c10], cells(j+20,3), cells(j+20,4), cells(j+20,6), cels(j+20,2)) next end with End Sub
Last edited by snb; 03-18-2012 at 03:40 PM.
I ended up with the code below, which seems to work okay. Thanks for the help. Now to the details.
1. The database is a table, how do I maintain the table as a table, if I post records to the bottom of the table with the shown code, it has some manually added records, those records no longer take on the properties of the table? Is there a way to force the table to remain a table after I post a record to it?
2. Secondly, how would I skip cells in the record, there are cells in the table that have vlookups and formulas in them that I would like to apply to the posted record. So I would like to post nothing in a column so I can use the table's formulas, then post the next form cell in the next database column.
3. How would I tell Excel which column to start entering the data from the form? Right now I'm sending a cell that is blank that goes into the first column. The first column is blank because I'm using Freeze First Column. The actual new record data starts at column 2.
4. Resize(1,13) Does the the 1 here determine how many copies to make of data?
![]()
Sub MoveRecord() Dim Increment As Integer 'looping variable Dim WSF As Worksheet ' Invoice worksheet Dim WSD As Worksheet ' SalesData worksheet Set WSF = Worksheets("Invoice") Set WSD = Worksheets("SalesData") NextRow = WSD.Cells(Rows.Count, 2).End(xlUp).Row + 1 For Increment = 19 To 34 ' Test rows 19 to 34 of Invoice, if order entry line has something in it. If WSF.Cells(Increment, 3) <> "" Then WSD.Cells(NextRow, 1).Resize(1, 14).Value = Array([D4], [F4], [F4], [F3], [A16], [B16], [c9], Cells(Increment, 3), Cells(Increment, 4), Cells(Increment, 11), Cells(Increment, 2), Cells(Increment, 12), Cells(Increment, 13), Cells(Increment, 14)) NextRow = WSD.Cells(Rows.Count, 2).End(xlUp).Row + 1 End If Next Increment
Last edited by laguna92651; 03-21-2012 at 01:53 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks