Insert value in cell if another cell contains specified value

[Mikus]

I need macro that would enter certain text value in specified cells if
certain value is entered in another cell.

For example i have following worksheet

A B C D
1 Biz xxx yyy zzz
2
3

I need macro that would enter "-" in column B and C if value "Priv" is
entered in column A (I select values from dropdown that is specified for
column A).
Macro should trigger when i select value "Priv" from column A cell.

In this case if i would select cell A2 and then choose value "Priv" then
value "-"should automaticaly be inserted in cell B2 and cell C2 and cursor
should stop on cell D2 (the active cell after macro runs should be D column
cell)

This should be applied for all cells in columns A,B,C,D

Any ideas ?

[Eric White]

You could use an IF formula in B and C, e.g.,
=IF($A2="Priv","-",OriginalValueOrFormula), or

Worksheet_Change(ByVal Target As Range)

if Target.Column = 1 And Target.Value = "Priv" Then
Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
Else
'Whatever original value or formulae for B and C
End If

End Sub




"Mikus" wrote:

> I need macro that would enter certain text value in specified cells if
> certain value is entered in another cell.
>
> For example i have following worksheet
>
> A B C D
> 1 Biz xxx yyy zzz
> 2
> 3
>
> I need macro that would enter "-" in column B and C if value "Priv" is
> entered in column A (I select values from dropdown that is specified for
> column A).
> Macro should trigger when i select value "Priv" from column A cell.
>
> In this case if i would select cell A2 and then choose value "Priv" then
> value "-"should automaticaly be inserted in cell B2 and cell C2 and cursor
> should stop on cell D2 (the active cell after macro runs should be D column
> cell)
>
> This should be applied for all cells in columns A,B,C,D
>
> Any ideas ?
>
>
>

[Mikus]

Thanks for your time Eric.

I need macro solution for this. I'cant use IF function .. besides i need to
automaticaly select D column cell if "Priv" is selected in column A.

Your code returns error. When i select "Priv" from column A i get runtime
error '13':
Type mismatch.

Altho error occurs, value "-" is entered in appropriate cells.

You forgot to write sellection of D column cell after "-" is inserted in
columns B and C.

What could cause such error ?
Can you add also the selection of cell in column D ?

"Eric White" wrote:

> You could use an IF formula in B and C, e.g.,
> =IF($A2="Priv","-",OriginalValueOrFormula), or
>
> Worksheet_Change(ByVal Target As Range)
>
> if Target.Column = 1 And Target.Value = "Priv" Then
> Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
> Else
> 'Whatever original value or formulae for B and C
> End If
>
> End Sub
>
>
>
>
> "Mikus" wrote:
>
> > I need macro that would enter certain text value in specified cells if
> > certain value is entered in another cell.
> >
> > For example i have following worksheet
> >
> > A B C D
> > 1 Biz xxx yyy zzz
> > 2
> > 3
> >
> > I need macro that would enter "-" in column B and C if value "Priv" is
> > entered in column A (I select values from dropdown that is specified for
> > column A).
> > Macro should trigger when i select value "Priv" from column A cell.
> >
> > In this case if i would select cell A2 and then choose value "Priv" then
> > value "-"should automaticaly be inserted in cell B2 and cell C2 and cursor
> > should stop on cell D2 (the active cell after macro runs should be D column
> > cell)
> >
> > This should be applied for all cells in columns A,B,C,D
> >
> > Any ideas ?
> >
> >
> >

[Eric White]

My mistake, Mikus. I replied to your question before I'd had that first cup
of coffee. You'll need to bracket my former code in the Worksheet_Change
Event with:

Application.EnableEvents = False

'Code here

Application.EnableEvents = True

I don't understand why, but when the Change event fired, the cells in B and
C were the Target range. So when VBA attempted to evaluate Target.Value =
"Priv" it was seeing two cells with values was and freaking out. Disabling
the events momentarily seemed to keep Target as the cell in column A, and
that seemed to do the trick.


"Mikus" wrote:

