Answered Mar 23, 2018 · 0 votes
I hope this isnt too much for you. Feel free to ask away
One method could be to create a dictionary of the sheet1 values
Dim YourDict As ObjectSet YourDict = CreateObject("Scripting.Dictionary")For i = firstrow To lastrowOn Error Resume NextYourDict.Add datasheet.Cells(i, YourColumn).Value2, _datasheet.Cells(i,YourColumn).Value2Next i
note that datasheet, firstrow, lastrow, and yourcolumn are all variables you have to assign. This dict, with the "On Error Resume Next" will skip over any duplicate entries
Then you could loop through the dictionary like so:
For Each entry In YourDict (do stuff) Next entry-
It is not clear what kind of condition you want to copy vs. not copy, but it could be something like an if statement
I should probably share my IsInArry function here
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)End Function-
You can assign ranges to arrays, and then I use this function to see if the range has the value I am looking for.
However you will need to convert a variant array into a string array, which is done as follows
ReDim YourStringArray(YourVariantArray.Count - 1) As String j = 0 For Each itm In YourVariantArray.keys() YourStringArray(j) = YourStringArray(itm) j = j + 1 Next itm
Also, here is how I get the last row and column (and first row)
Set datasheet = ActiveSheet ' Or the name of your sheet'this column always has data in every rowSiteCodeCOL = datasheet.Cells.Find("Site Code").ColumnWith datasheetlastrow = .Cells(.Rows.Count, SiteCodeCOL).End(xlUp).Rowfirstrow = .Cells(.Rows.Count, SiteCodeCOL).End(xlUp).End(xlUp).Rowlastcol = .Cells(firstrow, .Columns.Count).End(xlToLeft).Columnend with
'---------------------------------------------------------
Dim YourDict As Object, sheet1 as Worksheet, sheet2 as WorksheetDim sheet1lastrow As Integer, sheet1firstrow as IntegerDim sheet2firstrow As Integer, sheet2lastrow as IntegerDim sheet1lastcol As Integer, sheet2lastcol as IntegerDim DirArray As Variant 'if you wanted to use an array, like I mentioned above: DirArray = Range("A" & i & ":C" & i).Value2Set YourDict = CreateObject("Scripting.Dictionary")Set sheet1 = Worksheets("Sheet1") ' may need to include Set sheet2 = Worksheets("Sheet2")With sheet2.Sort .SetRange Range("A5:Q21") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .ApplyEnd WithWith sheet1sheet1lastrow = .Cells(.Rows.Count, 4).End(xlUp).Row ' "d" = 4sheet1firstrow = .Cells(.Rows.Count, 4).End(xlUp).End(xlUp).Row + 2sheet1lastcol = .Cells(sheet1firstrow, .Columns.Count).End(xlToLeft).ColumnEnd Withon error resume nextFor i = sheet1firstrow To sheet1lastrowYourDict.Add sheet1.cells(i, 1).value2, sheet1.cells(i, 1).value2 ' you are adding a key value pair here YourDict.Add sheet1.cells(i, 2).value2, sheet1.cells(i, 2).value2 '(i,2) means the B column, ith row, if i = 4 it means B4YourDict.Add sheet1.cells(i, 3).value2, sheet1.cells(i, 3).value2 'value2 is the fastest method, it represents the true value of the cellnext ii = sheet2firstrowFor Each entry In YourDictYourDict.Add sheet2.Range("D" & i).Value2, CStr(sheet2.Range("D" & i).Value2)If Err.Number <> 0 Then 'adding a key already in the dictionary will throw an error' copy the data heresheet2.range(cells(i,4),cells(i,6)).copy sheet1.cells(i,4)Err.Cleari = i + 1Next entryon error goto 0
