VBA help

D

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
I have a list of Banks in Sheet Bank that need to be Compared to Sheet Mapping, if bank not found in Sheet Mapping then list the new bank in sheet New Bank

<TABLE style="WIDTH: 610pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=812 border=0 x:str><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6656" width=182><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 129pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=172 height=17>Sheet "Bank" Range "A6:A"</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 147pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=196>Sheet "New Bank" Range "A6:A"</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=70> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 137pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=182>Sheet Mapping Range "A6:A"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bank</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">New Bank</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Banks</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="CITI ">CITI </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="CITI ">CITI </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>JP</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">JP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">RBS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BONY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
 
Sort by date Sort by votes

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Ok i found this code, which compares two sets of data, but rather the msgbox returning the result, i woud like the code to list the descrepancies in Sheet New Bank Range A6:A
Code: 
Sub FindMissing()
Dim myRange1, myRange2, cel As Range
Set myRange1 = Range("A1", Range("A1").End(xlDown))
For Each cel In myRange1
    Set myRange2 = Range("B1", Range("B1").End(xlDown))
    With myRange2
        If .Find(What:=cel, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext) Is Nothing Then
        MsgBox cel
        End If
    End With
Next
        
End Sub
 
Upvote 0
The following assumes that the header in Column A for each sheet is in Row 5 and that the data starts in Row 6...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ListNewBanks()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> wksBank         <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wksNewBank      <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wksMapping      <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> MappingRng      <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Cnt             <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow         <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i               <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> MatchVal        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wksBank = Worksheets("Bank")<br>    <SPAN style="color:#00007F">Set</SPAN> wksNewBank = Worksheets("New Bank")<br>    <SPAN style="color:#00007F">Set</SPAN> wksMapping = Worksheets("Mapping")<br><br>    <SPAN style="color:#00007F">With</SPAN> wksMapping<br>        <SPAN style="color:#00007F">Set</SPAN> MappingRng = .Range("A6", .Cells(.Rows.Count, "A").End(xlUp))<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <br>    Cnt = 1<br>    <SPAN style="color:#00007F">With</SPAN> wksBank<br>        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<br>        <SPAN style="color:#00007F">For</SPAN> i = 6 <SPAN style="color:#00007F">To</SPAN> LastRow<br>            MatchVal = Application.Match(.Cells(i, "A").Value, MappingRng, 0)<br>            <SPAN style="color:#00007F">If</SPAN> IsError(MatchVal) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> Cnt = 1 <SPAN style="color:#00007F">Then</SPAN><br>                    wksNewBank.Range("A6").Value = .Cells(i, "A").Value<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    wksNewBank.Cells(wksNewBank.Rows.Count, "A").<SPAN style="color:#00007F">End</SPAN>(xlUp)(2) = .Cells(i, "A").Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                Cnt = Cnt + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <br>    MsgBox "Completed...", vbInformation<br>        <br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hi Donai,
Try:
Rich (BB code): 

Sub Test()

  Const TitleCell = "A6"

  Dim a, b, c
  Dim r As Long, i As Long, k As String

  ' Copy banks list into a()
  With Sheets("Bank")
    If .FilterMode Then .ShowAllData
    a = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
  End With

  ' Copy banks list of "Mapping" into b()
  With Sheets("Mapping")
    If .FilterMode Then .ShowAllData
    b = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
  End With

  ' Create dest array
  ReDim c(1 To UBound(a), 1 To 1)
  i = 1: c(i, 1) = "New Bank"

  ' Main
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    ' Create mapping banks dictionary
    For r = 2 To UBound(b)
      k = Trim(b(r, 1))
      If Len(k) Then .Item(k) = 0
    Next
    ' Compare banks
    For r = 2 To UBound(a)
      k = Trim(a(r, 1))
      If Len(k) Then
        If Not .Exists(k) Then
          i = i + 1
          c(i, 1) = k
        End If
      End If
    Next
  End With

  ' Copy the differences into sheet "New Bank"
  With Sheets("New Bank")
    If .FilterMode Then .ShowAllData
    .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp)).ClearContents
    .Range(TitleCell).Resize(i).Value = c
  End With

End Sub
Regards
 
Last edited:
Upvote 0
Thankyou Domenic. This is exactly what i wanted.
Domenic said:
The following assumes that the header in Column A for each sheet is in Row 5 and that the data starts in Row 6...

Option Explicit

Sub ListNewBanks()

Dim wksBank As Worksheet
Dim wksNewBank As Worksheet
Dim wksMapping As Worksheet
Dim MappingRng As Range
Dim Cnt As Long
Dim LastRow As Long
Dim i As Long
Dim MatchVal As Variant

Set wksBank = Worksheets("Bank")
Set wksNewBank = Worksheets("New Bank")
Set wksMapping = Worksheets("Mapping")

With wksMapping
Set MappingRng = .Range("A6", .Cells(.Rows.Count, "A").End(xlUp))
End With

