CODEWITHSUNDEEP
excelvlookupvba
shaydawn
shaydawn

Reputation: 29

macro for select all in vlookup

I format a large spreadsheet which changes weekly and match it with a data file that also changes weekly. I need a "select all" function in my vlookup, as well as an "auto fill" to copy the formula down to the last row. I've tried "CurrentRegion", "table", & failed miserably with "end" statements. Since I rarely build macros, I don't have a large knowledge of VBA, but do enjoy learning. I've received help on this site before and appreciate the time savings for work-related issues. The codes I started with for this week are below, thank you!

ActiveCell.Select
Sheets("Demo").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'Listing'!R1C1:R12182C4,3,FALSE)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A177688")
ActiveCell.Range("A1:A177688").Select

Upvotes: 1

Views: 607

Answers (1)

Ygor Yansz
Ygor Yansz

Reputation: 176

i've made this macro that can select for you any cell that match a criteria of your desire that would be that select all function on your vlookup and it will also change the value of all selected cells according to your desire, but i need to know more about how the sheets looks like and what you're looking for in order to help you better, here is the code:

Sub selectall()

Dim rng     As String
Dim cname   As String

cname = InputBox("Enter column you wish to run the VLOOKUP", "NAME COLLECTOR")

'Change the "For" range as you desire to match the starting and ending line

For i = 1 To 100

If Range(cname & i) = "Enter what you're looking for" Then

If rng = Empty Then

rng = Range(cname & i).Address

Else:

rng = rng & "," & Range(cname & i).Address

End If

End If

Next

Range(rng).Value = InputBox("Enter the value or formula you want here", "NAME COLLECTOR")

End Sub

Upvotes: 0

Related Questions