dormanino
05-14-2015, 12:06 PM
Okay, this is going to be difficult to explain...iŽll do my best
in the attached excel file and in the scheenshots you can see two sheets. I need to look for information in the first sheet (see below)
13398
and compare the column a,b and c as reference to the second matrix sheet via VBA. If the "code" is found in the respective "bm" from the matrix, it adds an X...
13399
Is that possible? tried some coding...without success so far...
thank you for your attention
Sub sammelaef()
Dim plan As Worksheet
Dim plan2 As Worksheet
Set AVWMZ = Worksheets("AVWMZ")
Set sammel = Worksheets("Sammel-AEF")
'------------------
With AVWMZ
If WorksheetFunction.CountA(cells) > 0 Then
LastColumnavwmz& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
If WorksheetFunction.CountA(cells) > 0 Then
Lastrowavwmz& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End With
'------------------
With sammel
If WorksheetFunction.CountA(cells) > 0 Then
LastColumn1Sammel& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
If WorksheetFunction.CountA(cells) > 0 Then
lastrowsammel& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End With
'------------------
For clsammel = 2 To lastrowsammel
For clavwmzcode = 2 To Lastrowavwmz
a = sammel.range("a" & clsammelcode) & sammel.range("b" & clsammelcode) & sammel.range("c" & clsammelcode)
b = AVWMZ.range("a" & clavwmz) & AVWMZ.range("b" & clavwmz) & AVWMZ.range("c" & clavwmz)
If a = b Then
d = AVWMZ.cells(, clavwmzbm)
End If
Next clavwmz
Next clsammel
End Sub
in the attached excel file and in the scheenshots you can see two sheets. I need to look for information in the first sheet (see below)
13398
and compare the column a,b and c as reference to the second matrix sheet via VBA. If the "code" is found in the respective "bm" from the matrix, it adds an X...
13399
Is that possible? tried some coding...without success so far...
thank you for your attention
Sub sammelaef()
Dim plan As Worksheet
Dim plan2 As Worksheet
Set AVWMZ = Worksheets("AVWMZ")
Set sammel = Worksheets("Sammel-AEF")
'------------------
With AVWMZ
If WorksheetFunction.CountA(cells) > 0 Then
LastColumnavwmz& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
If WorksheetFunction.CountA(cells) > 0 Then
Lastrowavwmz& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End With
'------------------
With sammel
If WorksheetFunction.CountA(cells) > 0 Then
LastColumn1Sammel& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End If
If WorksheetFunction.CountA(cells) > 0 Then
lastrowsammel& = .cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End With
'------------------
For clsammel = 2 To lastrowsammel
For clavwmzcode = 2 To Lastrowavwmz
a = sammel.range("a" & clsammelcode) & sammel.range("b" & clsammelcode) & sammel.range("c" & clsammelcode)
b = AVWMZ.range("a" & clavwmz) & AVWMZ.range("b" & clavwmz) & AVWMZ.range("c" & clavwmz)
If a = b Then
d = AVWMZ.cells(, clavwmzbm)
End If
Next clavwmz
Next clsammel
End Sub