Cnt = 1
With wksBank
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 6 To LastRow
MatchVal = Application.Match(.Cells(i, "A").Value, MappingRng, 0)
If IsError(MatchVal) Then
If Cnt = 1 Then
wksNewBank.Range("A6").Value = .Cells(i, "A").Value
Else
wksNewBank.Cells(wksNewBank.Rows.Count, "A").End(xlUp)(2) = .Cells(i, "A").Value
End If
Cnt = Cnt + 1
End If
Next i
End With

MsgBox "Completed...", vbInformation

End Sub
Click to expand...
 
Upvote 0
Domenic is it possible to get the code to return Unique Bank in "NewBank"? If there are duplicate in Sheet Bank then i need the code to only return unique bank in sheet NewBank.


Domenic said:
The following assumes that the header in Column A for each sheet is in Row 5 and that the data starts in Row 6...

Option Explicit

Sub ListNewBanks()

Dim wksBank As Worksheet
Dim wksNewBank As Worksheet
Dim wksMapping As Worksheet
Dim MappingRng As Range
Dim Cnt As Long
Dim LastRow As Long
Dim i As Long
Dim MatchVal As Variant

Set wksBank = Worksheets("Bank")
Set wksNewBank = Worksheets("New Bank")
Set wksMapping = Worksheets("Mapping")

With wksMapping
Set MappingRng = .Range("A6", .Cells(.Rows.Count, "A").End(xlUp))
End With

Cnt = 1
With wksBank
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 6 To LastRow
MatchVal = Application.Match(.Cells(i, "A").Value, MappingRng, 0)
If IsError(MatchVal) Then
If Cnt = 1 Then
wksNewBank.Range("A6").Value = .Cells(i, "A").Value
Else
wksNewBank.Cells(wksNewBank.Rows.Count, "A").End(xlUp)(2) = .Cells(i, "A").Value
End If
Cnt = Cnt + 1
End If
Next i
End With

MsgBox "Completed...", vbInformation

End Sub
Click to expand...
 
Upvote 0
Vladimir is it possible to get the code to return Unique Bank in "NewBank"? If there are duplicates in Sheet Bank then i need the code to only return unique bank in sheet NewBank.

ZVI said:
Hi Donai,
Try:
Rich (BB code): 
Sub Test()
 
 Const TitleCell = "A6"
 
 Dim a, b, c
 Dim r As Long, i As Long, k As String
 
 ' Copy banks list into a()
 With Sheets("Bank")
   If .FilterMode Then .ShowAllData
   a = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
 End With
 
 ' Copy banks list of "Mapping" into b()
 With Sheets("Mapping")
   If .FilterMode Then .ShowAllData
   b = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
 End With
 
 ' Create dest array
 ReDim c(1 To UBound(a), 1 To 1)
 i = 1: c(i, 1) = "New Bank"
 
 ' Main
 With CreateObject("Scripting.Dictionary")
   .CompareMode = 1
   ' Create mapping banks dictionary
   For r = 2 To UBound(b)
     k = Trim(b(r, 1))
     If Len(k) Then .Item(k) = 0
   Next
   ' Compare banks
   For r = 2 To UBound(a)
     k = Trim(a(r, 1))
     If Len(k) Then
       If Not .Exists(k) Then
         i = i + 1
         c(i, 1) = k
       End If
     End If
   Next
 End With
 
 ' Copy the differences into sheet "New Bank"
 With Sheets("New Bank")
   If .FilterMode Then .ShowAllData
   .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp)).ClearContents
   .Range(TitleCell).Resize(i).Value = c
 End With
 
End Sub
Regards
Click to expand...
 
Upvote 0
Sure, try:
Rich (BB code): 

Sub Test1()

  Const TitleCell = "A6"

  Dim a, b, c, v
  Dim r As Long, i As Long, k As String

  ' Copy banks list into a()
  With Sheets("Bank")
    If .FilterMode Then .ShowAllData
    a = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
  End With

  ' Copy banks list of "Mapping" into b()
  With Sheets("Mapping")
    If .FilterMode Then .ShowAllData
    b = .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp))
  End With

  ' Create dest array
  ReDim c(1 To UBound(a), 1 To 1)

  ' Main
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    ' Create mapping banks dictionary
    For r = 2 To UBound(b)
      k = Trim(b(r, 1))
      If Len(k) Then .Item(k) = 0
    Next
    ' Compare mapping values with banks dictionary keys
    For r = 2 To UBound(a)
      k = Trim(a(r, 1))
      If Len(k) Then
        If Not .Exists(k) Then
          i = i + 1
          c(i, 1) = k
        End If
      End If
    Next
    ' Create unique of c()
    If i Then
      .RemoveAll
      For r = 1 To i
        .Item(c(r, 1)) = 0
      Next
      i = .Count
      c = WorksheetFunction.Transpose(.Keys)
    End If
  End With

  ' Copy the differences into sheet "New Bank"
  With Sheets("New Bank")
    If .FilterMode Then .ShowAllData
    .Range(TitleCell, .Cells(.Rows.Count, Left(TitleCell, 1)).End(xlUp)).ClearContents
    .Range(TitleCell) = "New Bank"
    If i Then .Range(TitleCell).Offset(1).Resize(i).Value = c
  End With

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top