Macro Correction

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Macro Correction

Post by zyxw1234 »

Code: Select all

Sub STEP5()
    Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
    Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\ap.xls")
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    Set desWS = wb1.Worksheets.Item(1)
    Set srcWS = wb2.Worksheets.Item(1)
    v1 = desWS.Range("B2", desWS.Range("B" & desWS.Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value
    Set rngList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1)
        If Not rngList.exists(Val) Then
            rngList.Add Key:=Val, Item:=i + 1
        End If
    Next i
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1)
        If rngList.exists(Val) Then
            desWS.Cells(rngList(Val), 26) = v2(i, 2)
        End If
    Next i
    Application.ScreenUpdating = True

wb1.Save
wb2.Save
wb1.Close
wb2.Close

End Sub




This is the code but i am not getting any output
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

The first loop fills the dictionary with the unique values of column B of ap-Sheet1. There is only one such value: WC5758.

The second loop checks whether this value occurs in column A of Sheet1 in the other workbook. It doesn't, so nothing happens.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Yes HansV Sir but i don't know what to do now
So looking for help regarding this

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

Can you explain what the code is supposed to do?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

if condition met it should paste the data in column 26

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

It will do that, but since the condition isn't met in the sample workbooks, there is nothing to paste.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Thnx Alot HansV Sir
I got ur point
Have a Great Day
Problem Solved