Problems with numeric formatting in cells

  • Thread starter Thread starter Matt Andrzejewski
  • Start date Start date
M

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
 
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
 
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.
 
Back
Top