Error message in assigning array values in Excel rows to another sheet

bpelkey

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I get an error 1004: Method 'Range' of object'_Worksheet' failed.

I have an Excel worksheet called "Open Items" and another called 'Closed Items' in an account reconciliation workbook. My code is successfully identifying consecutive-row matchups of WBS Element numbers (SAP) and amounts, debit/credit-wise. The array relates to the Open Items sheet and it stores a row's worksheet contents from columns 1 through 36. I'm attempting to assign (or copy) those array values in those 36 columns of a matched row into the Closed Items sheet below the data already in that sheet.

The macro is errorring-out on this line:
VBA Code:
wsc.Range(Cells(lrc, j)).Value = arr(i, j)

Here is the entire code:

VBA Code:
Option Base 1

Dim wbm As Workbook, wbr As Workbook, wsm As Worksheet, wsr As Worksheet, wsc As Worksheet, LastRowc As Long, lrc As Long, LastRowr As Long, Match As Boolean, ColV As Range, v As Range

Option Explicit
Public Sub Initialize()

'Note: I took the deletion of the Cut Open Items sheet row out of the code and it runs MUCH faster and with STABILITY.

'Then manually highlight the entire Open Items sheet range - including blanks and sort A to Z on WBSe and it eliminates
'the blanks.  Possibly record an entire range sort and edit that macro to have it do the sort automatically.

'Application.ScreenUpdating = False: Application.Calculation = xlManual: Application.EnableEvents = False

'Must have the correct worksheet name below:

Set wbr = Workbooks("LST_082019_25590000-25590200_MULTIPLE_ACCRUAL ROLL FORWARD TEMPLATE V1.0.xlsx")
Set wsr = wbr.Sheets("Open Items")
Set wsc = wbr.Sheets("Closed Items")

LastRowr = wsr.Cells(Rows.Count, 1).End(xlUp).Row
LastRowc = wsc.Cells(Rows.Count, 1).End(xlUp).Row

Sort_WBSe

Application.ScreenUpdating = True: Application.Calculation = xlAutomatic: Application.EnableEvents = True

End Sub

Public Sub Sort_WBSe()

    wsr.AutoFilter.Sort.SortFields.Clear
    wsr.AutoFilter.Sort.SortFields.Add _
        Key:=Range("V7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With wsr.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Process

End Sub


Public Sub Process()


Dim N As Integer, Amt, AmtNext, WBSe, WBSeNext As String, Rng As Range, i As Long, j As Long
lrc = LastRowc: j = 0

Set Rng = wsr.Range(Cells(8, 1).Address, Cells(LastRowr, 36).Address)

Dim arr As Variant
arr = Rng

For i = LBound(arr) To UBound(arr)
    If arr(i, 22) = arr(i + 1, 22) And arr(i, 19) = -arr(i + 1, 19) Then
       lrc = lrc + 1: 'lrc is the last row of the Closed Items sheet, wsc
       j = j + 1 'j is the element # in the array
       
       For j = 1 To 36
           wsc.Range(Cells(lrc, j)).Value = arr(i, j)
       Next j
    End If
Next i

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
wsc.Cells(lrc, j).Value = arr(i, j)
 

bpelkey

New Member
Joined
Dec 17, 2019
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thanks so much Fluff! It now works!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,314
Members
425,272
Latest member
Umba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top