0 votes
in VBA by Beginner (26 points)

I have list of accounts in sheet1 & Sheet2., need VBA to get below details..

1). If i add account number in sheet1 column "A", then "B" column should capture Currecy Code from sheet2.

2). "C"  Column should higlight missing accounts in sheet2.

3). "D" Column should highlight missing accounts in sheet1.

Saple file link provided with details....

Google drive link
Thank you in advance !


by Expert (892 points)
Not so clear. Can you explain with more details..?
by Beginner (26 points)
In sheet1, Column A, i will add account numbers manually, then it should update the remaining columns from B to C using sheet2 data.

if we add any number in sheet1 column A, which is not available in sheet2 column A, that missing numbers should be added to sheet1 column 'C'.

if i have any extra numbers in sheet2 column A, which are not available in sheet1 column A, that missing numbers should be added to sheet1 column 'D'.

My output is sheet1 columns B to D.
by Expert (892 points)
I tried to get what you are trying to do but still not clear. First try to use the sheet names in description. Another point put some of the desired output as snapshot so as to get the final and desired output.
by Beginner (26 points)


In output sheet column "A" I will add account numbers manually, remaining coulmns shoud populate automatically....

attached sample output for reference...

Google drive link provided

1 Answer

+1 vote
by Expert (892 points)

Try this code

Sub Test()
    Dim a, b, x, ws As Worksheet, sh As Worksheet, i As Long, r As Long
    Application.ScreenUpdating = False
        Set ws = ThisWorkbook.Worksheets("Raw Data")
        Set sh = ThisWorkbook.Worksheets("Output")
        sh.Range("A1").CurrentRegion.Offset(1, 1).ClearContents
        a = sh.Range("A1").CurrentRegion.Resize(100).Value: r = 2
        For i = LBound(a) + 1 To UBound(a)
            x = Application.Match(a(i, 1), ws.Columns(1), 0)
            If Not IsEmpty(a(i, 1)) Then
                If IsError(x) Then
                    a(i, 2) = "N/A"
                    a(r, 3) = a(i, 1): r = r + 1
                    a(i, 2) = ws.Cells(x, 2).Value
                End If
            End If
        Next i
        b = ws.Range("A1").CurrentRegion.Value: r = 2
        For i = LBound(b) + 1 To UBound(b)
            x = Application.Match(b(i, 1), sh.Columns(1), 0)
            If IsError(x) Then
                a(r, 4) = b(i, 1): r = r + 1
            End If
        Next i
        sh.Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    Application.ScreenUpdating = True
End Sub


by Beginner (26 points)
Code is working perfecct !

Thank you somuch for your time & help :)
by Expert (892 points)
You're welcome. Glad it worked fine for you

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.