Help with writing this code.
Hi All,
I've just got to grips with the basics of VBA but I need your expertise with a particular code. Looking at the data below, I would like the code to be written so if the the answer is yes is Column D (contractor info) I want the entire row to be cut and added to sheet 2. Sheet 2 would have the same headings. there will be no blanks at all.
District Work Request Audit Result Contractor Info provided
21 39NGWS D4 yes
81 01MNRK D4 No
81 01MQQZ D5 No
81 01MSCF D7 Yes
12 37JZLW D8 Yes
I'd be grateful if someone could help me with this.
thanks in advance
Re: Help with writing this code.
sorry the format changed so it would be something like this
District WR AR Contractor info
12 3654774LQ D1 Yes
Re: Help with writing this code.
try like
vb Code:
dim sumrows as range, c as range
nextrow = sheets("sheet2").cells(sheets("sheet2").rows.count, 1).end(xlup).row + 1
for each c in range("d:d")
if isempty(c) then exit for ' finish on empty cell
if c.value = "Yes" then
if sumrows is nothing then
set sumrows = c.entirerow
else
set sumrows = union(sumrows, c.entirerow)
end if
end if
next
sumrows.Copy Sheets("sheet2").Range("a" & nextrow)
sumrows.Delete xlShiftUp
Re: Help with writing this code.
Quote:
Originally Posted by
westconn1
try like
vb Code:
dim sumrows as range, c as range
nextrow = sheets("sheet2").cells(sheets("sheet2").rows.count, 1).end(xlup).row + 1
for each c in range("d:d")
if isempty(c) then exit for ' finish on empty cell
if c.value = "Yes" then
if sumrows is nothing then
set sumrows = c.entirerow
else
set sumrows = union(sumrows, c.entirerow)
end if
end if
next
sumrows.Copy Sheets("sheet2").Range("a" & nextrow)
sumrows.Delete xlShiftUp
Thank you very much for this.
I copied and pasted this in the workbook but to no avail. Please forgive my ignorance but I'm unable to run this macro when I press F5. As I said I'm a novice so please be patient with me.
Re: Help with writing this code.
Put the posted code within Sub MyCode() and End Sub lines in a module. Run it by pressing F5 within the Visual Basic editor, or from the workbook via Tools -> Macro menu.
Re: Help with writing this code.
Quote:
Originally Posted by
His Nibbs
Put the posted code within Sub MyCode() and End Sub lines in a module. Run it by pressing F5 within the Visual Basic editor, or from the workbook via Tools -> Macro menu.
Hi,
I did and it came with the following error:
Runtime Error '450'
wrong number of arguments or invalid property assignment
please let me know.
Re: Help with writing this code.
Quote:
wrong number of arguments or invalid property assignment
dunno, which line?
i tested before posting, show the code as you have it
1 Attachment(s)
Re: Help with writing this code.
Quote:
Originally Posted by
westconn1
dunno, which line?
i tested before posting, show the code as you have it
This is what it looks like.
Re: Help with writing this code.
it would help if you answered the question
though from your picture you have no sheet with name of sheet2, change code to suit