Skipping cells with specific text in formula

arcaidius

New Member
Joined
Dec 4, 2018
Messages
42
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:

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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

arcaidius

New Member
Joined
Dec 4, 2018
Messages
42
Alpha Frog,
I tried your code and it still changes the text. I replaced this section of code with yours, did i put it in the right place?

VBA Code:
If Dic.exists(Cl.Value) Then Cl.Offset(, 4).Value = Dic(Cl.Value)
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
You put it in the right place.

I don't know what to tell you. If column F equals expeditor, then column F doesn't update. Is that what you want?
 

arcaidius

New Member
Joined
Dec 4, 2018
Messages
42

ADVERTISEMENT

You put it in the right place.

I don't know what to tell you. If column F equals expeditor, then column F doesn't update. Is that what you want?
Yes Sir
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Try this to remove a trailing space character.
Rich (BB code):
If Dic.Exists(Cl.Value) And Trim(LCase(Cl.Offset(,4))) <> "expeditor" Then Cl.Offset(, 4).Value = Dic(Cl.Value)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top