Copy cells from column B if cells in column A matches criteria

[giovi321]

Hello everybody,
I am new to Mr.Excel and to VBA scripting. Thank you in advance for anybody who can help.
I have a table with multiple columns. I would like to copy from that table the column A to a certain range in another sheet if the cells in columns B and C match certain criterias.
Input

A	B	C
x	0,2	11
y	0,4	20
z	3	11

<tbody>
</tbody>

Criterias: B<1 AND C>10

Expected output:

A

x

y

<tbody>
</tbody>

I tried several codes taken mostly from Mr.Excel, but couldn't get any success.

Thank you again to anybody who can help!

 

[igold]

Hi giovi321,

Welcome to the Forum!

Is your source data really a table or is it a range. Also, where (name of sheet and what row and column) would you like the data that meets the criteria copied to...

Additionally, it seems as if your title and your description do not match as far as what column you would like copied...

 

[Rick Rothstein]

Hi giovi321,

Is your source data really a table or is it a range. Also, where (name of sheet and what row and column) would you like the data that meets the criteria copied to...

Additionally, it seems as if your title and your description do not match as far as what column you would like copied...

In addition to the question igold asked above, I have the following question... will any of the values in Column A ever have spaces within them?

 

[giovi321]

Hi everybody, thank you for replying promptly.
The title is incorrect: it should be "copy cells from column A if cells in column be meet criteria", sorry for the mistake.
It is not a table but a range.
The data should be copied to a range like A10:A20 in Sheet xyz (it contains a space).
Values in column A might contain spaces.

 

[Rick Rothstein]

Hi everybody, thank you for replying promptly.
The title is incorrect: it should be "copy cells from column A if cells in column be meet criteria", sorry for the mistake.
It is not a table but a range.
The data should be copied to a range like A10:A20 in Sheet xyz (it contains a space).
Values in column A might contain spaces.

Give this macro a try...

Sub giovi321()
  Dim R As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim Result(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    If Data(R, 2) < 1 And Data(R, 3) > 10 Then
      X = X + 1
      Result(X, 1) = Data(R, 1)
    End If
  Next
  Sheets("Sheet xyz").Range("A10").Resize(UBound(Result)) = Result
End Sub

 

[giovi321]

Rick, thank you very much for your help!
I have modified the macro slightly to match my sheet and it seems to be working perfectly!

 

[giovi321]

Hi Rick,
I have slightly modified my model and now I would like the if statement to be executed on the destination cells. Let me explain better.

Input sheet:

A	B	C	D
x	0,2	11	abc
y	0,4	20	def
z	3	11	abc

<tbody>
</tbody>

Output sheet before macro:

A	B	C
1	abc
0,3	def
1	abc
0,2	ghi

<tbody>
</tbody>

What I would like to achieve is fill the column output sheet column C with values from input sheet column A IF:

• values in output sheet column A<1
• Values in input sheet column D = values in output sheet column B
• Values in input sheet column B <1 [note: those values will change according to the content of output sheet column C, generating potentially a circular reference]

Do you think that this is feasible, considering the circular reference?

Thank you very much!

 

[giovi321]

up

 

[Rick Rothstein]

[*]Values in input sheet column B <1 [note: those values will change according to the content of output sheet column C, generating potentially a circular reference]

I don't understand how there can be a formula in Column C of the output sheet under this condition.

 

[giovi321]

I don't understand how there can be a formula in Column C of the output sheet under this condition.

I will try to explain better: the values of the column B in the sheet named input (what i have called input sheet) vary depending on the values of column C in the sheet named output (indirectly through a index match from another sheet).
This will create a circular reference.
I hope I have explained the situation better now.

Thank you!