So, with that as background to the Q? -- What would a user function look
like to locate those cells with their content beginning with the "="
sign in a newly-created workbook and use that cell content to set the
formula for the cell?
Hi,
Am Sun, 13 Sep 2020 08:25:01 -0500 schrieb dpb:
So, with that as background to the Q? -- What would a user function look
like to locate those cells with their content beginning with the "="
sign in a newly-created workbook and use that cell content to set the
formula for the cell?
I hope I understood your problem correctly.
If those cells are in one column then click in the columns header =>
Data => Text to columns => Fixed Width => Finish
On 9/13/2020 8:40 AM, Claus Busch wrote:
Hi,
Am Sun, 13 Sep 2020 08:25:01 -0500 schrieb dpb:
So, with that as background to the Q? -- What would a user function look >>> like to locate those cells with their content beginning with the "="
sign in a newly-created workbook and use that cell content to set the
formula for the cell?
I hope I understood your problem correctly.
If those cells are in one column then click in the columns header =>
Data => Text to columns => Fixed Width => Finish
Some are, some aren't, unfortunately, the time-consuming part is they're scattered all around the place.
I was hoping to just run a macro after building the data and text
formulas into the new sheet to automagically convert the formulat text
to the actual cell formula.
I'll try the suggestion but it doesn't actually do the conversion, does it?
Hi,
Am Sun, 13 Sep 2020 10:03:21 -0500 schrieb dpb:
BY GOLLY!!! It DOES do the conversion--hadn't ever tried the conversion
from a cell containing a formula before. Old dog, new tricks! :)
OK, but it doesn't let select scattered-around cells; be faster than
having to type/convert manually, but still be nice if were a way to do
it globally.
when the formula is shown in a cell that cell is textformatted.
It is not enough to change the format to "General". You have to write
the formula again or press F2 and Enter.
TexttoColumns writes the whole column new.
Try TextToColumns over all columns in your sheet:
Sub ConvFormulas()
Dim myCol As Range
For Each myCol In ActiveSheet.UsedRange.Columns
myCol.TextToColumns Destination:=Cells(1, myCol.Column), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Next
End Sub
BY GOLLY!!! It DOES do the conversion--hadn't ever tried the conversion
from a cell containing a formula before. Old dog, new tricks! :)
OK, but it doesn't let select scattered-around cells; be faster than
having to type/convert manually, but still be nice if were a way to do
it globally.
Thanks; that gives me something from which to start...finished the hard
data translation, ready to attack actually writing the formulas where
they belong.
Can you 'splain what/how that works to the neophyte?
Am building data for workbook dynamically from external application that contains the input data -- things move around depending upon number of elements of given type, both by column and row......
But, knowing the numbers and which, can compute/determine the row/column
and therefrom build a string that is the required formula to insert for
sums or differences or whatever at the correct locations besides the underlying data and can write those strings in those cells.
The external app includes a prepackaged function that will allow me to
write the data to create the spreadsheet very easily; it doesn't have
the facility to write formulas builtin, unfortunately.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 88:02:16 |
Calls: | 6,658 |
Files: | 12,203 |
Messages: | 5,333,954 |