how to copy data with multiple cases matched.

Zshan

New member
Joined
Apr 28, 2020
Messages
31
Reaction score
0
Points
0
Excel Version(s)
Excel10
IT seems to be easy but I cant figure out how to copy data with multiple cases matched.

its like 3 sheets:

RECORD
DATA
OUTPUT

RECORD IS MAIN SHEET,

SO ITS LIKE THIS:




Dim RA, RB, RC As Range
Dim DA1, DB1, DC1 As Range
Dim DA2, DB2, DC2, DD2, DE2, DF2, DG2 As Range
Dim OA As Range

Dim WS1, WS2, WS3 As Worksheet

Set WS1 = Workbooks("QST.xlsm").Worksheets("RECORD")
Set WS2 = Workbooks("QST.xlsm").Worksheets("DATA")
Set WS3 = Workbooks("QST.xlsm").Worksheets("OUTPUT")

Set RB = WS1.Range("B999999").End(xlUp)
Set RA = WS1.Range("B999999").End(xlUp).Offset(-1, -1)
Set RC = WS1.Range("B999999").End(xlUp).Offset(0, 1)

'THESE CELLS COULD BE CONTAING ANYTHING LIKE TEXTS,VALUES OR FORMULAS!

'NOW I WANT A CODE WHICH COULD FIND IN "DATA" SHEET A ROW THAT HAS CELL,

'IN COLUMN "B" SAME AS 'RB' (SUPPOSE AS DB1)
'IN COLUMN "C" SAME AS 'RC' (SUPPOSE AS DC1)
'AND ABOVE THAT ROW IN COLUMN "A" SAME AS 'RA' (SUPPOSE AS DA1)

'IF THESE THREE THINGS ARE MATCHED,

'FROM 'RA' MATCHED CELL TO LAST FILLED CELL IN COLUMN "B",
'IT'LL LOOK IF IN THE LAST ROW CELL IN COLUMN "C" IS FILLED BUT CELLS IN COLUMN "D","E","F" & "G" ARE EMPTY
'& IN COLUMN "A" ABOVE THAT ROW CELL TEXT = "CLEAR"

'THEN COPY ALL SELECTED & PASTE VALUES IN 'OA'

'IF ITS NOT HAPPENING IN LAST ROW BUT IN SECOND LAST OR THIRD LAST OR ANYWHERE ELSE IT SHOULD BE COPIED TILL THERE SAME LIKE IN THE FILE!

'BUT IF CASE IS LIKE DATA & RECORD THEN NOTHING SHOULD HAPPEN!

'IF CASE IS LIKE DATA1 & RECORD1 THEN OUTPUT1 SHOULDBE RESULT & SO ON!



I am uploading the file nammed QST.xlsm coz its difficult to explain question without reading this file.

I hope if anybody could help me out here!

Thanks.
 

Attachments

  • QST.xlsm
    30.8 KB · Views: 19
Last edited by a moderator:
No suggestions!
 
Still No suggestions!
 
No replies to a request is usually due to a lack of clarity as to what's required.
I suggest you tell us what the ultimate goal is here rather than us trying to figure it out from code you think would be required.

ps: all variables need to be typed even if on the same line as others ie: Dim DA2, DB2, DC2, DD2, DE2, DF2, DG2 As Range
has DG2 as type Range but DA2, DB2, DC2, DD2, DE2, and DF2 will all be type Variant.
 
Thx for reply!

Actually I am kinda new to VBA I am really sorry if I couldn't explain my question but what my ultimate goal is in the file QST.xlsm I have uploaded!
If you take a look in record & data sheets data is same so nothing comes in output sheet,
If you take a look in record1 & data1 sheets what data is different than record1 from clear row is in output1,
In output2 nothing happened coz condition of column A doesn't match,
As it happens to be in output3 because of data3 differs from record3 sheets!

I don't know how to put all this in order to write a code that is because I mixed range with variant!
Now I hope some help from this forum.
 
Sorry, I can't figure out what you're trying to do.
Hopefully somebody else will be able to assist.
 
Instead of trying to tell what is wrong and not working, suggest you explain step by step what you are trying to do. Explain it in simple English as if we were standing in line at a coffee shop. Explain it as if you were doing it manually. Let us figure out how in Excel once we understand your needs.
 
Hi alansidman,
Thx for reply,
what I wanna say is like this,

1 file = QST.xlsm

3 sheets = RECORD, DATA, OUTPUT

In sheet("RECORD") whatever data it contains just look at the last Row you'll see
in column "B" = some value,
in column "C" = date
and right above the last Row in column "A" there'd be TEXT = "CLEAR",
It makes 'em 3 things from sheet("RECORD").

