Здравствуйте. Есть большая таблица, которую не очень удобно анализировать - таб1. Есть таблица поменьше - таб2. В обеих таблицах есть ключевое поле (D&B_ID). Надо на отдельном листе создать таблицу со столбцами D&B_ID, Cust_Customer и Cust_VAT Reg No. Последний столбец должех быть заполнен по принципу: если значение в столбце "Cust_VAT Reg No" в исходной таблице есть, то оно копируется в итоговую таблицу, если значение отсутствует, то туда надо скопировать соответствующее ключевому полю значение из столбца "D&B_NATIONAL IDENTIFICATION NUMBER" и выделить эту ячейку цветом. Цвета три и они зависят от значения в столбце D&B_Confidence Code. 7-10 -зеленый, 4-6 - желтый, 1-3 - розовый. Если отсутствуют значения и в первом и во втором столбцах, то ячейка остается пустой. Кроме того из итоговой таблицы должны быть исключены все записи, которые уже есть в таб2.
Хотелось бы написать макрос, который бы это все делал по нажатию кнопочки.
Может кто-нибудь помочь?
Здравствуйте. Есть большая таблица, которую не очень удобно анализировать - таб1. Есть таблица поменьше - таб2. В обеих таблицах есть ключевое поле (D&B_ID). Надо на отдельном листе создать таблицу со столбцами D&B_ID, Cust_Customer и Cust_VAT Reg No. Последний столбец должех быть заполнен по принципу: если значение в столбце "Cust_VAT Reg No" в исходной таблице есть, то оно копируется в итоговую таблицу, если значение отсутствует, то туда надо скопировать соответствующее ключевому полю значение из столбца "D&B_NATIONAL IDENTIFICATION NUMBER" и выделить эту ячейку цветом. Цвета три и они зависят от значения в столбце D&B_Confidence Code. 7-10 -зеленый, 4-6 - желтый, 1-3 - розовый. Если отсутствуют значения и в первом и во втором столбцах, то ячейка остается пустой. Кроме того из итоговой таблицы должны быть исключены все записи, которые уже есть в таб2.
Хотелось бы написать макрос, который бы это все делал по нажатию кнопочки.
- Чтобы понять и помочь - достаточно таблицы на 10-20 строк - При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу
В Правилах форума написано
Цитата
- Чтобы понять и помочь - достаточно таблицы на 10-20 строк - При этом старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу
Sub Мяу() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Tab1") arr1 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Tab2") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1) + UBound(arr2), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr1) If Len(arr1(i, 3)) Then arr1(i, 5) = Empty Else arr1(i, 3) = arr1(i, 5) If Len(arr1(i, 3)) Then Select Case arr1(i, 4) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If If Not .exists(arr1(i, 1)) Then k = k + 1 arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 3) arr3(k, 4) = arr1(i, 4) arr3(k, 5) = arr1(i, 5) .Item(arr1(i, 1)) = 1 End If Next
For i = 1 To UBound(arr2) If Len(arr2(i, 3)) Then arr2(i, 5) = Empty Else arr2(i, 3) = arr2(i, 5) If Len(arr2(i, 3)) Then Select Case arr2(i, 4) Case 1 To 3 arr2(i, 5) = 48 Case 4 To 6 arr2(i, 5) = 6 Case 7 To 10 arr2(i, 5) = 43 End Select End If End If If Not .exists(arr2(i, 1)) Then k = k + 1 arr3(k, 1) = arr2(i, 1) arr3(k, 2) = arr2(i, 2) arr3(k, 3) = arr2(i, 3) arr3(k, 4) = arr2(i, 4) arr3(k, 5) = arr2(i, 5) .Item(arr2(i, 1)) = 1 End If Next
End With With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With End Sub
[/vba]
[vba]
Код
Sub Мяу() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Tab1") arr1 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Tab2") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1) + UBound(arr2), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr1) If Len(arr1(i, 3)) Then arr1(i, 5) = Empty Else arr1(i, 3) = arr1(i, 5) If Len(arr1(i, 3)) Then Select Case arr1(i, 4) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If If Not .exists(arr1(i, 1)) Then k = k + 1 arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 3) arr3(k, 4) = arr1(i, 4) arr3(k, 5) = arr1(i, 5) .Item(arr1(i, 1)) = 1 End If Next
For i = 1 To UBound(arr2) If Len(arr2(i, 3)) Then arr2(i, 5) = Empty Else arr2(i, 3) = arr2(i, 5) If Len(arr2(i, 3)) Then Select Case arr2(i, 4) Case 1 To 3 arr2(i, 5) = 48 Case 4 To 6 arr2(i, 5) = 6 Case 7 To 10 arr2(i, 5) = 43 End Select End If End If If Not .exists(arr2(i, 1)) Then k = k + 1 arr3(k, 1) = arr2(i, 1) arr3(k, 2) = arr2(i, 2) arr3(k, 3) = arr2(i, 3) arr3(k, 4) = arr2(i, 4) arr3(k, 5) = arr2(i, 5) .Item(arr2(i, 1)) = 1 End If Next
End With With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With End Sub
Sub Мяв() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Tab1") arr1 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Tab2") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr2) .Item(arr2(i, 1)) = 1 Next k = 1 arr3(k, 1) = arr1(1, 1) arr3(k, 2) = arr1(1, 2) arr3(k, 3) = arr1(1, 3) arr3(k, 4) = arr1(1, 4) arr3(k, 5) = arr1(1, 5)
For i = 1 To UBound(arr1) If Not .exists(arr1(i, 1)) Then k = k + 1 If Len(arr1(i, 3)) Then arr1(i, 5) = Empty Else arr1(i, 3) = arr1(i, 5) If Len(arr1(i, 3)) Then Select Case arr1(i, 4) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 3) arr3(k, 4) = arr1(i, 4) arr3(k, 5) = arr1(i, 5) .Item(arr1(i, 1)) = 1 End If Next End With
With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 On Error Resume Next For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With
End Sub
[/vba]
[vba]
Код
Sub Мяв() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Tab1") arr1 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Tab2") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr2) .Item(arr2(i, 1)) = 1 Next k = 1 arr3(k, 1) = arr1(1, 1) arr3(k, 2) = arr1(1, 2) arr3(k, 3) = arr1(1, 3) arr3(k, 4) = arr1(1, 4) arr3(k, 5) = arr1(1, 5)
For i = 1 To UBound(arr1) If Not .exists(arr1(i, 1)) Then k = k + 1 If Len(arr1(i, 3)) Then arr1(i, 5) = Empty Else arr1(i, 3) = arr1(i, 5) If Len(arr1(i, 3)) Then Select Case arr1(i, 4) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 3) arr3(k, 4) = arr1(i, 4) arr3(k, 5) = arr1(i, 5) .Item(arr1(i, 1)) = 1 End If Next End With
With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 On Error Resume Next For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With
А можно еще вопрос? Я тут пыталась "переделать" ваш код под свою таблицу, точнее поменяла номера столбцов только, у меня же их исходно очень много, и теперь куда-то делся цвет! Не посмотрите, что случилось?
[vba]
Код
Sub Macro1() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Total Duns Assigned and Append") arr1 = .Range(.Cells(1, 30), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Matched_on_NatID") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr2) .Item(arr2(i, 1)) = 1 Next k = 1 arr3(k, 1) = arr1(1, 1) arr3(k, 2) = arr1(1, 2) arr3(k, 3) = arr1(1, 6) arr3(k, 4) = arr1(1, 15) arr3(k, 5) = arr1(1, 30)
For i = 1 To UBound(arr1) If Not .exists(arr1(i, 1)) Then k = k + 1 If Len(arr1(i, 6)) Then arr1(i, 30) = Empty Else arr1(i, 6) = arr1(i, 30) If Len(arr1(i, 6)) Then Select Case arr1(i, 15) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 6) arr3(k, 4) = arr1(i, 15) arr3(k, 5) = arr1(i, 30) .Item(arr1(i, 1)) = 1 End If Next End With
With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 On Error Resume Next For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With
End Sub
[/vba]
А можно еще вопрос? Я тут пыталась "переделать" ваш код под свою таблицу, точнее поменяла номера столбцов только, у меня же их исходно очень много, и теперь куда-то делся цвет! Не посмотрите, что случилось?
[vba]
Код
Sub Macro1() Dim arr1, arr2, arr3 Dim i&, k& With Sheets("Total Duns Assigned and Append") arr1 = .Range(.Cells(1, 30), .Cells(Rows.Count, 1).End(xlUp)).Value End With With Sheets("Matched_on_NatID") arr2 = .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp)).Value End With ReDim arr3(1 To UBound(arr1), 1 To 5) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr2) .Item(arr2(i, 1)) = 1 Next k = 1 arr3(k, 1) = arr1(1, 1) arr3(k, 2) = arr1(1, 2) arr3(k, 3) = arr1(1, 6) arr3(k, 4) = arr1(1, 15) arr3(k, 5) = arr1(1, 30)
For i = 1 To UBound(arr1) If Not .exists(arr1(i, 1)) Then k = k + 1 If Len(arr1(i, 6)) Then arr1(i, 30) = Empty Else arr1(i, 6) = arr1(i, 30) If Len(arr1(i, 6)) Then Select Case arr1(i, 15) Case 1 To 3 arr1(i, 5) = 48 Case 4 To 6 arr1(i, 5) = 6 Case 7 To 10 arr1(i, 5) = 43 End Select End If End If arr3(k, 1) = arr1(i, 1) arr3(k, 2) = arr1(i, 2) arr3(k, 3) = arr1(i, 6) arr3(k, 4) = arr1(i, 15) arr3(k, 5) = arr1(i, 30) .Item(arr1(i, 1)) = 1 End If Next End With
With Sheets.Add .Cells(1, 1).Resize(k, 4).NumberFormat = "@" .Cells(1, 1).Resize(k, 4) = arr3 On Error Resume Next For i = 1 To k If Len(arr3(i, 5)) Then .Cells(i, 3).Interior.ColorIndex = arr3(i, 5) End If Next End With
А ведь наверняка существует какой-нибудь способ, чтобы в каждой строке листа, например в последней колонке поставить checkbox? [moder]Вопрос не в тему. Читайте Правила форума[/moder]
А ведь наверняка существует какой-нибудь способ, чтобы в каждой строке листа, например в последней колонке поставить checkbox? [moder]Вопрос не в тему. Читайте Правила форума[/moder]Tunka-s