Macro to pull data from a search from different spreadsheet & macro adjustment

[Hyflex]

Hi,

I need help adjusting a macro and creating a macro.

The first macro, I am looking for some help creating.

I have two spreadsheets:
Spreadsheet 1: ListValues.xlsm <-- This Spreadsheet is HUGE
Spreadsheet 2: Picker.xlsm <-- This spreadsheet contains the macro

The macro needs to look at Column A in Picker.xlsm and for a text string.
It needs to then take that value and search in Column G in ListValue.xlsm
If it finds two matches I need it to open a message box alerting me that
two matches were found.
When it finds a match I need it to copy certain cells in the row where the match is found.
It then needs to paste these values into spesific into cells on the same row as the source (search value)
Once completed It needs to move onto the next row and do the same thing, but there might be blank/empty rows and if there is one it needs to skip that row.




The following macro I need help making it better/faster.
Basically the point of the macro is to go through all cells in column A, it is supposed to remove one of the three text values from each row (some rows might not have any of them in) and move onto the next row

At the moment, I think my current macro selects all cells in column A with data in and replaces Fresh with nothing/null (basically removes it) and then it will check for Tinned and Finally Pureed
I am wanting to change the macro so that if it finds and removes Fresh, for it to skip checking the other options and go to the next cell.

Sub Task1()

    Application.ScreenUpdating = False
    
    Dim myLastRow As Long
    Dim myLastColumn As Long
    
    Range("A1").Select
        On Error Resume Next
            myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
            myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
            myLastCell = Cells(myLastRow, myLastColumn).Address
            Tomato = "A1:" & myLastCell

    Range(Tomato).Select
    
    For Each cell In Selection
        cell.Value = Replace(cell.Value, "(Fresh)", "", 1, 1, vbTextCompare)
        cell.Value = Replace(cell.Value, "(Tinned)", "", 1, 1, vbTextCompare)
        cell.Value = Replace(cell.Value, "(Pureed)", "", 1, 1, vbTextCompare)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[AlphaFrog]

Lightning fast...

Sub Task1()

    Application.ScreenUpdating = False
    
    Cells.Replace What:="(Fresh)", Replacement:="", LookAt:=xlPart, MatchCase:=False
    Cells.Replace What:="(Tinned)", Replacement:="", LookAt:=xlPart, MatchCase:=False
    Cells.Replace What:="(Pureed)", Replacement:="", LookAt:=xlPart, MatchCase:=False
    
    Application.ScreenUpdating = True
    
End Sub

 

[Hyflex]

Thank you AlphaFrog.

I thought it would be quicker to specify a specific column instead of doing the whole document...

Only thing now then is the macro in which I need to create.

 

[AlphaFrog]

As far as your original "Task1" macro, it was searching\replacing each cell in the whole document and not just column A.

If you want to just search\replace within column A, then try this...

Sub Task1()

    Application.ScreenUpdating = False
    
    With Range("A:A")
        .Replace What:="(Fresh)", Replacement:="", LookAt:=xlPart, MatchCase:=False
        .Replace What:="(Tinned)", Replacement:="", LookAt:=xlPart, MatchCase:=False
        .Replace What:="(Pureed)", Replacement:="", LookAt:=xlPart, MatchCase:=False
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Hyflex]

Thank you once again AlphaFrog.
In my attempt, I was hoping for the macro to:

Find last used row in Column A.
Select Column A (From Row 1 to last used row)
Replace those three spesific words with a blank/nothing
Each line may or may not have any of those three spesific words

I was hoping it would go line by line and if it finds any of those three for it to replace and then don't both checking for other two words in that row/column and go to next cell in column

 

[AlphaFrog]

As far as your "Other" macro, you may be able to do it with just formulas.

This will return a message within a cell if cell A2 has more than one match in ListValues.xlsm "Sheet1" Column G

=IF(A2="","",IF(COUNTIF([ListValues.xlsm]Sheet1!$G:$G,A2)>1,"Duplicates",""))

Say that formula above is in cell B2 for this example. Then you could have another formula in say cell F2 to return the value from ListValues.xlsm Column H of the A2 text match if there are not duplicates.

=IF(OR(A2="", B2="Duplicates"), "", INDEX([ListValues.xlsm]Sheet1!$H:$H, MATCH(A2, [ListValues.xlsm]Sheet1!$G:$G,0)))

TIP: The easiest way to avoid a syntax error in a formula when referencing a range in another workbook, instead of typing in the long reference like [ListValues.xlsm]Sheet1!$H:$H, click on the other workbook, then worksheet, then cell range. Excel will fiill in the proper reference in the formula for you.

 

[Hyflex]

The IF Function makes it lag massively, I've been trying to find functions in VBS to make it in a macro but I'm unable to find anything.