Solved

Excel Macro - need three conditions met - two must be equal and third must be different from values in column on another sheet

Posted on 2014-12-09
9
378 Views
Last Modified: 2014-12-10
Hello,

I'm new here and must say i'm very glad that i have found this page. It helped me a lot for now, but i can't find answer to my recent challenge.

I have file (attached) with several sheets, but main two sheets i need help with are called Predispit_Konzultacije and Trening_tablica. I need certain cells copied from Predispit_Konzultacije sheet to first empty row in Trening_tablica sheet if several conditions are met on Predispit_Konzultacije sheet:
1. Checkbox must be checked (that is solved by linking to cell where checkbox "says" TRUE or FALSE
2. Column L must have value "BUS Tech"
3. If value from columns F, H, I from Predispit_Konzultacije sheet already exist in Tablica_trening sheet in columns L, M , N

So, basically, i need to check cell value, comapare it to columns L, M, N in Tablica_trening and if it doesn't exist there, copy cells from row in Predispit_Konzultacije sheet to Trening_tablica. First two conditions i manage, but third (to find out if value from cell exist somewhere in sheet Tablica trening, and if does, not to copy that row, and if it doesn't, copy it). There is a button that runs macro called "Kopiraj u tablicu" on Predispit_Konzultacije sheet.

The problem is, if I select one row, and copy it, and then two more, it wil copy first row twice. I don't want that to happen.

I would very much appreciate any help i could get. I'm quite new to macro/vba in excel. Thanks,

Kind regards,
Matija

tablica-example.xlsm
0
Comment
Question by:matija385
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40488343
Hi,

you could something like this  (the file headers do not correspond to your question)
For Each c In Range(Range("A3"), Range("B" & Cells.Rows.Count).End(xlUp).Offset(0, -1))
    If Range("P" & c.Row) = True And Range("L" & c.Row) = "BUS Tech" Then
        On Error Resume Next
        Res = Evaluate("=MATCH(F" & c.Row & "&H" & c.Row & "&I" & c.Row & ",Trening_tablica!N3:N22&Trening_tablica!L3:L22&Trening_tablica!K3:K22,0)")
        On Error GoTo 0
        If IsError(Res) Then
            ' Your code
        End If
    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:matija385
ID: 40488595
Hi Rgonzo1971,

Thank you for your help. I'm not quite sure where to put this code. What i would like to do is to copy cells from one sheet to another (not entire row, but just some cells from row) to another sheet if conditions are met. So, to copy data to another sheet, checkbox must be checked, in L column, value must be "BUS Tech" and data should not exist already on another sheet (cell values from F, H and I column is criteria to check if data already exist on other sheet - if any of data from those columns exist). If it doesn't exit, copy cells, if it does exist, don't copy cells from that row.

Thank you in advance,

Kind regards,
Matija
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40488634
pls try

something like this in a module

Sub macro()
ActiveWorkbook.Sheets("Predispit_Konzultacije").Activate
For Each C In Range(Range("A3"), Range("B" & Cells.Rows.Count).End(xlUp).Offset(0, -1))
    If Range("P" & C.Row) = True And Range("L" & C.Row) = "BUS Tech" Then
        On Error Resume Next
        Res = Evaluate("=MATCH(F" & C.Row & "&H" & C.Row & "&I" & C.Row & ",Trening_tablica!N3:N10000&Trening_tablica!L3:L10000&Trening_tablica!K3:K10000,0)")
        On Error GoTo 0
        If IsError(Res) Then
            Set destSH = Sheets("Trening_tablica")
            LastRowDest = destSH.Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("F" & C.Row).Copy Destination:=destSH.Range("N" & LastRowDest)
            Range("H" & C.Row).Copy Destination:=destSH.Range("L" & LastRowDest)
            Range("I" & C.Row).Copy Destination:=destSH.Range("K" & LastRowDest)
        End If
    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:matija385
ID: 40489003
hi, ok, i tried, not working, sorry.

