• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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

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
matija385
Asked:
matija385
  • 5
  • 4
1 Solution
 
Rgonzo1971Commented:
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
 
matija385Author Commented:
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
 
Rgonzo1971Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
matija385Author Commented:
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
 
Rgonzo1971Commented:
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
 
matija385Author Commented:
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
 
Rgonzo1971Commented:
Hi,

on the example file seems to work perfectly

Regards
tablica-exampleV1.xlsm
0
 
matija385Author Commented:
Hi,

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

Kind regards,
Matija
0
 
matija385Author Commented:
Extremely satisfied - quick response, thorough solution.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now