how to convert text to formula?

[omar]

hi!

i've been trying to convert the values of some cells to formulas.

those values are aalmost a formula, except for the "=". i tried to replace
"'Z:\blablabla" for "='Z\blablabla" but it didn't work. no match found.

so, i discovered that the macros have a powerful tool:
ActiveSheet.Cells(2, 1).Formula = "=sum(B1:B5)"

i made this little macro but, guess what, it doesn't work:

no_row = 227
Do While no_row < 445
no_col = 3
Do While no_col < 163
va = Worksheets("sales").Cells(no_row, no_col).Value
ActiveSheet.Cells(no_row, no_col).Formula = CVar("=" & va)
no_col = no_col + 1
Loop
no_row = no_row + 1
Loop

could you plis help me to figure it out?
tia!

 

[Tom Ogilvy]

Sub MakeFormulas()
Dim rng as Range, cell as Range
With Worksheets("Sales")
Set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
for each cell in rng
cell.formula = "=" & cell.Value
Next
End If
end Sub

 

[omar]

hi,

i used your code and it was working very well for the three firsts cells,
then it stopped and 1004 error appears.

any idea?

tia!

- omar

 

[Tom Ogilvy]

Probably means that it doesn't contain a legitimate formula.

Sub MakeFormulas()
Dim rng as Range, cell as Range
With Worksheets("Sales")
Set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
for each cell in rng
On Error Resume Next
cell.formula = "=" & cell.Value
On Error goto 0
Next
End If
end Sub

 

[omar]

i really appreciate your help, but surely i'm doing something wrong.

let me explain a little better my problem.

in the worksheet "sales" i have almost 425000 cells. each of them has the
next text, well, it varies: (it'd be crazy to have 425000 cells with the same
formula)

'Z:\service\2005\[0101.xls]fev'!$O$51

as you can see, i'm linking the information contained in others workbooks.

now, it seems that when i execute your last code it always finds some error
because doesn't change a thing.

the result i'm expecting is:

='Z:\service\2005\[0102.xls]fev'!$G$8

thank you so much!

omar.

 

[Tom Ogilvy]

Sub MakeFormulas()
Dim v as Variant
Dim rng as Range
With Worksheets("Sales")
set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
v = rng.Value
for i = lbound(v,1) to ubound(v,1)
for j = lbound(v,2) to ubound(v,2)
v(i,j) = "=" & v(i,j)
next j
next i
rng.Value = v
end Sub

--
Regards,
Tom Ogilvy

i really appreciate your help, but surely i'm doing something wrong.

let me explain a little better my problem.

in the worksheet "sales" i have almost 425000 cells. each of them has the
next text, well, it varies: (it'd be crazy to have 425000 cells with the same
formula)

'Z:\service\2005\[0101.xls]fev'!$O$51

as you can see, i'm linking the information contained in others workbooks.

now, it seems that when i execute your last code it always finds some error
because doesn't change a thing.

the result i'm expecting is:

='Z:\service\2005\[0102.xls]fev'!$G$8

thank you so much!

omar.

Probably means that it doesn't contain a legitimate formula.

Sub MakeFormulas()
Dim rng as Range, cell as Range
With Worksheets("Sales")
Set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
for each cell in rng
On Error Resume Next
cell.formula = "=" & cell.Value
On Error goto 0
Next
End If
end Sub

 

[Omar]

mr. ogilvy, i thank you so much your time and help.

i don't know what's happening but the code you posted didn't work. anyways i
appreciate so much your help.

take care!

omar.

Sub MakeFormulas()
Dim v as Variant
Dim rng as Range
With Worksheets("Sales")
set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
v = rng.Value
for i = lbound(v,1) to ubound(v,1)
for j = lbound(v,2) to ubound(v,2)
v(i,j) = "=" & v(i,j)
next j
next i
rng.Value = v
end Sub

--
Regards,
Tom Ogilvy

i really appreciate your help, but surely i'm doing something wrong.

let me explain a little better my problem.

in the worksheet "sales" i have almost 425000 cells. each of them has the
next text, well, it varies: (it'd be crazy to have 425000 cells with the same
formula)

'Z:\service\2005\[0101.xls]fev'!$O$51

as you can see, i'm linking the information contained in others workbooks.

now, it seems that when i execute your last code it always finds some error
because doesn't change a thing.

the result i'm expecting is:

='Z:\service\2005\[0102.xls]fev'!$G$8

thank you so much!

omar.

Probably means that it doesn't contain a legitimate formula.

Sub MakeFormulas()
Dim rng as Range, cell as Range
With Worksheets("Sales")
Set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
for each cell in rng
On Error Resume Next
cell.formula = "=" & cell.Value
On Error goto 0
Next
End If
end Sub


--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

hi,

i used your code and it was working very well for the three firsts cells,
then it stopped and 1004 error appears.

any idea?

tia!

- omar

:

Sub MakeFormulas()
Dim rng as Range, cell as Range
With Worksheets("Sales")
Set rng = .Range("C227:FF444")
End With
rng.NumberFormat = "General"
for each cell in rng
cell.formula = "=" & cell.Value
Next
End If
end Sub


--
Regards,
Tom Ogilvy


hi!

i've been trying to convert the values of some cells to formulas.

those values are aalmost a formula, except for the "=". i tried to
replace
"'Z:\blablabla" for "='Z\blablabla" but it didn't work. no match
found.

so, i discovered that the macros have a powerful tool:
ActiveSheet.Cells(2, 1).Formula = "=sum(B1:B5)"

i made this little macro but, guess what, it doesn't work:

no_row = 227
Do While no_row < 445
no_col = 3
Do While no_col < 163
va = Worksheets("sales").Cells(no_row, no_col).Value
ActiveSheet.Cells(no_row, no_col).Formula = CVar("=" & va)
no_col = no_col + 1
Loop
no_row = no_row + 1
Loop

could you plis help me to figure it out?
tia!