> Thanks for your time Eric.
>
> I need macro solution for this. I'cant use IF function .. besides i need to
> automaticaly select D column cell if "Priv" is selected in column A.
>
> Your code returns error. When i select "Priv" from column A i get runtime
> error '13':
> Type mismatch.
>
> Altho error occurs, value "-" is entered in appropriate cells.
>
> You forgot to write sellection of D column cell after "-" is inserted in
> columns B and C.
>
> What could cause such error ?
> Can you add also the selection of cell in column D ?
>
> "Eric White" wrote:
>
> > You could use an IF formula in B and C, e.g.,
> > =IF($A2="Priv","-",OriginalValueOrFormula), or
> >
> > Worksheet_Change(ByVal Target As Range)
> >
> > if Target.Column = 1 And Target.Value = "Priv" Then
> > Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
> > Else
> > 'Whatever original value or formulae for B and C
> > End If
> >
> > End Sub
> >
> >
> >
> >
> > "Mikus" wrote:
> >
> > > I need macro that would enter certain text value in specified cells if
> > > certain value is entered in another cell.
> > >
> > > For example i have following worksheet
> > >
> > > A B C D
> > > 1 Biz xxx yyy zzz
> > > 2
> > > 3
> > >
> > > I need macro that would enter "-" in column B and C if value "Priv" is
> > > entered in column A (I select values from dropdown that is specified for
> > > column A).
> > > Macro should trigger when i select value "Priv" from column A cell.
> > >
> > > In this case if i would select cell A2 and then choose value "Priv" then
> > > value "-"should automaticaly be inserted in cell B2 and cell C2 and cursor
> > > should stop on cell D2 (the active cell after macro runs should be D column
> > > cell)
> > >
> > > This should be applied for all cells in columns A,B,C,D
> > >
> > > Any ideas ?
> > >
> > >
> > >

[Mikus]

This does not work for me. I don't know where the problem is.
I put excel example in http:\\www.svara-kontrole.lv\test.xls

I also need to SELECT cell in column D after "-" is inserted
How do i do that

"Eric White" wrote:

> My mistake, Mikus. I replied to your question before I'd had that first cup
> of coffee. You'll need to bracket my former code in the Worksheet_Change
> Event with:
>
> Application.EnableEvents = False
>
> 'Code here
>
> Application.EnableEvents = True
>
> I don't understand why, but when the Change event fired, the cells in B and
> C were the Target range. So when VBA attempted to evaluate Target.Value =
> "Priv" it was seeing two cells with values was and freaking out. Disabling
> the events momentarily seemed to keep Target as the cell in column A, and
> that seemed to do the trick.
>
>
> "Mikus" wrote:
>
> > Thanks for your time Eric.
> >
> > I need macro solution for this. I'cant use IF function .. besides i need to
> > automaticaly select D column cell if "Priv" is selected in column A.
> >
> > Your code returns error. When i select "Priv" from column A i get runtime
> > error '13':
> > Type mismatch.
> >
> > Altho error occurs, value "-" is entered in appropriate cells.
> >
> > You forgot to write sellection of D column cell after "-" is inserted in
> > columns B and C.
> >
> > What could cause such error ?
> > Can you add also the selection of cell in column D ?
> >
> > "Eric White" wrote:
> >
> > > You could use an IF formula in B and C, e.g.,
> > > =IF($A2="Priv","-",OriginalValueOrFormula), or
> > >
> > > Worksheet_Change(ByVal Target As Range)
> > >
> > > if Target.Column = 1 And Target.Value = "Priv" Then
> > > Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Value = "-"
> > > Else
> > > 'Whatever original value or formulae for B and C
> > > End If
> > >
> > > End Sub
> > >
> > >
> > >
> > >
> > > "Mikus" wrote:
> > >
> > > > I need macro that would enter certain text value in specified cells if
> > > > certain value is entered in another cell.
> > > >
> > > > For example i have following worksheet
> > > >
> > > > A B C D
> > > > 1 Biz xxx yyy zzz
> > > > 2
> > > > 3
> > > >
> > > > I need macro that would enter "-" in column B and C if value "Priv" is
> > > > entered in column A (I select values from dropdown that is specified for
> > > > column A).
> > > > Macro should trigger when i select value "Priv" from column A cell.
> > > >
> > > > In this case if i would select cell A2 and then choose value "Priv" then
> > > > value "-"should automaticaly be inserted in cell B2 and cell C2 and cursor
> > > > should stop on cell D2 (the active cell after macro runs should be D column
> > > > cell)
> > > >
> > > > This should be applied for all cells in columns A,B,C,D
> > > >
> > > > Any ideas ?
> > > >
> > > >
> > > >