Maybe i didn't explain what i need correctly.

In sheet Predispit_Konzultacije (i'll call it in this text Sheet1, it's easier :)) i have 50, or lets say 100 rows. Each row will have check box and filled data. If checkbox in row is checked and in column L is value "BUS Tech", macro has to copy cells from that row to sheet Trening_tablica (i'll call it Sheet2). That must be done for all 50, or 100 rows and cells must be copied to first empty row in Sheet 2. Cells that need to be copied are (i'll copy macro with column numbers here):

Sheets("Trening_tablica").Cells(R2, 4) = Sheets("Predispit_Konzultacije").Cells(R, 3)
Sheets("Trening_tablica").Cells(R2, 5) = Sheets("Predispit_Konzultacije").Cells(R, 4)
Sheets("Trening_tablica").Cells(R2, 15) = Sheets("Predispit_Konzultacije").Cells(R, 5)
Sheets("Trening_tablica").Cells(R2, 14) = Sheets("Predispit_Konzultacije").Cells(R, 6)
Sheets("Trening_tablica").Cells(R2, 10) = Sheets("Predispit_Konzultacije").Cells(R, 7)
Sheets("Trening_tablica").Cells(R2, 12) = Sheets("Predispit_Konzultacije").Cells(R, 8)
Sheets("Trening_tablica").Cells(R2, 11) = Sheets("Predispit_Konzultacije").Cells(R, 9)
Sheets("Trening_tablica").Cells(R2, 13) = Sheets("Predispit_Konzultacije").Cells(R, 10)
Sheets("Trening_tablica").Cells(R2, 7) = Sheets("Predispit_Konzultacije").Cells(R, 13)
Sheets("Trening_tablica").Cells(R2, 8) = Sheets("Predispit_Konzultacije").Cells(R, 14)

Open in new window


So, i have this macro(s):

Sub Predispit_U_Tablica()

    For I = 1 To Sheets("Predispit_Konzultacije").UsedRange.Rows.Count
        If Sheets("Predispit_Konzultacije").Cells(I, 16) = True And Sheets("Predispit_Konzultacije").Cells(I, 12) = "BUS Tech" Then
            CopyRow (I)
        End If
    Next
End Sub

Open in new window


Sub CopyRow2(ByVal R As Integer, ByVal R2 As Integer)

    Sheets("Trening_tablica").Cells(R2, 6) = Sheets("Predispit_Konzultacije").Cells(R, 3)
    Sheets("Trening_tablica").Cells(R2, 7) = Sheets("Predispit_Konzultacije").Cells(R, 4)
    Sheets("Trening_tablica").Cells(R2, 17) = Sheets("Predispit_Konzultacije").Cells(R, 5)
    Sheets("Trening_tablica").Cells(R2, 16) = Sheets("Predispit_Konzultacije").Cells(R, 6)
    Sheets("Trening_tablica").Cells(R2, 12) = Sheets("Predispit_Konzultacije").Cells(R, 7)
    Sheets("Trening_tablica").Cells(R2, 14) = Sheets("Predispit_Konzultacije").Cells(R, 8)
    Sheets("Trening_tablica").Cells(R2, 13) = Sheets("Predispit_Konzultacije").Cells(R, 9)
    Sheets("Trening_tablica").Cells(R2, 15) = Sheets("Predispit_Konzultacije").Cells(R, 10)
    Sheets("Trening_tablica").Cells(R2, 9) = Sheets("Predispit_Konzultacije").Cells(R, 13)
    Sheets("Trening_tablica").Cells(R2, 10) = Sheets("Predispit_Konzultacije").Cells(R, 14)

End Sub

Open in new window


Sub CopyRow(ByVal R As Integer)

    If WorksheetFunction.CountA(Sheets("Trening_tablica").Cells) = 0 Then
        R2 = 1
    Else
        R2 = Sheets("Trening_tablica").Range("D" & Sheets("Trening_tablica").Rows.Count).End(xlUp).Row + 1
    End If
    
    CopyRow2 R, R2
