Based on value in C15 I want to have a text statement added to cell B21. I
would like this to be put at the end of whatever is already in B21 - not
just replace the cell content.

Need help with the code - and where it would go.

Thanks

You posted in the Excel Programming section, but I don't think you need a
macro to do what you described. Put this function in an appropriate cell:
=IF(C15>15,B21&C21,"")

Regards,
Ryan---

--
RyGuy


"HH" wrote:

> Based on value in C15 I want to have a text statement added to cell B21. I
> would like this to be put at the end of whatever is already in B21 - not
> just replace the cell content.
>
> Need help with the code - and where it would go.
>
> Thanks
>
>
>
>

Let say tht B21 originally contained:
=B19+B20
change this to:
=IF(C15=1,(B19+B20) & " and C15 has a one",B19+B20)
--
Gary''s Student - gsnu200782


"HH" wrote:

> Based on value in C15 I want to have a text statement added to cell B21. I
> would like this to be put at the end of whatever is already in B21 - not
> just replace the cell content.
>
> Need help with the code - and where it would go.
>
> Thanks
>
>
>
>

Maybe I was not clear enough - or maybe I don't understand. I'll try
again..

If I put a value less than 1980 in cell C17, I would like a text statement
added to cell C21. This added text statement would say "Construction
material may contain lead." There may already be a text statement in C21 so
the new statement would be added at the end of whatever is already in the
cell.
What I have come up with is: =If (C17>1980,C21="Construction material may
contain lead.","") I think this would delete whatever is already in C21
and replace the Consturction material...statement. But even if it would
work - I don't know where to add the =if statement.
Thanks

"HH" <(E-Mail Removed)> wrote in message
news:rx2Sj.1452$_.(E-Mail Removed)...
> Based on value in C15 I want to have a text statement added to cell B21.
> I would like this to be put at the end of whatever is already in B21 - not
> just replace the cell content.
>
> Need help with the code - and where it would go.
>
> Thanks
>
>
>

If you don't mind updating the whole worksheet as a batch, you can create a
button and then use something similar to the following code to loop thru all
cells:

Private Sub CommandButton1_Click()
Dim oRange As Range
Dim sTest As String
Dim iCount As Integer
Dim i As Integer

With Sheets("Sheet1")
Debug.Print .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Range("A" & i).Value <> "" Then
If .Range("A" & i).Value < 1870 Then
.Range("B" & i).Value = .Range("B" & i).Value & " - may contain
lead"
End If
End If
Next
End With
End Sub

You may have to change the columns above as I am just using A & B for example.



"HH" wrote:

> Maybe I was not clear enough - or maybe I don't understand. I'll try
> again..
>
> If I put a value less than 1980 in cell C17, I would like a text statement
> added to cell C21. This added text statement would say "Construction
> material may contain lead." There may already be a text statement in C21 so
> the new statement would be added at the end of whatever is already in the
> cell.
> What I have come up with is: =If (C17>1980,C21="Construction material may
> contain lead.","") I think this would delete whatever is already in C21
> and replace the Consturction material...statement. But even if it would
> work - I don't know where to add the =if statement.
> Thanks
>
> "HH" <(E-Mail Removed)> wrote in message
> news:rx2Sj.1452$_.(E-Mail Removed)...
> > Based on value in C15 I want to have a text statement added to cell B21.
> > I would like this to be put at the end of whatever is already in B21 - not
> > just replace the cell content.
> >
> > Need help with the code - and where it would go.
> >
> > Thanks
> >
> >
> >
>
>
>

What is in C21 now? By that I mean, does it contain a formula which displays
text (if so, tell us the formula) or does it contain text typed in by the
user?

Rick


"HH" <(E-Mail Removed)> wrote in message
news:2j9Sj.1669$_.(E-Mail Removed)...
> Maybe I was not clear enough - or maybe I don't understand. I'll try
> again..
>
> If I put a value less than 1980 in cell C17, I would like a text
> statement added to cell C21. This added text statement would say
> "Construction material may contain lead." There may already be a text
> statement in C21 so the new statement would be added at the end of
> whatever is already in the cell.
> What I have come up with is: =If (C17>1980,C21="Construction material may
> contain lead.","") I think this would delete whatever is already in C21
> and replace the Consturction material...statement. But even if it would
> work - I don't know where to add the =if statement.
> Thanks
>
> "HH" <(E-Mail Removed)> wrote in message
> news:rx2Sj.1452$_.(E-Mail Removed)...
>> Based on value in C15 I want to have a text statement added to cell B21.
>> I would like this to be put at the end of whatever is already in B21 -
>> not just replace the cell content.
>>
>> Need help with the code - and where it would go.
>>
>> Thanks
>>
>>
>>
>
>

Rick,
C21 is a general text cell where the user can type comments. There is no
formula in this cell now.

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> What is in C21 now? By that I mean, does it contain a formula which
> displays text (if so, tell us the formula) or does it contain text typed
> in by the user?
>
> Rick
>
>
> "HH" <(E-Mail Removed)> wrote in message
> news:2j9Sj.1669$_.(E-Mail Removed)...
>> Maybe I was not clear enough - or maybe I don't understand. I'll try
>> again..
>>
>> If I put a value less than 1980 in cell C17, I would like a text
>> statement added to cell C21. This added text statement would say
>> "Construction material may contain lead." There may already be a text
>> statement in C21 so the new statement would be added at the end of
>> whatever is already in the cell.
>> What I have come up with is: =If (C17>1980,C21="Construction material may
>> contain lead.","") I think this would delete whatever is already in
>> C21 and replace the Consturction material...statement. But even if it
>> would work - I don't know where to add the =if statement.
>> Thanks
>>
>> "HH" <(E-Mail Removed)> wrote in message
>> news:rx2Sj.1452$_.(E-Mail Removed)...
>>> Based on value in C15 I want to have a text statement added to cell
>>> B21. I would like this to be put at the end of whatever is already in
>>> B21 - not just replace the cell content.
>>>
>>> Need help with the code - and where it would go.
>>>
>>> Thanks
>>>
>>>
>>>
>>
>>
>

If the user can type into the cell, then you cannot put a formula in the
cell also (it will be overwritten by the user's entry). The only way to do
what you want is through an event procedure. In order to give you the code
you will need, we need some more information. Are you interested in cells
C17 and C21 only? Or is this a functionality you need across multiple
columns? If multiple columns, which ones (start column, end column)? Always
rows 17 and 21, or do other rows need to react to the value typed into C17?

Rick


"HH" <(E-Mail Removed)> wrote in message
news:kGgSj.45664$%(E-Mail Removed)...
> Rick,
> C21 is a general text cell where the user can type comments. There is no
> formula in this cell now.
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:%(E-Mail Removed)...
>> What is in C21 now? By that I mean, does it contain a formula which
>> displays text (if so, tell us the formula) or does it contain text typed
>> in by the user?
>>
>> Rick
>>
>>
>> "HH" <(E-Mail Removed)> wrote in message
>> news:2j9Sj.1669$_.(E-Mail Removed)...
>>> Maybe I was not clear enough - or maybe I don't understand. I'll try
>>> again..
>>>
>>> If I put a value less than 1980 in cell C17, I would like a text
>>> statement added to cell C21. This added text statement would say
>>> "Construction material may contain lead." There may already be a text
>>> statement in C21 so the new statement would be added at the end of
>>> whatever is already in the cell.
>>> What I have come up with is: =If (C17>1980,C21="Construction material
>>> may contain lead.","") I think this would delete whatever is already
>>> in C21 and replace the Consturction material...statement. But even if
>>> it would work - I don't know where to add the =if statement.
>>> Thanks
>>>
>>> "HH" <(E-Mail Removed)> wrote in message
>>> news:rx2Sj.1452$_.(E-Mail Removed)...
>>>> Based on value in C15 I want to have a text statement added to cell
>>>> B21. I would like this to be put at the end of whatever is already in
>>>> B21 - not just replace the cell content.
>>>>
>>>> Need help with the code - and where it would go.
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

Rick,
Yes, C17 and C21 are the only cells involved. What I want to do is add the
staement "Construction material may contain lead." to C21 if a year less
than 1980 is entered in C17.
You are right, there may be other text already in the cell when the
statement is to be added. Also there may be text added to C21 after the
statement is added.
"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> If the user can type into the cell, then you cannot put a formula in the
> cell also (it will be overwritten by the user's entry). The only way to do
> what you want is through an event procedure. In order to give you the code
> you will need, we need some more information. Are you interested in cells
> C17 and C21 only? Or is this a functionality you need across multiple
> columns? If multiple columns, which ones (start column, end column)?
> Always rows 17 and 21, or do other rows need to react to the value typed
> into C17?
>
> Rick
>
>
> "HH" <(E-Mail Removed)> wrote in message
> news:kGgSj.45664$%(E-Mail Removed)...
>> Rick,
>> C21 is a general text cell where the user can type comments. There is
>> no formula in this cell now.
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>> in message news:%(E-Mail Removed)...
>>> What is in C21 now? By that I mean, does it contain a formula which
>>> displays text (if so, tell us the formula) or does it contain text typed
>>> in by the user?
>>>
>>> Rick
>>>
>>>
>>> "HH" <(E-Mail Removed)> wrote in message
>>> news:2j9Sj.1669$_.(E-Mail Removed)...
>>>> Maybe I was not clear enough - or maybe I don't understand. I'll try
>>>> again..
>>>>
>>>> If I put a value less than 1980 in cell C17, I would like a text
>>>> statement added to cell C21. This added text statement would say
>>>> "Construction material may contain lead." There may already be a text
>>>> statement in C21 so the new statement would be added at the end of
>>>> whatever is already in the cell.
>>>> What I have come up with is: =If (C17>1980,C21="Construction material
>>>> may contain lead.","") I think this would delete whatever is already
>>>> in C21 and replace the Consturction material...statement. But even if
>>>> it would work - I don't know where to add the =if statement.
>>>> Thanks
>>>>
>>>> "HH" <(E-Mail Removed)> wrote in message
>>>> news:rx2Sj.1452$_.(E-Mail Removed)...
>>>>> Based on value in C15 I want to have a text statement added to cell
>>>>> B21. I would like this to be put at the end of whatever is already in
>>>>> B21 - not just replace the cell content.
>>>>>
>>>>> Need help with the code - and where it would go.
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>

Give this a try and see if it does what you want. Click the tab at the
bottom of the worksheet you want this functionality on and select "View
Code" from the popup menu that appears; then Copy/Paste the code below into
the code window that appeared when you did that. Now, go back to the
worksheet and enter different combinations of dates in C17 and text in C21
to see if the code is doing what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Const Warning As String = " (Construction material may contain lead.)"
If Target.Address = "$C$21" And Len(Target.Value) > 0 Then
If Range("C17").Value < 1980 Then
Application.EnableEvents = False
Target.Value = Target.Value & Warning
Application.EnableEvents = True
End If
ElseIf Target.Address = "$C$17" And Len(Range("C21").Value) > 0 Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Target.Value < 1980 Then
Range("C21").Value = Range("C21").Value & Warning
End If
Application.EnableEvents = True
End If
End Sub

Rick


"HH" <(E-Mail Removed)> wrote in message
news:gOmSj.45806$%(E-Mail Removed)...
> Rick,
> Yes, C17 and C21 are the only cells involved. What I want to do is add
> the staement "Construction material may contain lead." to C21 if a year
> less than 1980 is entered in C17.
> You are right, there may be other text already in the cell when the
> statement is to be added. Also there may be text added to C21 after the
> statement is added.
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> If the user can type into the cell, then you cannot put a formula in the
>> cell also (it will be overwritten by the user's entry). The only way to
>> do what you want is through an event procedure. In order to give you the
>> code you will need, we need some more information. Are you interested in
>> cells C17 and C21 only? Or is this a functionality you need across
>> multiple columns? If multiple columns, which ones (start column, end
>> column)? Always rows 17 and 21, or do other rows need to react to the
>> value typed into C17?
>>
>> Rick
>>
>>
>> "HH" <(E-Mail Removed)> wrote in message
>> news:kGgSj.45664$%(E-Mail Removed)...
>>> Rick,
>>> C21 is a general text cell where the user can type comments. There is
>>> no formula in this cell now.
>>>
>>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>>> in message news:%(E-Mail Removed)...
>>>> What is in C21 now? By that I mean, does it contain a formula which
>>>> displays text (if so, tell us the formula) or does it contain text
>>>> typed in by the user?
>>>>
>>>> Rick
>>>>
>>>>
>>>> "HH" <(E-Mail Removed)> wrote in message
>>>> news:2j9Sj.1669$_.(E-Mail Removed)...
>>>>> Maybe I was not clear enough - or maybe I don't understand. I'll try
>>>>> again..
>>>>>
>>>>> If I put a value less than 1980 in cell C17, I would like a text
>>>>> statement added to cell C21. This added text statement would say
>>>>> "Construction material may contain lead." There may already be a text
>>>>> statement in C21 so the new statement would be added at the end of
>>>>> whatever is already in the cell.
>>>>> What I have come up with is: =If (C17>1980,C21="Construction material
>>>>> may contain lead.","") I think this would delete whatever is
>>>>> already in C21 and replace the Consturction material...statement. But
>>>>> even if it would work - I don't know where to add the =if statement.
>>>>> Thanks
>>>>>
>>>>> "HH" <(E-Mail Removed)> wrote in message
>>>>> news:rx2Sj.1452$_.(E-Mail Removed)...
>>>>>> Based on value in C15 I want to have a text statement added to cell
>>>>>> B21. I would like this to be put at the end of whatever is already in
>>>>>> B21 - not just replace the cell content.
>>>>>>
>>>>>> Need help with the code - and where it would go.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>