Now these're 3 things to look for in sheet("DATA")

In sheet("DATA") (whole sheet) if these 3 things match: from above Row which contains text "CLEAR" (that'd become 'STARTING POINT OF COPY')

Now usually we copy whole data from where CONDITIONS MATCHED to the LAST FILLED Row but, In this case there's an exception,
there're things to look for!

In last Filled Row of sheet("DATA")
if column "B" has value & column "C" has date (which obviously there'd be) in this same Row column "D" & column "F" WOULD BE EMPTY,
and right above this last filled Row in column "A" there'd be TEXT = "CLEAR"
(If these all conditions are matched it becomes 'ENDING POINT OF COPY')

It'll be copied in sheet("OUTPUT")

This is the basic need!

let's suppose Last Filled Row doesn't match the conditons of 'ENDING POINT OF COPY'
so we'll look from DOWNWARDS to UPWARDS which Row matches with conditions to become 'ENDING POINT OF COPY' then it'be copied & pasted in sheet("OUTPUT").

in the file QST.xlsm I've put sheets as examples!
1, sheets("RECORD") & ("DATA") have same data so nothing copied in sheet("OUTPUT").
2, sheet("DATA1") has extended data than sheet("RECORD1") so sheet("OUTPUT1") has been pasted all extended data from "CLEAR" Row to last filled Row.
3, sheet("DATA2") has also extended data than sheets("RECORD2") but nothing copied in sheet("OUTPUT2") coz conditions of 'ENDING POINT OF COPY' don't match.
4, sheet("DATA3") has extended data than sheet("RECORD3") so again sheet("OUTPUT3") has been pasted all extended data from "CLEAR" Row to last filled Row.

I'm sorry if by mistake I made it complicated to understand but this is what I need!

Thx again for help!
 
Hi,
I see nobody has any suggestion, let's make it easier if we forget about exception,
What I mean is if conditions match in sheet("DATA") the Row which contains text "CLEAR" to the last filled Row how to copy everything in sheet("OUTPUT").
I think if I could find the way in different steps!
 
What I mean is if conditions match in sheet("DATA") the Row which contains text "CLEAR" to the last filled Row how to copy everything in sheet("OUTPUT").
Code:
Sub Zshan()

    Dim lr As Long
    Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
    Dim RA As String, RB As String, RC As String
    Dim fnd As Range
    Dim strtrow As Long, lastrow As Long, pasterow As Long
    
Set WS1 = Workbooks("QST.xlsm").Worksheets("RECORD")
Set WS2 = Workbooks("QST.xlsm").Worksheets("DATA")
Set WS3 = Workbooks("QST.xlsm").Worksheets("OUTPUT")

Application.ScreenUpdating = False

With WS1
    lr = .Range("B" & .Rows.Count).End(xlUp).Row
    RA = .Range("A" & lr - 1).Value
    RB = .Range("B" & lr).Value
    RC = .Range("C" & lr).Value
End With

With WS2
    Set fnd = .Range("B:B").Find(RB, , xlValues, xlWhole)
    If Not fnd Is Nothing Then
        If fnd.Offset(-1, -1).Value = RA And fnd.Offset(, 1).Value = RC Then
            ' start row
            strtrow = fnd.Row - 1
            ' last row
            lastrow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            ' rows to copy
            .Rows(strtrow & ":" & lastrow).Copy
            'where to paste
            pasterow = WS3.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + 1
            WS3.Range("A" & pasterow).PasteSpecial xlPasteValues
            'stop the marching ants
            Application.CutCopyMode = False
        End If
    Else
        MsgBox RB & " was not found in column B of the DATA sheet."
    End If
End With
    
Application.ScreenUpdating = True

End Sub
 
Hi,

Thanks for writing a code for me its great it works just fine!

Now you know what I was needed earlier, let's suppose I have sheet called "OUTPUT" which contains few Rows of column "A" text = "CLEAR",
if I want last entry of " CLEAR " to be focused,
just 1 Row below this "CLEAR" row remains,
every row below this 1 row to the last filled row of column "B" would be deleted,
in this 1 row in column "D" ,"E","F","G","J" & "K" would be cleared!

Remember this process is just for the last "CLEAR" entry (from downwards to upwards it'd be the first) & not for every "CLEAR" entry the sheet contains,

any suggestion what would be the code for that purpose!
waiting for your reply!

Thanks again.
 
any suggestion what would be the code for that purpose!
Using the macro recorder while manually doing this will give you the basis for the coding required.
 
Back
Top