Problems with numeric formatting in cells

[Matt Andrzejewski]

Hi Folks:

I can't figure this one out. I'm writing a macro that counts the
frequencies of certain values (similar to a frequency distribution) for
a range of data. So, in my routine I have the line:

Cells(i + 1, 7) = (i - 1) * var1 + Start & " - " & i * var1 + Start - 1

which should give me, in cell(i + 1, 7), for example:

6 - 10

except I'm getting:

Jun-10

Does anyone know how to set the format of cell(i + 1, 7) to "General" or
string, or something other than a date, in the code of the macro?

Thanks,

Matt Andrzejewski

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[J.E. McGimpsey]

One way:

With Cells(i + 1, 7)
.NumberFormat = "@"
.Value = (i - 1) * var1 + Start & " - " & i * var1 + Start - 1
End with

which changes cell format to Text. Another:

Cells(i + 1, 7).Value = "'" & (i - 1) * var1 + Start & " - " & _
i * var1 + Start - 1

which leaves the cell number format alone

 

[steve]

Matt,

One of 2 ways:
1. Preformat the cell as Text (or use code)
2. Cells(1,1) = "'" & 6 & " - " & 10
this puts a leading apostrophy in front of the value and Excel sees
it as text.