PDA

View Full Version : Solved: Excel insists on changing the format of cells



EirikDaude
10-16-2009, 07:00 AM
I'm having some trouble getting Excel to understand that the string I'm putting into some cells is to be interpreted as a formula, and not as text.

I've tried changing the formatting of the cells both before and after putting the string into it, but no matter what I do the cell formatting reverts back to "Text".

Has anyone here got any idea of what I should do to solve my problem?

I'm using Excel 2003 and my code currently looks like this:


(...)
For teljar2 = 0 To 9
skrivTil.Offset(33 + justering + teljar2, -1).NumberFormat = "General"
skrivTil.Offset(33 + justering + teljar2, -1).Value = _
hentSkiftmedlemAdresse(CInt(startSkiftEr1(teljar)), "BR", teljar2)
Next
(...)

Function hentSkiftmedlemAdresse(skift As Integer, omraade As String, posisjon As Integer) As String
Dim justering As Integer

With Sheets("Kompetanseoversikt")

Select Case omraade
(...)
Case "BR"
justering = (skift - 1) * 10
hentSkiftmedlemAdresse = "=" & .Cells(41 + justering + posisjon, 1).Address(External:=True)
(...)
End Select
End With
End Function


As you can see I've put the number-formatting before inputting the string in this case, but I've also tried it afterwards, with no more luck. Ideally I'd hope to get rid of the formatting altogether while running the sub, as it takes long enough to run without it :whyme:

Thanks in advance for all the help you'll come up with :biggrin:

mdmackillop
10-16-2009, 07:10 AM
You have to say it is a formula

Range("A1").Formula = "=B1+C1"

EirikDaude
10-16-2009, 07:17 AM
Simple as that, eh. Do I feel stupid now :)

So instead of

Range.Value = ...

I put in

Range.Formula = ...

Well, thanks a lot for your help.

mdmackillop
10-16-2009, 07:19 AM
or FormulaR1C1 depending on your reference type.

EirikDaude
10-16-2009, 07:27 AM
Yup.

Seems I've got it to work now.

Again, thanks a lot for your help. Maybe this will prevent all my hair from turning grey before I'm done with this project :)