Phelony
11-11-2011, 06:11 AM
Hi guys
I've written the below and am a little stumped as to why it's not working.
The code is supposed to scan column B on the Invoices sheet, when it comes across an entry reading "payment", it marks the cell with as "pmark" it should then copy that line to the next available line in the CashC sheet and then repeat the process for the line above "pmark".
Unfortunately, all it's doing is cycling through the list in column B on Invoices as if the rest of the code weren't even there!
Ideas? Suggestions?
:dunno
Option Explicit
Sub findinvoice()
Dim pmark As Range
'set start point
Sheets("invoices").Range("B1").Activate
Do Until ActiveCell = ""
If ActiveCell = "payment" Then
'mark activecell as variable name
Set pmark = ActiveCell
Range(pmark).EntireRow.Select
Selection.Copy
'switch to secondary sheet
Sheets("cashc").Activate
Range("A1").Activate
Do Until ActiveCell = ""
If ActiveCell <> "" Then
ActiveCell.Offset(1, 0).Activate
End If
Loop
Selection.PasteSpecial xlPasteValues
'return to variable location
Sheets("invoices").Range(pmark).Activate
Range(pmark).Offset(-1, 0).EntireRow.Select
Selection.Copy
'switch to secondary sheet again
Sheets("cashc").Activate
Range("A1").Activate
Do Until ActiveCell = ""
If ActiveCell <> "" Then
ActiveCell.Offset(1, 0).Activate
End If
Loop
Selection.PasteSpecial xlPasteValues
'closure of loop
Sheets("invoices").Range(pmark).Activate
pmark.Offset(1, 0).Activate
Set pmark = Nothing
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub
I've written the below and am a little stumped as to why it's not working.
The code is supposed to scan column B on the Invoices sheet, when it comes across an entry reading "payment", it marks the cell with as "pmark" it should then copy that line to the next available line in the CashC sheet and then repeat the process for the line above "pmark".
Unfortunately, all it's doing is cycling through the list in column B on Invoices as if the rest of the code weren't even there!
Ideas? Suggestions?
:dunno
Option Explicit
Sub findinvoice()
Dim pmark As Range
'set start point
Sheets("invoices").Range("B1").Activate
Do Until ActiveCell = ""
If ActiveCell = "payment" Then
'mark activecell as variable name
Set pmark = ActiveCell
Range(pmark).EntireRow.Select
Selection.Copy
'switch to secondary sheet
Sheets("cashc").Activate
Range("A1").Activate
Do Until ActiveCell = ""
If ActiveCell <> "" Then
ActiveCell.Offset(1, 0).Activate
End If
Loop
Selection.PasteSpecial xlPasteValues
'return to variable location
Sheets("invoices").Range(pmark).Activate
Range(pmark).Offset(-1, 0).EntireRow.Select
Selection.Copy
'switch to secondary sheet again
Sheets("cashc").Activate
Range("A1").Activate
Do Until ActiveCell = ""
If ActiveCell <> "" Then
ActiveCell.Offset(1, 0).Activate
End If
Loop
Selection.PasteSpecial xlPasteValues
'closure of loop
Sheets("invoices").Range(pmark).Activate
pmark.Offset(1, 0).Activate
Set pmark = Nothing
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub