VBA Lookup/Filter

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
656
Looking to interpret how exactly this works? I love using it, but i really don't know how it works and how i would manipulate it to work in other scenarios?


Set ws3= ThisWorkbook.Worksheets("Sheet3")
Set ws1 = ThisWorkbook.Worksheets("Sheet1")


Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant


With ws1
lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
inarr = Range(.Cells(3, 1), .Cells(lastrow, 18))
End With


With ws3
lastrow2 = .Cells(Rows.Count, "G").End(xlUp).Row
searcharr = Range(.Cells(3, 7), .Cells(lastrow2, 7))
outarr = Range(.Cells(3, 1), .Cells(lastrow2, 18))
End With


On Error Resume Next
For i = 3 To lastrow2
For j = 3 To lastrow
Searchfor = searcharr(i, 1)
If inarr(j, 1) = Searchfor Then
For kk = 1 To 18
outarr(i, kk - 1) = inarr(j, kk)
Next kk
Exit For
End If
Next j
Next i


With ws1
Range(.Cells(3, 1), .Cells(lastrow2, 18)) = outarr
End With
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I can't modify this to work. I have a sheet1 with data columns A:J. I'm advance filtering to take out the duplicate values in column G and pasting the values on sheet3 column G. I'm then trying to fill in the data for the rest of the columns.
 
Upvote 0
i can't modify this to work. I have a sheet1 with data columns a:j. I'm advance filtering to take out the duplicate values in column g and pasting the values on sheet3 column g. I'm then trying to fill in the data for the rest of the columns.

thank you thank you thank you!!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top