Solved: Check and copy rows

[francis]

Hi all.

I need look to compare the value in column A of sheet1 to
column C on another sheet

If a match exists then I need the code to look 5 columns to the right of the same row, If this value is equal to "done" then copy the whole row to sheet "done" and if the value is "Pending" then copy the row to sheet "Pend".

If no match is found then nothing should happen.

Thanks

regards, francis

[grichey]

...at least have a try at it...

[grichey]

This is pretty close but I've mucked up my loop.

[VBA]Option Explicit
Sub comparevals()
Dim counter As Integer
Dim counter2 As Integer
Dim counter3 As Integer
Dim varVal1 As String
Dim varVal2 As String

counter = 1
counter2 = 1
counter3 = 1
Do
Sheets("sheet1").Select
Range("A" & counter).Select
varVal1 = Selection
Sheets("sheet2").Select
Range("A" & counter).Select
varVal2 = Selection

If (varVal1 = varVal2) Then
Range("F" & counter).Select
If (Selection = "done") Then

Rows(counter).Copy Destination:=Sheets("done").Range("A" & counter)
counter2 = counter2 + 1
Else


Rows(counter).Copy Destination:=Sheets("done").Range("A" & counter)
counter3 = counter3 + 1


End If





Sheets("sheet1").Select
counter = counter + 1
Range("A" & counter).Select

Loop Until IsEmpty(Selection.Offset(1, 0))

End Sub
[/VBA]

[grichey]

woops and there in the middle where the pasting is going on. Those 2 pastes need to be "A" & counter2 and "A" & counter3 respectively.

[francis]

Thank for looking into this

I run into an error message : "Complie error : Loop without Do "
on this : Loop Until IsEmpty(Selection.Offset(1, 0))

I am not sure where to change this as I see that there is a Do on top

regards, francis

[grichey]

edit: missing end stmt. see next

[grichey]

Ha - missing end stmt. This one works: dont forget to marked solved if this does it for you.
[VBA]Option Explicit
Sub comparevals()
Dim counter As Integer
Dim counter2 As Integer
Dim counter3 As Integer
Dim varVal1 As String
Dim varVal2 As String

counter = 1
counter2 = 1
counter3 = 1
Sheets("sheet1").Select
Do

Range("A" & counter).Select
varVal1 = Selection
Sheets("sheet2").Select
Range("A" & counter).Select
varVal2 = Selection

If (varVal1 = varVal2) Then
Range("F" & counter).Select
If (Selection = "done") Then

Rows(counter).Copy Destination:=Sheets("done").Range("A" & counter2)
counter2 = counter2 + 1
Else


Rows(counter).Copy Destination:=Sheets("pend").Range("A" & counter3)
counter3 = counter3 + 1


End If
End If






Sheets("sheet1").Select
counter = counter + 1
Range("A" & counter).Select

Loop Until IsEmpty(Selection)


End Sub
[/VBA]

[francis]

Hi Gavin

I add in this line :

[VBA]ElseIf (Selection = "Pending") Then[/VBA]
and its work as expected.

thanks and I appreciate for your help in this.

cheers

[petethegreek]

hi both

i know this is marked solved so is unlikely to get read

i tried this and came up with the following changes

on the done and pending sheet i insert a row at the top of the sheet before pasting this removes the need for the counters which i believe would leave lots of blank rows in your done and pending sheets

i also used ucase to force the test values into uppercase this covers differences in case.

i also added varval3 this allows the tests to happen without selecting the sheet first so should run quicker if you have lots of lines to test

Option Explicit
Sub comparevals()
Dim counter As Integer
Dim varVal1 As String
Dim varVal2 As String
Dim varval3 As String

counter = 1
Do


varVal1 = Sheets("sheet1").Range("A" & counter).Value
varVal2 = Sheets("sheet2").Range("c" & counter).Value

If (UCase(varVal1) = UCase(varVal2)) Then
varval3 = Sheets("sheet2").Range("F" & counter).Value

If UCase(varval3 = "DONE") Then

Worksheets("done").Range("A1").EntireRow.Insert 'added by petethegreek

Rows(counter).Copy Destination:=Sheets("done").Range("A1")

ElseIf UCase(varval3 = "PENDING") Then

Worksheets("pending").Range("A1").EntireRow.Insert 'added by petethegreek

Rows(counter).Copy Destination:=Sheets("pending").Range("A1")


End If
End If





Sheets("sheet1").Select
counter = counter + 1
Range("A" & counter).Select

Loop Until IsEmpty(Selection.Value)
End Sub

Ha - missing end stmt. This one works: dont forget to marked solved if this does it for you.
[vba]Option Explicit
Sub comparevals()
Dim counter As Integer
Dim counter2 As Integer
Dim counter3 As Integer
Dim varVal1 As String
Dim varVal2 As String

counter = 1
counter2 = 1
counter3 = 1
Sheets("sheet1").Select
Do

Range("A" & counter).Select
varVal1 = Selection
Sheets("sheet2").Select
Range("A" & counter).Select
varVal2 = Selection

If (varVal1 = varVal2) Then
Range("F" & counter).Select
If (Selection = "done") Then

Rows(counter).Copy Destination:=Sheets("done").Range("A" & counter2)
counter2 = counter2 + 1
Else


Rows(counter).Copy Destination:=Sheets("pend").Range("A" & counter3)
counter3 = counter3 + 1


End If
End If






Sheets("sheet1").Select
counter = counter + 1
Range("A" & counter).Select

Loop Until IsEmpty(Selection)


End Sub
[/vba]

[grichey]

I don't really follow what you're saying about the leaving of blanks in the done and pending sheets. I also wasn't familar w/ ucase so thx for the info.
As far as the counters, I'm sure that some of the more proficient members of the forum could come up with a more elegant solution than I. This was just a shot at it from someone w/ a very novice knowledge of vba...

[petethegreek]

hi gavin

sorry no offence ment


i like you am a novice and have picked up most of my knowledge from sites like this so i thought it was time to put something back

the more we share the more we learn, there always a more elegant solution

happy coding

[francis]

Hi,

Thanks for this. me too don't understand the part on leaving blanks on the sheets " done" and "pend". For Ucase, I believe this would made it become case-sensitive if I am not wrong and the macro may not produce the expected result.

[quote by grichey]This was just a shot at it from someone w/ a very novice knowledge of vba...[/quote]
This is good enough for me at my level right now


Maybe using an autofilter macro would be a more elegant solution. But I don't know how to code this. Just my thought as I am still a novice in this and still learning from experts.

cheers