Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

error mismatch when fill missed items between two sheets

0

Hello 

I  try  matching   sheet report with  sheet  stock  based on column B . if   the  items  in columns  C for sheet  report is  matched with  sheet STOCK for  column C ,    then  will  fill the  items  in colulm B for  sheet  report , and  if  it's  not  matched  will  keep  empty  some  items  are  not  the  same  thing .

in  reality  I use dictionary   for  make  fast  for  simple  data .

I  got  error  mismatch  in this  line .

a(i, 2) = w(2)

here  is  the  whole  code 

Sub test1()
    Dim a, i As Long, ii As Long, ws As Worksheet, w, dic As Object
    Set ws = Sheets("report")
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("STOCK").Cells(1).CurrentRegion
        a = .Value
        For i = 2 To UBound(a, 1)
            dic(a(i, 3)) = Application.Index(a, i, 0)
        Next
        With ws
            
                With ws.Cells(1).CurrentRegion
                    a = .Value
                    For i = 2 To UBound(a, 1)
                       
                            For ii = 2 To 3
                               
                            Next
                       
                            w = dic(a(i, 3))
                        
                        If a(i, 2) = "" Then a(i, 2) = w(2)
                        dic(a(i, 3)) = w
                    Next
                    .Value = a
                    
                End With
           
        
        End With
        
    End With
    End Sub

thanks

Answer
Discuss

Discussion

Hi Leopard

Don't think I'll have time to look at this for a few days.

You don't label your Next returns (like Next n) so perhaps don't realise that this is  loop which does nothing useful:
For ii = 2 To 3 
                               
Next

It just loops twice then carries on with the loop starting :
For i = 2 To UBound(a, 1)


What had you meant to do there?
John_Ru (rep: 6102) Nov 10, '22 at 12:41 pm
hi John,
Don't think I'll have time to look at this for a few days.
understood.
as  to  question   I  use  looping  within  sheet stock  and  loop within  sheet  report 
leopard (rep: 88) Nov 10, '22 at 2:12 pm
Leopard. 

You say "... I  use  looping  within  sheet stock  and  loop within  sheet  report" but that particular loop sets ii to 2, reaches the Next line, loops back and set ii to 3 then drops to the Next. It sets ii to 4 loops back and falls out of the loop. It actually does nothing!
John_Ru (rep: 6102) Nov 10, '22 at 2:45 pm
yes  john  after  check  the  code  again  this  loop  is  useless.
but  I'm stiil have new  error subscript  out  of  range
w = dic(a(i, 3))

 
leopard (rep: 88) Nov 10, '22 at 3:09 pm
Leopard

I've now added an Answer. Not sure why you spell "Thailand" as Thiland"- I have not corrected it in your workbook.
John_Ru (rep: 6102) Nov 15, '22 at 5:43 am
Add to Discussion

Answers

0
Selected Answer

Leopard

In the attached file, the modified code below differentiates between items on the "report" sheet which:

  1. exist on the "stock" sheet, (the BSJ*** * Code is written to column B)- I've made B2:B3 and B9:B10 like this.
  2. do NOT exist on the "stock" sheet, like B49 to B52 (column B becomes "NOT FOUND")

It does that by checking if a dictionary item exists (from  the "stock" sheet). Main changes are in bold (plus I deleted the loop mentioned in the Discussion under your Question):

Sub test1()
    Dim a, i As Long, ws As Worksheet, w, dic As Object

    Set ws = Sheets("report")
    Set dic = CreateObject("Scripting.Dictionary")

    ' collect data into dictionary
    With Sheets("STOCK").Cells(1).CurrentRegion
        a = .Value
        For i = 2 To UBound(a, 1)
            dic(a(i, 3)) = Application.Index(a, i, 0)
        Next i
    End With

    With ws.Cells(1).CurrentRegion
        a = .Value
        For i = 2 To UBound(a, 1)
            ' check Brand is on Stock sheet
            If dic.exists(a(i, 3)) Then
                w = dic(a(i, 3))
                ' write Code from dictionary
                If Not (a(i, 2)) > "" Then a(i, 2) = w(2)

                ' if Brand is NOT on Stock sheet
                Else
                a(i, 2) = "NOT FOUND"
            End If
        Next i

        'write back to report sheet
        .Value = a

    End With

End Sub

Hope this works for you (I leave you to modify the code to write Balance values to the report sheet and/or replace the "NOT FOUND" action).

Discuss

Discussion

Hi John ,
good  work !
just  question  for  this  line 
If Not (a(i, 2)) > ""

I see this procedure is unusual , why you don't use ?
if a(i,2)=""

anyway  thanks  very  much  for  your  assistance .
leopard (rep: 88) Nov 15, '22 at 8:22 am
Thanks for selecting my answer, Leopard. There's no good reason for that Not test, sorry! (I think it was from when I first compared to vbNullString) 
John_Ru (rep: 6102) Nov 15, '22 at 9:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login