displaying text & variable information in cell

[pallaver]

I have two variables taken from textboxes on a userform: XPosVariance
& XNegVariance

I want to have them displayed in a cell when clicking done on the
userform such that the cell reads:

+5/-8

(In the case that XPosVariance = 5 and XNegVariance = 8).

I have this:

Sheets("´£¥X¥Î").Cells(SokuteiItemRow, SokuteiItemColumn + 2).Value = "+"
& (XPosVariance) & "/-" (XNegVariance)

But it doesn't work. I'm sure the variables are fine because as a
test I just wrote = "TEST" and I get "TEST" written in the correct
spot. Any way I can do the above? I have a feeling this isn't a
tough question, I just don't know how to do it.

Thankx.

 

[Mike]

Maybe this
Sheets("???").Cells(SokuteiItemRow, SokuteiItemColumn + 2).Value = _
Format("+" & (XPosVariance) & "/-" & (XNegVariance), "@")

 

[pallaver]

BAM! On the money.

Sorry for the ???, I'm working on macros in Japan, thus have sheet
names in Japanese which probably don't convert. This site is a
lifesaver since it's really hard to figure out the help function
entirely in Japanese.

One question though, why is "@" necessary at the end? I wonder if it
works without it.

Thanks.

 

[Mike]

If you record a macro formatting a cell to text. That is what it records. "@"
Try it.

 

[Mike]

And yes it will work with out it.

BAM! On the money.

Sorry for the ???, I'm working on macros in Japan, thus have sheet
names in Japanese which probably don't convert. This site is a
lifesaver since it's really hard to figure out the help function
entirely in Japanese.

One question though, why is "@" necessary at the end? I wonder if it
works without it.

Thanks.

 

[pallaver]

I know how to record a macro, but don't know how to record one
formatting a cell to text. What would I type in the cell when the
macro is recording?

--- MIKE WROTE ---
If you record a macro formatting a cell to text. That is what it
records. "@" Try it.

 

[Mike]

Just right-click on a cell. Select Format Cells... Select Text as the format.
Thats it.

 

[Rick Rothstein \(MVP - VB\)]

In looking at the code line, I don't see why the Format function call is
needed at all... it looks like a simple concatenation and cell assignment to
me.

To the OP.... does this modified statement work also?

Sheets("???").Cells(SokuteiItemRow, SokuteiItemColumn + 2).Value = _
"+" & XPosVariance & "/-" & XNegVariance

Rick

 

[pallaver]

Nope, sorry Rick. That doesn't work. I get an error. I tried adding
parenthesis around the variables as well, but similar to above, NG.

I'm stuck on my macro now on some execution error 13, non-agreement
with bodies.... argh...

 

[Dave Peterson]

You never shared what those row and column values were.

I'd use one of these just to make sure that the value is treated like text.

Later, if you edit that cell (that's formatted as General), you may find that
your text expression has changed to a formula. And I don't know if you want
that.

Sheets("Sheet1").Cells(SokuteiItemRow, SokuteiItemColumn + 2).Value _
= "'+" & XPosVariance & "/-" & xNegVariance

(notice the apostrophe in front of the plus)

or

with Sheets("Sheet1").Cells(SokuteiItemRow, SokuteiItemColumn + 2)
.numberformat = "@" 'text
.Value = "+" & XPosVariance & "/-" & xNegVariance
end with