Copy cell value based on another cell value criteria

[nuficek]

Hi,

could anybody help me please with VBA code to copy value based on another cell value. I have a table where I put data in the column "E". If the value in the cell "EZ" is the same as value in the "B1" I need to copy value from "C1" to "FZ". I need to run the code anytime when the value is inserted and criteria met. Any help would be appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]XXX[/TD]
[TD]123[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GHI[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JKL[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XXX[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MNO[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PQR[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STU[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VWZ[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

You said:

If the value in the cell "EZ" is the same as value in the "B1" I need to copy value from "C1" to "FZ"

There is no cell "EZ" Or "FZ"

We have cells Like "E14" or "F22"

We Have a column EZ but not a cell EZ

The letter is the column and the number is the row.
​

 

[nuficek]

I apologize not to be so specific. I thought it would be easier this way. So here is the PrtScn of the table. I know I can use formula[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]. But the problem is that the user will input data manually and he would overwrite the formula.

[/URL]
roll a dice online free
[/IMG]<strike></strike>
[/FONT]

 

[My Aswer Is This]

You said:

I need to copy value from "C1" to "FZ"

In your screen shot I see nothing in column C

You need to re explain your question and Not use EZ and FZ

I see nothing in this screen shot about FZ
​

 

[nuficek]

Sorry, it was 3am and I was too tired to think constructive. Yeah you are right, it's confusing. So let's start again, please:
I need to fill data to column D and E. If cell data in column D meets criteria in column A I need to copy data from column B to column E. In this example D6=A1 so I need copy data B1 to E6. And

D9=A3 so I need copy data B3 to E9. I know I can use easy formula for this but the problem is that I need to fill column E data manually so I would overwrite all formulas. That's why I'm asking for help with VBA code.



​

 

[My Aswer Is This]

I still do not understand. You said:

I need to​

fill data to column D ​

and E. If ​

cell data in column D​

meets criteria in column A I need to copy data from column B to column E. In this example D

So fill in column D if it meets criteria in column D????
​

 

[nuficek]

Normally I need to fill data to column D by hand or by drop down menu cell by cell and also to column E. And in case that the required criteria is met (in this case if the value in the column D is equal to value in the column A) I need to copy the value next to cell in the column B to column E. For example simple formula IF(D9=A1,E9=B1,""). But this should be like a form. So as soon as I fill data in D1:E14 and export them the table will be erased and ready for new input (this is how it is intended to use) and A1:B3 is fixed table. And the problem is that I fill also data to column E so there can't be any formula otherwise I will overwrite it.

 

[nuficek]

The idea is to have VBA code which will check condition on the background in the range D1:D14 as I write. If the condition is met the apropriate value from column B will be copied to apropriate cell in the column E.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Res As Variant
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
      Res = Evaluate("INDEX(B:B,MATCH(" & Target.Address & ",A:A,0))")
      If Not IsError(Res) Then Target.Offset(, 1) = Res
   End If
End Sub

 

[nuficek]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Res As Variant
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
      Res = Evaluate("INDEX(B:B,MATCH(" & Target.Address & ",A:A,0))")
      If Not IsError(Res) Then Target.Offset(, 1) = Res
   End If
End Sub

Fluff, thanks a lot. That's exactly what I was looking for. Working like a charm.