insert cell value into string

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hello-

I have the code below which is evaluating cell (E6) and entering a
text string in (D15) based on the value. I want to replace the XXX
part of the string with the value of (A6) if each scenario. Any ideas?
Thanks in advance.

Scott

Sub Com1()

Dim myCell As Range
Set myCell = Selection


On Error Resume Next

If Range("E6").Value <= 8 Then
Range("d16").Select
ActiveCell.FormulaR1C1 = "Great job, you handled XXX calls yesterday!"
ElseIf Range("E6").Value <= 8.25 Then
Range("d16").Select
ActiveCell.FormulaR1C1 = "Way to go, your XXX calls really made a
difference!"
ElseIf Range("E6").Value <= 8.5 Then
Range("d16").Select
ActiveCell.FormulaR1C1 = "Wow, XXX calls in one day is fantastic!"

End If
myCell.Select
End Sub
 
i would add these 2 lines:

dim ws as worksheet
set ws = worksheets("Sheet1")

then
ActiveCell.Value = "Wow, " & ws.Range("A1").Value & "calls in one day is
fantastic!"
 
Gary said:
ActiveCell.Value = "Wow, " & ws.Range("A1").Value & "calls in one day is
fantastic!"
Click to expand...

I think it's better to use this code:

ActiveCell.Value = "Wow, " + ws.Range("A1").Text + "calls in one day _
is fantastic!"

AFAIR is '&' the operator for 'Binary AND'. To concat strings in VBA,
you need the '+' operator.

And I found that using the .text property of a range saves you some
hassle, if the content of the cell can be interpreted as something other
than a string.

CU,
Christian
 
Back
Top