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

Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

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?

That would save having to then manually enter the formulas after writing
the data which I've been doing...a big time-saver.

PS. This goes along with the "many to one" and the SUMIFS()
problem--those work in the target sheet to correctly return the proper
results from new data; the app successfully inserts into the proper
locations the new data including the \n for the many-to-one issue so if
had this worked out, would be almost done. I can do one by hand to get
the bookkeeper going on the immediate problem tomorrow, but there are a
number of other sheets yet to go...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

The painful ones are those that operate on a group of rows above them
that aren't necessarily the same number and that are grouped by Fund
which contains subfunds for example.

Appearance to user would look something like--

DONOR ACCT AUTHORIZED
Fred Flintstone F6321 $2,000
Fred Flintstone F6421 $500
Fred Flintstone SUMMARY $2,500
Barney Rubble
...

where the cell content would be

DONOR ACCT AUTHORIZED
Fred Flintstone F6321 $2,000
Fred Flintstone F6423 $500
Fred Flintstone SUMMARY =SUM(H37:H38)
Barney Rubble
...

The reason for the subfunds has to do with other unrelated reasons but
that for accounting purposes we must track income/expense separately for
the subaccounts, not just the total fund.

I can compute/know both the column "H" and rows "37,38" for the
rearranged workbook and easily write the text for the formula in the appropriate cell...it isn't possible (other than by COM that was trying
to avoid in the external app) to write a formula property but I can
write the text.

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?

Thanks...dpb

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/13/2020 9:51 AM, dpb wrote:

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?

...

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, again, sorry for doubting... :)


--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/13/2020 10:24 AM, Claus Busch wrote:

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

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?

The Excel object model is so massive trying to figure out what is the
property to try to change and then to figure out how to use is
mind-boggingly complex to just try to jump into without any real
VBA-Excel background.

I used VB and before that PB7.1 for data acq systems extensively like
30+ years ago, but never used Excel to any extent at all until got
involved here with the local CC Foundation and trying to speed up their processes that rely on dozens of these convoluted/complex spreadsheets
and moving data by hand from one to another and copying/pasting/entering
same thing multiple times...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Sun, 13 Sep 2020 10:45:00 -0500 schrieb dpb:

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?

some things a not documented and you only find them out with heavy work
in Excel and a lot of expirience.
Most people knows TextToColumns to seperate strings.
But you can it also use to change quickly the format and write values
new. Another thing you can do with TextToColumns is deleting superfluous spaces.


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/13/2020 8:25 AM, dpb wrote:

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.

...

Well, actually it does--just discovered another feature hadn't realized
was there before -- it DOES pay to read the doc again with new product
releases sometimes!! :)

Anyway, the writing routine now has the ability to start an Excel
session during the write in which case Excel now interprets the "=" in
the cell content as the formula...so, I can indeed build a routine to
compute the formula content and put them where needed dynamically from
the other app without actually having to delve into the COM engine
directly...

Thanks again for the help -- particularly the ability to use regexp
and/or wildcards in the IF condition made it all come together...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)