Need help with VBA code please.... I have 2 Excel workbooks that will interact with a 3rd workbook. They search for matching numbers in a cell in the 3rd workbook and copy text to a different cell if a match is found.
What I need help with is to not update the text in the cell if specific text already exists. For instance if the cell in column F is "expeditor" then do nothing and check for the next match.
Here is my code below:
What I need help with is to not update the text in the cell if specific text already exists. For instance if the cell in column F is "expeditor" then do nothing and check for the next match.
Here is my code below:
VBA Code:
Sub Refresh_Click()
Dim Cl As Range
Dim Dic As Object
Application.ScreenUpdating = False
Set Dic = CreateObject("scripting.dictionary")
'Workbook 2 Cell S2 is search criteria and next column is copy value
With Sheets("Sheet1")
For Each Cl In .Range("S2", .Range("S" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Cl.Offset(, 1).Value
Next Cl
End With
'Searches S2 value within Workbook 3 Column B and paste text in column F
With Workbooks.Open("C:\Location\filename.xlsx").Sheets("Sheet1")
For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
If Dic.exists(Cl.Value) Then Cl.Offset(, 4).Value = Dic(Cl.Value)
Next Cl
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub