DesertEagle61
New Member
- Joined
- Jul 30, 2012
- Messages
- 2
Hi guys,
I need a macro which will work with 2 Tables (Excel 2010).
The first table contains different columns and 2 ID columns which I need to insert into the second table if these values dont exist in the second one.
I want to create a mapping for these distinct values so I thought having a second table would be the best idea, especially considering that the first table will be changed every few days and me not wanting that to affect my mapping table.
I have come up with this (my vba knowledge is limited):
atm it does copy all values of my first table into the second one and deletes all duplicates.
2 things I did not manage to solve until now:
- I could not set the starting positions to be a variable accoriding to the first empty row in my mapping table and the last filled row in my first table.
- more importantly: atm it copies all distinctive values, but i want to check if the value exists in my mapping table and if thats the case to do nothing. / i only want new combinations of IDs in my mapping.
Help will be appreciated.
I need a macro which will work with 2 Tables (Excel 2010).
The first table contains different columns and 2 ID columns which I need to insert into the second table if these values dont exist in the second one.
I want to create a mapping for these distinct values so I thought having a second table would be the best idea, especially considering that the first table will be changed every few days and me not wanting that to affect my mapping table.
I have come up with this (my vba knowledge is limited):
Code:
Sub addNew()
Dim i As Long
Dim k As Long
Dim l As Long
Dim boo As Boolean
Sheets("Increments_Status").Select
'k has to be a vriable starting position
'k = Range("Increment ID").Rows.Count
k = 0
l = 0
Sheets("Increments").Select
' variable doesnt work atm
For i = 1 To 1000
'Cells(Rows.Count, 1).End(xlUp).Row
'If Worksheets("Increments").Cells(j + 1, 2).Value = Worksheets("Increments_Status").Cells(l + 1, 2).Value Then
If boo = True Then
k = k + 1
Worksheets("Increments_Status").Cells(k + 1, 1).Value = Worksheets("Increments").Cells(i + 1, 1).Value
Worksheets("Increments_Status").Cells(k + 1, 2).Value = Worksheets("Increments").Cells(i + 1, 24).Value
End If
Next i
Sheets("Increments_Status").Select
ActiveSheet.Range("$A$1:$B$1000").RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes
End Sub
atm it does copy all values of my first table into the second one and deletes all duplicates.
2 things I did not manage to solve until now:
- I could not set the starting positions to be a variable accoriding to the first empty row in my mapping table and the last filled row in my first table.
- more importantly: atm it copies all distinctive values, but i want to check if the value exists in my mapping table and if thats the case to do nothing. / i only want new combinations of IDs in my mapping.
Help will be appreciated.