Return values from column based on condition

[truerip]

This is probably very simple but is giving me a problem.
I want to retrieve values from column B of Sheet2 to column C of Sheet1 while excluding cells that contain certain text (up to 4 different text strings).
Any suggestions would be gratefully appreciated.
Best regards,
truerip

 

[truerip]

Results not as expected.
Results:
$C$2 and empty message box
$C$3 and empty message box
$C$4 and empty message box
$C$5 and empty message box
$C$6 and empty message box
$C$7 and empty message box
$C$8 and empty message box
$C$2 and empty message box
$C$9 and "Standard"

Here is the data on Sheet2 that I'm working with (removed values from previous for testing):

Excel 2013/2016
	A	B
1
2
3
4
5
6
7
8		Standard
9
10		BBB-001-TRE-1
11		BAL-005-0.2b
12		CIP-002-5.1a
13		CIP-003-6
14		CIP-004-6
15		CIP-005-5
16
Sheet2

This is what I end up with on Sheet1:

Excel 2013/2016
	A	B	C
1
2
3
4
5
6
7
8
9			Standard
10
11
12
13
14
Sheet1

 

[bellman101]

Sorry True,

That's my error. Needed a second reference to "Sheet2". Replace the For loop with this:

For Each forCELL In Sheets("Sheet2").Range("B" & varROW & ":B" & Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row).Cells
         If Evaluate("=COUNTIF(Sheet3!A:A," & forCELL.Value & ")") = 0 Then
             Sheets("Sheet1").Range("C" & varDestRow) = forCELL.Value
             varROW = varROW + 1
             varDestRow = varDestRow + 1
         End If
Next

That's why knowing how to hunt errors is as key as knowing how to create them!!

Cheers JB

 

[truerip]

Hello JB,
I am still missing something.
I adjusted the code per you direction in last post leaving the MSgBox's from before.
Results:
$C$2 and "Standard"
$C$3 and empty message box
$C$4 and "BBB-001-TRE-1"
Then I receive a Run-time error '13': Type mismatch.



Data in sheet2 is still as in above post.
Sheet3 is blank (hope to get the exclusions working later)
Sheet1 results before Run-time error:

Excel 2013/2016
	A	B	C	D
1
2			Standard
3
4			BBB-001-TRE-1
5
6
7
Sheet1

Here is the code:

Sub MoveCertainCells()
    
    varROW = 8 'change this number to whatever row your data starts on.
    varDestRow = 2
   
    For Each forCELL In Sheets("Sheet2").Range("B" & varROW & ":B" & Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row).Cells
         If Evaluate("=COUNTIF(Sheet3!A:A," & forCELL.Value & ")") = 0 Then
             Sheets("Sheet1").Range("C" & varDestRow) = forCELL.Value
             MsgBox Sheets("Sheet1").Range("C" & varDestRow).Address
             MsgBox forCELL.Value
             varROW = varROW + 1
             varDestRow = varDestRow + 1
         End If
    Next
End Sub

Thank you for continuing to troubleshoot this.
Best regards,
True

 

[bellman101]

I've just run this exact code in a test book, with some random text in B8, B10, 11, 12, down to about 18. I basically just mashed the keyboard and hit enter a few times. Anyway, it works exactly as it should.

What is in Sheet2 Cell B11?

 

[truerip]

Hello JB,
Sheet2 Cell B11 contains:
BAL-005-0.2b

 

[truerip]

If I do what you did...random text, it works. Weird...

 

[Fluff]

Try changing the Evaluate to this

If Evaluate("=COUNTIF(ToDo!A:A," & forcell.[COLOR=#ff0000]Address [/COLOR]& ")") = 0 Then

 

[truerip]

That got it Fluff! It transfers all of the data from Sheet2 B:B to Sheet1 C:C now!
Now, how can I get it to evaluate and exclude values that match values located in Sheet3 A:A?
For example, if I want to exclude all cells that contain an instance of "BAL", entered into Sheet3 A:A along with other values for exclusion, it should not return BAL-005-0.2b to Sheet1 from Sheet2 but skip to the next cell value and evaluate it and so on.
Thanks!