CODEWITHSUNDEEP
excelvba
Brianna Cates
Brianna Cates

Reputation: 347

VBA Formula and Text in a specific cell

I am trying to have some vba code to place the value of a formula and text into a specific cell. I want the code to say Leads:(value of formula). Currently my code runs the formula and places the value in the correct box. I just do not know how to add the text with it. The code I have is written below.

ws.Range("$B$1").Formula = "=COUNTIF(E:E,""Lead"")"

Upvotes: 4

Views: 178

Answers (2)

user4039065
user4039065

Reputation:

A custom Range.NumberFormat property will give you the displayed result while leaving the actual raw value in a numerical form for possible further computation or comparison.

with ws
    with .range("B1")    '<~~ no need for absolute $ anchors here
        .formula = "=COUNTIF(E:E,""Lead"")"
        .numberformat = "[=1]L\e\a\d\: 0;L\e\a\d\s\: 0"
    end with
end with

Upvotes: 5

Scott Craner
Scott Craner

Reputation: 152450

Try this:

ws.Range("$B$1").Formula = "=""Leads:("" & COUNTIF(E:E,""Lead"")&"")"""

so that it ends up like the following when applied:

="Leads:(" & COUNTIF(E:E,"Lead")&")"

Upvotes: 4

Related Questions