Can any one help me plz!!!!
i have a sheet with 10 columns and 20 rows.
For eg say from A to k columns and 1 to 20 rows of data.
if a value matches in column G i.e G2=25 and G5=-25 then i need to copy and paste both the row G2 and G5 in another sheet of same workbook
Please help me on this
anon wrote:
Hi,1 & 2.
27-Apr-08
Hi
1 & 2. Both I & J are just there to mark the cells Duplicate 1
Duplicate 2, Duplicate 500 etc before they are deleted. They are no
column letters
3. To change this you need to replace the one line with these lines
Dim toend As Lon
toend = Range("H" & Rows.Count).End(xlUp).Ro
For Each C In ActiveSheet.Range("H6:H" & toend
4. C1 = C.Offset(0, 1).Valu
This is checking the column one row to the right of H and storin
the value (column I
5. C2 = C.Offset(0, 2).Valu
This is checking the column two rows to the right of H and storing th
value (column J
6. For J = 6 To 1
If you have added in the code described above in step 3 just chang
this line t
For J = 6 to toen
So your code will look like this (I have added explanations
Sub FINDANDDELETE(
Dim C, FINDC, C1, C2, FOUNDCELL, I, J, SRCHRN
SRCHRNG = ActiveSheet.Range("H6:H17") 'change this rang
I =
J = 50
Dim toend As Lon
toend = Range("H" & Rows.Count).End(xlUp).Row 'find the last ro
in column H and store it as toen
For Each C In ActiveSheet.Range("H6:H" & toend) ' for each cell (C) i
H6 to the last used row in column
C.Select 'select the cel
If Left(C.Value, 1) = "-" Then 'check if the value of the cell is
minus numbe
FINDC = "+" & C.Value 'if it is a minus number set FINDC (ie. th
value to search for) as a poitive numbe
Else 'if it is not a minus numbe
FINDC = "-" & C.Value 'set FINDC (ie. the value to search for) as
minus numbe
End I
C1 = C.Offset(0, 1).Value 'set C1 as the value of the cell in the sam
row in column
C2 = C.Offset(0, 2).Value 'set C1 as the value of the cell in the sam
row in column
With ActiveSheet.Cell
Set FOUNDCELL = ActiveSheet.Range("H6:H" & toend).FIND(FINDC,
xlValues) 'FOUNDCELL is what we're looking fo
End Wit
If Not FOUNDCELL Is Nothing Then 'if the matching value is coun
in column
FOUNDCELL.Activate 'activate the cell where it is foun
If FOUNDCELL.Offset(0, 1).Value = C1 Then 'check the cell i
column I matched the column I on the row we are searching fro
If FOUNDCELL.Offset(0, 2).Value = C2 Then 'check the cell i
column J matched the column I on the row we are searching fro
FOUNDCELL.Value = "DUPLICATE" & I 'if all 3 cells match set th
cell value as DUPLICATE and a number (eg. DUPLICATE1
C.Value = "DUPLICATE" & J 'if all 3 cells match set the origina
cell value as DUPLICATE and a number (eg. DUPLICATE500
Els
'DO NOTHIN
End I
End I
End I
I = I +
J = J +
Next C 'do this for all of the cell values in column
Dim RNG As Rang
For J = 6 To toend 'change this to be the row numbers of your rang
Set RNG = ActiveSheet.Range("H" & J) 'change this to be the colum
'search through H6 to the ast cell in column
number of your rang
RNG.Selec
If Left(RNG.Value, 9) = "DUPLICATE" Then 'if the left of the cel
value is duplicat
RNG.EntireRow.Delete 'delete the ro
J = J - 1 'now check the row above (as we have just deleted a row
Els
End I
Next
End Su
Shout if you have any more questions.
Previous Posts In This Thread:
DELETE DEBIT AND CREDIT FIGURES ROW BY MACRO
Hi all, I have data in sheet (see below
ROW A E F-----co
1 3080 G16 1
2 500 G16 1
3 -3080 G16 1
4 3080 G16 1
5 -3080 G16 1
6 -3080 G16 1
7 5040 G34 1
8 52590 G34 1
9 -5040 G34 1
10 5040 G34 1
11 -5040 G34 1
12 -5040 G34 1
I want macro which should check values in column E and F in row by ro
like E1 & F1 and if E1 & F1 value match in any other row of column
and F like in above table I have G16 and 11 in cell E1 & F1 and excect
value in cell E3 & F3 and so on. So when same row value in column E
and F match and in the same row of where those value matching if they
have debit and criedit amount in column A then both debit and credit
figures rows should be deleted. i hope that i have explained what i
am trying to say. Please if any friend can help.
Macro should bring result like this (see below)
ROW A E F-----col
1 500 G16 12
2 -3080 G16 12
3 52590 G34 12
4 -5040 G34 12
Here's some code to start you off;Sub FINDANDDELETE()Dim C, FINDC, C1, C2,
Here's some code to start you off;
Sub FINDANDDELETE()
Dim C, FINDC, C1, C2, FOUNDCELL, I, J, SRCHRNG
SRCHRNG = ActiveSheet.Range("H6:H17") 'change this range
I = 1
J = 500
For Each C In ActiveSheet.Range("H6:H17") 'change this range
C.Select
If Left(C.Value, 1) = "-" Then
FINDC = "+" & C.Value
Else
FINDC = "-" & C.Value
End If
C1 = C.Offset(0, 1).Value
C2 = C.Offset(0, 2).Value
With ActiveSheet.Cells
Set FOUNDCELL = ActiveSheet.Range("H6:H17").FIND(FINDC, ,
xlValues) 'change this range
End With
If Not FOUNDCELL Is Nothing Then
FOUNDCELL.Activate
If FOUNDCELL.Offset(0, 1).Value = C1 Then
If FOUNDCELL.Offset(0, 2).Value = C2 Then
FOUNDCELL.Value = "DUPLICATE" & I
C.Value = "DUPLICATE" & J
Else
'DO NOTHING
End If
End If
End If
I = I + 1
J = J + 1
Next C
Dim RNG As Range
For J = 6 To 17 'change this to be the row numbers of your range
Set RNG = ActiveSheet.Range("H" & J) 'change this to be the column
number of your range
RNG.Select
If Left(RNG.Value, 9) = "DUPLICATE" Then
RNG.EntireRow.Delete
J = J - 1
Else
End If
Next J
End Sub
Here's what you need to do;
Put the code in the worksheet object where your values are. Change the
ranges in the code - in this example I have used the range H6:H17 as
the first column in your table - in your example above it would be A1
to A12. I have marked in the code where you need to change the ranges
to suit your worksheet.
Run the code. It checks for a match and if it finds a match it marks
it as DUPLICATE. Then when it has found all of the DUPLICATES it
deletes these rows. I have tested it and it works perfectly for me.
Re: DELETE DEBIT AND CREDIT FIGURES ROW BY MACRO
hi anon thank for replying. I checked your macro and it works fine
but it slightly giving different result on the sheet the one I got. I
already have macro in my sheet (please see below). It works fine but
as you can see that it only match value from column E and then delete
DR and CR figures from column A. but i want it look value in column F
as well as i explained in my question above. is it possible that you
can do some amendments in macro below and then it should check value
from column E & F and then delete DR and CR figures. as this macro
gives exact result what i want but just need to add column F which i
have no clue how i'll do it.
Sub DELDRCR()
HdgRow =3D 5
i1 =3D Cells(Rows.Count, "E").End(xlUp).Row
While i1 >=3D HdgRow + 2
j1 =3D 1
While i1 - j1 > HdgRow And Cells(i1, "E").Value =3D Cells(i1 -
j1, "E").Value
If -Cells(i1, "A") =3D Cells(i1 - j1, "A") _
And IsNumeric(Cells(i1, "D")) Then
Rows(i1).DELETE
Rows(i1 - j1).DELETE
i1 =3D i1 - 2
j1 =3D 0
End If
j1 =3D j1 + 1
Wend
i1 =3D i1 - 1
Wend
End Sub
The code I gave you checks columns E & F (if you've changed the rangesto suit
The code I gave you checks columns E & F (if you've changed the ranges
to suit your sheet as i explained).
Basically my code finds the match in the first column, then checks the
next two columns. If they all match the rows will get deleted.
Re: DELETE DEBIT AND CREDIT FIGURES ROW BY MACRO
Hi anon I just have few questions about your code so I can better
understand how it works. Can you please give me little bit
explaination if you don=92t mind
1 - "I =3D 1" what this recommend
2 - "J =3D 500" what this recommend
3 - "For Each C In ActiveSheet.Range("H6:H17") " how can I change
this range to H6 to the last value cell in column H
4 - "C1 =3D C.Offset(0, 1).Value" which column this code is
recommending I or J
5 - "C2 =3D C.Offset(0, 2).Value" which column this code is
recommending J or K
6 - For J =3D 6 To 17 how can I change this row change from 6 to last
value cell row in column H
Hi,1 & 2.
Hi,
1 & 2. Both I & J are just there to mark the cells Duplicate 1,
Duplicate 2, Duplicate 500 etc before they are deleted. They are not
column letters.
3. To change this you need to replace the one line with these lines;
Dim toend As Long
toend = Range("H" & Rows.Count).End(xlUp).Row
For Each C In ActiveSheet.Range("H6:H" & toend)
4. C1 = C.Offset(0, 1).Value
This is checking the column one row to the right of H and storing
the value (column I)
5. C2 = C.Offset(0, 2).Value
This is checking the column two rows to the right of H and storing the
value (column J)
6. For J = 6 To 17
If you have added in the code described above in step 3 just change
this line to
For J = 6 to toend
So your code will look like this (I have added explanations)
Sub FINDANDDELETE()
Dim C, FINDC, C1, C2, FOUNDCELL, I, J, SRCHRNG
SRCHRNG = ActiveSheet.Range("H6:H17") 'change this range
I = 1
J = 500
Dim toend As Long
toend = Range("H" & Rows.Count).End(xlUp).Row 'find the last row
in column H and store it as toend
For Each C In ActiveSheet.Range("H6:H" & toend) ' for each cell (C) in
H6 to the last used row in column H
C.Select 'select the cell
If Left(C.Value, 1) = "-" Then 'check if the value of the cell is a
minus number
FINDC = "+" & C.Value 'if it is a minus number set FINDC (ie. the
value to search for) as a poitive number
Else 'if it is not a minus number
FINDC = "-" & C.Value 'set FINDC (ie. the value to search for) as a
minus number
End If
C1 = C.Offset(0, 1).Value 'set C1 as the value of the cell in the same
row in column I
C2 = C.Offset(0, 2).Value 'set C1 as the value of the cell in the same
row in column J
With ActiveSheet.Cells
Set FOUNDCELL = ActiveSheet.Range("H6:H" & toend).FIND(FINDC, ,
xlValues) 'FOUNDCELL is what we're looking for
End With
If Not FOUNDCELL Is Nothing Then 'if the matching value is cound
in column H
FOUNDCELL.Activate 'activate the cell where it is found
If FOUNDCELL.Offset(0, 1).Value = C1 Then 'check the cell in
column I matched the column I on the row we are searching from
If FOUNDCELL.Offset(0, 2).Value = C2 Then 'check the cell in
column J matched the column I on the row we are searching from
FOUNDCELL.Value = "DUPLICATE" & I 'if all 3 cells match set the
cell value as DUPLICATE and a number (eg. DUPLICATE1)
C.Value = "DUPLICATE" & J 'if all 3 cells match set the original
cell value as DUPLICATE and a number (eg. DUPLICATE500)
Else
'DO NOTHING
End If
End If
End If
I = I + 1
J = J + 1
Next C 'do this for all of the cell values in column H
Dim RNG As Range
For J = 6 To toend 'change this to be the row numbers of your range
Set RNG = ActiveSheet.Range("H" & J) 'change this to be the column
'search through H6 to the ast cell in column H
number of your range
RNG.Select
If Left(RNG.Value, 9) = "DUPLICATE" Then 'if the left of the cell
value is duplicate
RNG.EntireRow.Delete 'delete the row
J = J - 1 'now check the row above (as we have just deleted a row)
Else
End If
Next J
End Sub
Shout if you have any more questions.
Re: DELETE DEBIT AND CREDIT FIGURES ROW BY MACRO
row
, ,
d
in
et the
inal
Thanks anon thats brilliant. just another small question that i tried
your code and it work superb but for some reason its still leaving few
dr and cr figures which need to be deleted. As in my Sheet some time
same values in column E and F have dr figure in A1 and the cr figure
come in A50 so may be both dr and cr figures are too far from
eachother that macro not picking them and macro just deleting the dr
cr figures which are bit near to eachother. i know you may be
thinking that i should sort data first and then run macro but you see
i cant do this as this will ruin my whole spreadsheet. is their way
you can solve this for me. Many thanks
Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET AJAX Automatically Saving Web Form Data
http://www.eggheadcafe.com/tutorial...ce-959b56d814cc/aspnet-ajax-automaticall.aspx