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
383 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 49

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 49

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

867 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

21 Experts available now in Live!

Get 1:1 Help Now