Macro for VLookup of number to assign name

**andrew93hughes** · 09-12-2013, 10:08 AM

Hi guys,

I have 2 sheets on this workbook, Final Results and Count.

I need to lookup the FLOC number in Final Results! Column A, Check it against the FLOC numbers in Count! Column A, and assign Count! Column C to the match.

I know how to do this with a VLookup manual, but when I try to automate it, it never works.

Little help?

**judgeh59** · 09-12-2013, 10:25 AM

so this was me just turning on the macro recorder to see how to program what I want....this is a great technique to learn stuff, BUT, it doesn't make it very dynamic....so you'll have to figure last rows so if you change the size of the data set code will work...

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],COUNT!R1C1:R5612C3,3,0)"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B223")
    Range("B4:B223").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

**AB33** · 09-12-2013, 10:35 AM

Formula in B2 and drag it down.

=IFERROR(INDEX('FINAL RESULTS'!$C:$C,MATCH(A2,'FINAL RESULTS'!$A:$A,0)),0)

**andrew93hughes** · 09-12-2013, 10:58 AM

I need it to be fully automated as the sheet itself is generated as part of a macro.

**judgeh59** · 09-12-2013, 10:46 AM

@AB33 - I believe he is using Excel 2000 and I don't think IFERROR was available then....I did the same though and had to remove it....

**andrew93hughes** · 09-12-2013, 10:58 AM

judgeh59, i'm trying your one out now, wish me luck!

**AB33** · 09-12-2013, 10:56 AM

Judgeh59,
Thank you!
Yes, only available in 2007 and beyond, but there is an equivalent function called ISNA

**judgeh59** · 09-12-2013, 10:58 AM

AB33 - also a ISERROR back in 2000 if my memory is correct....

**judgeh59** · 09-12-2013, 10:59 AM

@andrew - good luck

**andrew93hughes** · 09-12-2013, 11:10 AM

judgeh, having a few problems! The paste at the end is the problem, i'm trying to make it general, i.e. able to assign for any data input not the specific one listed; you've given me a great start, any ideas for next step?

**judgeh59** · 09-12-2013, 11:15 AM

well all the copy/special paste is doing is removing the formula....you can delete the last 3 lines of code and the formula will stay intact...I'm not sure what you are trying to say in the I.E. section....sorry

**andrew93hughes** · 09-12-2013, 11:31 AM

Right, all i need is for the macro to paste into Column J instead of Column B, any ideas?

FYI, I suck at VBA, I may be asking silly questions, it's a distinct possiblity that I am now!

**judgeh59** · 09-12-2013, 11:36 AM

not a problem - we probably all sucked at VBA at some point in time --- try this

    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-9],COUNT!R1C1:R5612C3,3,0)"
    Range("J4").Select
    Selection.AutoFill Destination:=Range("J4:J223")

Macro for VLookup of number to assign name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Re: Macro for VLookup of number to assign name

Thread Information

Users Browsing this Thread

Similar Threads

Macro to count consecutive blank cells and assign a number

Assign a macro to a numpad number

Creating macro to assign the next number in a series from a separate worksheet

Find a number in a column then assign another cell number for the output number

Assign one number to a number range

Bookmarks

Bookmarks

Posting Permissions