Find and paste into a different column based on a table

[Silky0]

I am trying to create a macro that searches a table on the "names" tab in the table called Names. The Names table is a list of company names that should be consolidated (Apple, LLC, Apple, Inc, iPhone, LLC, etc.)

The names are in column A, I want to hardcode the name override in column B, as it's already a slow file. The list goes down to row 1015 at the moment. A recording looks like the below.

Thanks for any help.

Sub Macro2()

Range("B16").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Names,2,FALSE),"""")"
Selection.Copy
Range("A16").Select
Selection.End(xlDown).Select
Range("B1015").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range("B16").Select
Application.CutCopyMode = False
Calculate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
End Sub

 

[Silky0]

bump

 

[Silky0]

bump. thanks in advance for any help cleaning this up.

 

[Joe4]

Welcome to the Forum.

Is your code working now, but the problem is that it is just to slow? Or does it not work properly?

I think it would be very beneficial to us if you could show us a small sample of what your data looks like along with your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Silky0]

Thank you for the response. See below. Adjusted the macro for the sample that I am posting.

To answer your question, the macro does work, but it is slow and not very dynamic - I am using 9 rows here, not the ~200 rows in this example. The rest of the file is already slow, so I am trying to keep this as a macro to not slow down the file even further.

Column C is meant to be a name override for all the name permutations of a company name, with the ability for manual input as some names will not be in the table. I have had to adjust column D formula from isblank() but would prefer to avoid that, if possible.

Sub Macro1()
Range("C6").Select
ActiveCell.FormulaR1C1 = "=+IFERROR(VLOOKUP(RC[-1],Names,2,FALSE),"""")"
Selection.Copy
Range("B6").Select
Selection.End(xlDown).Select
Range("C231").Select
Range(Selection, Selection.End(xlUp)).Select
Range("C6:C231").Select
Range("C231").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Book1.xlsm
	B	C	D	E	F	G	H
3		Override Count
4		7
5	Company Name	Name Override	Live Name			Alternative Names	Company Name
6	Apple	Apple	Apple			Apple	Apple
7	Apples	Apple	Apple			Apples	Apple
8	Apple LLC	Apple	Apple			Apple LLC	Apple
9	Banana	Banana	Banana			Banana	Banana
10	Bananas	Banana	Banana			Bananas	Banana
11	Banana LLC	Banana	Banana			Banana LLC	Banana
12	Cherry Investment		Cherry Investment
13	Blackberry		Blackberry
14	Blue-berry LLC	Blueberry	Blueberry
Data

Cell Formulas
Range	Formula
C4	C4	=+COUNTA(C6:C14)-COUNTBLANK(C6:C14)
D6:D14	D6	=IF(C6="",B6,C6)

 

[Joe4]

OK, I think I see what you are doing.

This should be a lot faster:

Sub Macro1()

    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate column C with formulas
    Range("C6:C" & lr).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Names,2,FALSE),"""")"
    
'   Change formulas to hard-coded values
    Range("C6:C" & lr).Value = Range("C6:C" & lr).Value

    Application.ScreenUpdating = True
    
End Sub

 

[Silky0]

Thank you for the help