End Sub

Open in new window


In first Sub (Sub Predispit_U_Tablica()) i check for conditions in Sheet1 (in this case if cell in each row in column P (column number 16) is True and column L (column number 12) is "Bus Tech".
In Second Sub (Sub CopyRow2(ByVal R As Integer, ByVal R2 As Integer)) i define which cell from that row must be copied from sheet1 to sheet2
In third Sub (Sub CopyRow(ByVal R As Integer)) i define first empty row.

So, not all cells must be copied from sheet1 to sheet2, but some of them must be copied in first empty row on sheet2. other data that is not copied, well, nothing happens with it - stays on Sheet1, and on sheet2 empty cell in copied "row" remain empty.

i need help with that first sub and condition that macro has to check if value from row that macro is now checking in column F on sheet1 doesen't exist already in sheet2 in column N or from sheet1 value from column H doesent exist in sheet2 in column L, or from sheet1 value from column I doesn't exist in sheet2 in column K. If it exists, that entry is already in sheet2, and i don't want to duplicate it, so macro, don't copy it. If it doesent exist, and meet other two conditions (True and "BUS Tech") copy cells (defined in sub copyrows2) from sheet1 to sheet2.

Sheet1 is Predispit_Konzultacije, Sheet2 is Trening_tablica.

Sorry for this long comment, i tried to explain it as detailed as possible. Sorry for my english too. :)

Hope this helps you to help me :)

Thank you for your effort.

Kind regards,
Matija
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40489293
then try

Sub Predispit_U_Tablica()

    For I = 1 To Sheets("Predispit_Konzultacije").UsedRange.Rows.Count
        If Sheets("Predispit_Konzultacije").Cells(I, 16) = True And Sheets("Predispit_Konzultacije").Cells(I, 12) = "BUS Tech" And Sheets("Predispit_Konzultacije").Cells(I, 6) <> Sheets("Trening_tablica").Cells(I, 14) Then
            On Error Resume Next
            Res = Evaluate("=MATCH(F" & I & "&H" & I & "&I" & I & ",Trening_tablica!N3:N10000&Trening_tablica!L3:L10000&Trening_tablica!K3:K10000,0)")
            On Error GoTo 0
            If IsError(Res) Then
                CopyRow (I)
            End If
        End If
    Next
End Sub

Open in new window

0
 

Author Comment

by:matija385
ID: 40490680
Hi Rgonzo1971,

Now it's copying only last row from Predispit_Konzultacije sheet that meets criteria. If for example rows 2 and 4 meets criteria, macro is copying only last row (in this example row 4). It doesen't copy both rows. If I uncheck row 4, and run macro, it will overwrite copied row 4 (which is in Tablica_trening already) with row 2. What i need is to row 2 in that case copies data to next empty row. For row to be empty, macro must check column D in Trening_tablica.

This spreadsheet will be used for selecting students for job. In Predispit_Konzultacije (which in English means something like preliminary examination) i will have all candidates. And then i have to check those who did well on preliminary examination and job interview. Why I need this macro - i will for example select 10 candidates, and want their data to copy to next sheet (Tablica_trening) which we use for administration. And when i select those 10 candidates i press the button for copying their data to another sheet (i run macro). And then, i see one more candidate i want, so, that i don't have to unselect previous 10 candidates, and select just this one to copy his/her data to Tablica_trening.

Thank you,

Kind regards,
Matija
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40490691
Hi,

on the example file seems to work perfectly

Regards
tablica-exampleV1.xlsm
0
 

Author Comment

by:matija385
ID: 40490706
Hi,

Sorry, it was my fault, yesterday changed something and broke macro. Thank you very much.

Kind regards,
Matija
0
 

Author Closing Comment

by:matija385
ID: 40490709
Extremely satisfied - quick response, thorough solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now