Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment array range
Hi,
I just can't get this to work. What I've did is combine code from here, there and everywhere. What I want to do is to create an array on 1 worksheet and then paste the results incrementally on another whilst skipping the blanks. here is my code: Sub tester1() Dim rngArr As Variant Dim account As String Dim company As String Dim debit As Currency Dim credit As Currency Dim costcent As String Dim jrntx As String Dim tax As String On Error Resume Next Dim cell As Range Sheets("journal").Activate lastRow = Cells(Rows.Count, "A").End(xlUp).Row Set Checkrange = Range("A9:A" & lastRow) For Each cell In Checkrange If cell < blank Then account = cell.Offset(0, 1) company = cell debit = cell.Offset(0, 5) credit = cell.Offset(0, 6) costcent = cell.Offset(0, 2) jrntx = cell.Offset(0, 8) tax = cell.Offset(0, 10) rngArr = Array(account, company, debit, credit, Null, Null, Null, costcent, Null, Null, Null, Null, jrntx, tax) If Sheets("Data Entry Journal").Range("A44") = "" Then Set Target = Sheets("Data Entry Journal").Range("A44") ElseIf Sheets("Data Entry Journal").Range("A44") < "" And Sheets("Data Entry Journal").Range("A45") = "" Then Set Target = Sheets("Data Entry Journal").Range("A45") ElseIf Sheets("Data Entry Journal").Range("A44") < "" And Sheets("Data Entry Journal").Range("A45") < "" Then Set Target = Sheets("Data Entry Journal").Range("A44").End(xlDown).Offset(1, 0) End If rngArr.Copy Destination:=Target Application.CutCopyMode = False End If Next End Sub I think where it falls over is the line rngArr.Copy Destination:=Target Many thanks in advance, Marc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to convert range of time into 1 hour increment | Excel Discussion (Misc queries) | |||
How to fix an array or range | Excel Discussion (Misc queries) | |||
Auto increment chart range montly | Charts and Charting in Excel | |||
Increment cells only in filtered range? | Excel Programming | |||
Range as array | Excel Programming |