PDA

View Full Version : Excel Add Text in Cell with already existing text



grohm
02-21-2008, 03:02 AM
Hi All

I want to have a button/function, which, when clicked, will add a certain text to the already existing text in that cell. this is what i have:
Sub EngagementsMCS_Button1_Click()

ActiveCell = "" & ("On HOLD!")


End Sub

but it deletes the existing text in the cell unfortunately....

any help?

regards

grohm

Bob Phillips
02-21-2008, 03:06 AM
Sub EngagementsMCS_Button1_Click()

With ActiveCell

.Value = .Value & "On HOLD!"
End With

End Sub

grohm
02-21-2008, 03:13 AM
Sub EngagementsMCS_Button1_Click()

With ActiveCell

.Value = .Value & "On HOLD!"
End With

End Sub


hmmm, still doesn't work. same outcome as above :-(

Bob Phillips
02-21-2008, 03:17 AM
Nonsense, if the activecell has a value, this appends to it.

grohm
02-21-2008, 03:37 AM
Nonsense, if the activecell has a value, this appends to it.

ah yes, you were right. sorry for that. is there a opposite to & ?

i mean, i want to delete that same value from the cell that i added, so i could completely reverse the process from the first button. possible?

many thx!

Ago
02-21-2008, 03:58 AM
you mean if the value is "blabla on hold" you want a button to make it "blabla"?

i think you would have to split the cellvalue and check each part.


txt = ActiveCell.Value
x = Split(txt, " ")
j = 0

For i = 0 To UBound(x)
If x(i) = "On" And x(i + 1) = "HOLD" Then
while j < i
ActiveCell.Value = ActiveCell.Value & " " & x(j)
j = j + 1
Wend
End If
Next i



think this should work

Ago
02-21-2008, 04:06 AM
wait!
i just noticed it will add a space in the begining of each cell and osme other problems :-)
this should delete the problem with the space.


Sub test()
txt = ActiveCell.Value
x = Split(txt, " ")
j = 0

For i = 0 To UBound(x)
If x(i) = "On" And x(i + 1) = "HOLD" Then
ActiveCell.Value = ""
While j < i
If j = 0 Then
ActiveCell.Value = x(j)
Else
ActiveCell.Value = ActiveCell.Value & " " & x(j)
End If
j = j + 1

Wend
i = UBound(x)
End If
Next i
End Sub

Bob Phillips
02-21-2008, 04:22 AM
With ActiveCell

.Value = Replace(.Value, "On Hold", "")
End With