PDA

View Full Version : Solved: Check and copy rows



francis
05-21-2008, 09:53 AM
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
05-21-2008, 10:36 AM
...at least have a try at it...

grichey
05-21-2008, 11:06 AM
This is pretty close but I've mucked up my loop.

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

grichey
05-21-2008, 11:16 AM
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
05-22-2008, 11:44 AM
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
05-22-2008, 02:26 PM
edit: missing end stmt. see next

grichey
05-22-2008, 02:31 PM
Ha - missing end stmt. This one works: dont forget to marked solved if this does it for you.
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

francis
05-24-2008, 11:41 AM
Hi Gavin

I add in this line :

ElseIf (Selection = "Pending") Then
and its work as expected.

thanks and I appreciate for your help in this.

cheers

petethegreek
05-24-2008, 12:58 PM
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.
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

grichey
05-24-2008, 01:06 PM
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
05-24-2008, 01:27 PM
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:beerchug:

francis
05-25-2008, 08:55 AM
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.


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


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