For If Statement with Two Ranges

[aliensrreal]

Hello!

I have recently begun to teach myself VBA to improve reconciliation spreadsheets for an Accounting department. There is one particular issue I can't seem to solve and it's driving me insane. I'd like to do the following:


Search through workbook "yesterday" in specific ranges, "searchrange" and identify any transactions with an x in column K. Copy column A:J in that row and copy them to "Today's Recon"

Copy them to Section2a if negative (value is in column "E")
Copy them to Section 2b if positive
Copy them in the first available row in the range. The currently defined ranges do not include any title rows, so it could begin at row 1 of the range.



I can't get this code to work and it's driving my crazy.


Sub PullChargebacks()
Dim Yesterday As Worksheet
Dim Today As Worksheet
Dim Section2a As Range
Dim Section2b As Range
Dim SearchRange As Range
Dim Last2aRow As Long
Dim last2bRow As Long
Dim Cell As Object
Set Yesterday = Sheets("Yesterday's Recon")
Set Today = Sheets("Today's Recon")
Set Section2a = Today.Range("A500:K514")
Set Section2b = Today.Range("A520:K594")
Set SearchRange = Yesterday.Range("K104:K453,K460:K489,K603:K952,K1322:K1571, _
1573:K1587,K1594:K1623,K1630:K1659,K1677:K1696")
For Each Cell In SearchRange
If Cell.Value = "x" And Cell.Offset(0, -6) < 0 Then
Last2aRow = Section2a.End(xlUp).Row + 1
SearchRange.Range(Cell.Offset(0, -1), Cell.Offset(0, -11)).Copy Section2a.Range("A" & Last2aRow)
Last2aRow = Last2aRow + 1
Else
If Cell.Value = "x" And Cell.Offset(0, -6) > 0 Then
last2bRow = Section2b.End(xlUp).Row + 1
SearchRange.Range(Cell.Offset(0, -1), Cell.Offset(0, -11)).Copy Section2b.Range("A" & last2bRow)
last2bRow = last2bRow + 1
End If
End If
Next Cell
End Sub

[SamT]

I would use
Dim Start2A as Range 'Repeat as indicated below

Set Start2A = Range("A500")
Set Start2B= Range("A520")
Set End2B = Range("A539")


When you get ready tp paste the data to Section A
IF Start2A = "" Then 'Paste to Start2A
Else
'Paste to Start2B.End(xlUp).Offset(1)

When you paste to section b
IF Start2b = "" Then 'Paste to Start2b
Else
'End2B.End(xlUp).Offset(1)

Notice the difference in process between this and your original. In this, we know the the starts and ends of each section and use those cells to find the desired data location.

[aliensrreal]

Wow!! Thank you SO much! It worked and I couldn't be more grateful. :)