aliensrreal
12-07-2016, 10:01 AM
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
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