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

pull values and check in array if not existed

0

Hello

in  this  code  I  use  array  becuase my  real  data  are  big  . my  goal  is  pulling  values  from sheet1  to  sheet RES  based  on  match  column B  and  if  there  is  new  item  in sheet 1  but  is not  existed in sheet RES   then  should  add  the  whole  item   to  sheet RES .

Sub add()
Dim i&, j&, s1, res
With Sheets("Sheet1")
    s1 = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
End With

With Sheets("RES")
    res = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(res)
    For j = 1 To UBound(s1)
        If res(i, 1) = s1(j, 1) Then
            res(i, 2) = s1(j, 2)
            res(i, 3) = s1(i, 3)
            Exit For
        End If
    Next

Next
.Range("C2:D1000000").ClearContents
.Range("B2").Resize(UBound(res), UBound(res, 2)).Value = res
End With
End Sub

so  far   doesn't  seem to pull values  correctly   and  not  add  new  items .

I  hope  somebody  help  me  for  this  project .

Answer
Discuss

Answers

0
Selected Answer

Hasson

Your current macro doesn't correctly "pull  values  from sheet1" where they exist in sheet RES  since your last write to the array reads:

res(i, 3) = s1(i, 3)

should read:

res(i, 3) = s1(j, 3)

Also it makes no attempt to write any Items which don't already exist.

In the revised file attached, I've looped through your array s1 (from Sheet1) first, assuming there will be the same or more items in that sheet than RES. I then loop though the array of RES items to check is they exist there (and adding them if not). I've added some comments (and bits in bold) to help you:

Sub add()
Dim i&, j&, s1(), res()
Dim Found As Boolean

' copy new data to array s1
With Sheets("Sheet1")
    s1 = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
End With

With Sheets("RES")
    ReDim res(1 To .Cells(Rows.Count, "B").End(xlUp).Row, 1 To 3)
    ' copy existing data to array res
    res = .Range("B2:D" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
    ' loop through s1 ###
    For i = 1 To UBound(s1)
        ' reset flag
        Found = False
        ' check against res ###
        For j = 1 To UBound(res)
            ' if Item matches, copy new Import/Export values to array res
            If res(j, 1) = s1(i, 1) Then
                res(j, 2) = s1(i, 2)
                res(j, 3) = s1(i, 3)
                ' flag the match
                Found = True
                Exit For
            End If
        Next j
        ' if doesn't exist in res...
        If Found = False Then
            ' redimension array to increase by 1
            res = WorksheetFunction.Transpose(res)
            ReDim Preserve res(1 To UBound(res, 1), 1 To UBound(res, 2) + 1)
            res = WorksheetFunction.Transpose(res)
            ' write value to new array element
            res(UBound(res), 1) = s1(i, 1)
            res(UBound(res), 2) = s1(i, 2)
            res(UBound(res), 3) = s1(i, 3)
        End If
    Next i
    .Range("B2:D1000000").ClearContents
    'copy revised res to sheet
    .Range("B2").Resize(UBound(res), UBound(res, 2)).Value = res
End With
' tell user is more items in s1 than res
If UBound(res) <> UBound(s1) Then
    j = UBound(s1) - UBound(res)
    MsgBox 2 * j & " duplicate Items in new data (last values copied to RES)"
End If

End Sub

The new items are added without borders or numbers in column A but that makes them easy to see.

Hope this fixes your problem.

Discuss

Discussion

Hi John,
The new items are added without borders or numbers in column A
this  is  minor and  not  problem.
thanks  very  much  for  correction and  addition some  lines   makes  the  code  works  greatly ! 
Hasson (rep: 26) Nov 7, '22 at 1:55 pm
Glad it worked well for you. Thanks for selecting my answer, Hasson. 
John_Ru (rep: 4312) Nov 7, '22 at 2:07 pm
Add to Discussion


Answer the Question

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