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 (916 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 (916 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 (916 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 (916 points)
You're welcome. Glad it worked fine for you

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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