Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: how to copy data with multiple cases matched.

  1. #1
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10

    how to copy data with multiple cases matched.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    Last edited by AliGW; 2020-10-25 at 10:07 AM. Reason: Poor title updated

  2. #2
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10
    No suggestions!

  3. #3
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10
    Still No suggestions!

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    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.

  5. #5
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10
    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.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    Sorry, I can't figure out what you're trying to do.
    Hopefully somebody else will be able to assist.

  7. #7
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    272
    Articles
    0
    Excel Version
    2019
    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.

  8. #8
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10
    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!

  9. #9
    Acolyte Zshan's Avatar
    Join Date
    Apr 2020
    Posts
    29
    Articles
    0
    Excel Version
    Excel10
    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!

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •