Add text based on imput in another cell

  • Thread starter Thread starter HH
  • Start date Start date
H

HH

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---
 
Let say tht B21 originally contained:
=B19+B20
change this to:
=IF(C15=1,(B19+B20) & " and C15 has a one",B19+B20)
 
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
 
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.
 
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
 
Rick,
C21 is a general text cell where the user can type comments. There is no
formula in this cell now.
 
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
 
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.
 
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
 
Rick,
The code works - but only if there is another entry in C21. If the C21 does
not have an entry when the date is entered into C17 it does not work.
The warning will appear in C21 after any entry is added - even a space.
Also - if I enter a wrong date (pre 1980) it will enter the warning. If I
then enter the correct date (still pre 1980) it enters the warning again.
i.e. If I enter 1950 the warning appears. If I change the date to 1955 the
warning appears again.. If I change the date to after 1980 - it work great-
all warnings are removed.

Hank
 
I had to guess at the functionality you would have wanted if C21 was empty
and it looks like I guessed incorrectly. Try replacing **all** the code I
gave you earlier with the code below and see if it functions like you
want...

'********** START OF CODE **********
Const Warning As String = " (Construction material may contain lead.)"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Or Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Range("C17").Value < 1980 Then
Range("C21").Value = Range("C21").Value & Warning
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Value
If Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
Application.EnableEvents = True
Else
If Range("C17").Value < 1980 And _
InStr(Range("C21").Value, Warning) = 0 Then
Application.EnableEvents = False
Range("C21").Value = Range("C21").Value & Warning
Application.EnableEvents = True
End If
End If
End Sub
'********** END OF CODE **********

Rick
 
Rick,
That works great except when I try to manually type something else in C21.
Then the warring is not visible. The new typed text is added before the
warning. After I move to another cell the warning reappears along with the
manually entered text.
It will work OK as is but would be better if the warning was visible and new
text was added after the warning.
Hank
 
I wrote the code making the warning disappear while the user is entering
something in C21 on purpose. The problem as I saw it was, if the warning
were allowed to remain during editing, there would be no practical way to
protect if from being modified (either on purpose or by accident). Consider
if the user accidentally deleted the closing parenthesis from the warning
message (although any deletion and/or modification will have the same
effect, I just chose the smallest one)... then the program would not be able
to recognize it as the warning message any more and would treat it as newly
edited text. That means when the user leaves the cell, a new warning message
would be added to the remaining text in the cell (the user's new text plus
the remainder of the modified warning message). To protect the warning
message against this scenario, I decided to remove it when the user went to
edit the text in C21 and restore it when they were finished. If you really
do not like this action, I can modify the code to leave the warning message
in the cell during editing... just let me know. Meanwhile, here is the code
to place the warning message in front of the user's entry...

'********** START OF CODE **********
Const Warning As String = "(Construction material may contain lead.) "

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$17" Or Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
If Range("C17").Value < 1980 Then
Range("C21").Value = Warning & Range("C21").Value
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Value
If Target.Address = "$C$21" Then
Application.EnableEvents = False
Range("C21").Value = Replace(Range("C21").Value, Warning, "")
Application.EnableEvents = True
Else
If Range("C17").Value < 1980 And _
InStr(Range("C21").Value, Warning) = 0 Then
Application.EnableEvents = False
Range("C21").Value = Warning & Range("C21").Value
Application.EnableEvents = True
End If
End If
End Sub
'********** END OF CODE **********

Rick
 
Rick,
That makes sense when I think about it. The code works great now.
Thanks for your hard work and patients.
Hank
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Move multiple values in single Cell To Cells of there own? 3
Easy text formulae 2
Multiple Ranges in Hide Row Code 5
Auto Fill Equations in VBA 5
conditionally formatting colour of cells 2
Execute code on a range of cells, then move down and do again? 3
Unable to sum range variable 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0

Back
Top