Very complex formula applied by default to thousands of cells ???
Nelson Hicks
considered a user defined function?
Nelson
Kansas City, MO USA
Gozo <ROT13=Tb...@fbpvbybtvfg.pbz> wrote in message
news:8F6C25811...@63.211.125.90...
> I've developped a data sheet involving a very very complex string
> concatenation formula that need to be tuned-up and adapted quite
> frequently. The formula has to be applied to each record/line of my data
> sheet which are actually over a thousand...
>
> I would like to avoid having to coopy my formula from its master location
> to every record/line EACH time I edit it ! Is there a way to say that a
> given cell use the formula found in another cell, but *adapted* to the
> first's cell relative location ? I could then have only one occurence of
> the formula somewhere, all other lines/record using an absolute addressing
> to the master formula and adapting it to their location.
>
> Thanks for your help
>
> --
> Gozo
>
philwwebb
How about using a user-defined function such as the one below? When you
need to make a change to it, you can recalculate the sheet (F9) to have the
changes take effect.
Function udfGenerateHTML(URL As Range, _
SiteName As Range, _
ImageName As Range, _
Score As Range, _
Description As Range, _
Comment As Range) As String
Dim strHTML As String
strHTML = "<tr><TD align=" & Chr(34) & "center" & Chr(34) & ">"
If Not IsEmpty(ImageName) Then
strHTML = strHTML & "<a href=" & Chr(34) & URL.Value & Chr(34) & ">"
strHTML = strHTML & "<img src=" & Chr(34) & ImageName
strHTML = strHTML & ".gif" & Chr(34) & " border=" & Chr(34) & "0" &
Chr(34) & "></A>"
Else
strHTML = strHTML & Chr(38) & "nbsp" & Chr(59)
End If
strHTML = strHTML & "</TD><TD><a href=" & Chr(34) & URL & Chr(34) & ">"
& SiteName & "</A></TD>"
strHTML = strHTML & "<TD align=" & Chr(34) & "center" & Chr(34) & ">"
If Not IsEmpty(Score) Then
strHTML = strHTML & "<img src=" & Chr(34) & Score & ".gif" & Chr(34)
& _
" border=" & Chr(34) & "0" & Chr(34) & ">"
Else
strHTML = strHTML & Chr(38) & "nbsp" & Chr(59)
End If
strHTML = strHTML & "</TD><TD>"
If Not IsEmpty(Description) Then
strHTML = strHTML & Description
Else
strHTML = strHTML & Chr(38) & "nbsp" & Chr(59)
End If
If Not IsEmpty(Comment) Then
strHTML = strHTML & "<BR><I>" & Comment & "</I>"
End If
strHTML = strHTML & "</TD></TR>"
udfGenerateHTML = strHTML
End Function
Regards,
Phil Webb
Gozo <ROT13=Tb...@fbpvbybtvfg.pbz> wrote in message
news:8F6C37122...@63.211.125.90...
> nkh...@netzero.net (Nelson Hicks) wrote in
> <OmuIK0X6$GA.293@cppssbbsa04>:
>
> >Can you post your formula and give some specific details? Have you
> >considered a user defined function?
>
> The formula itself won't help you much, but here it is:
>
> ="<tr><TD align="&CHAR(34)&"center"&CHAR(34)&">"&IF(C2<>"",("<a
href="&CHAR
> (34)&A2&CHAR(34)&">"&"<img src="&CHAR(34)&C2&".gif"&CHAR(34)&"
> border="&CHAR(34)&"0"&CHAR(34)&"></A>"),CHAR(38)&"nbsp"&CHAR(59))&"</TD>
> <TD><a href="&CHAR(34)&A2&CHAR(34)&">"&B2&"</A></TD><TD align="&CHAR(34)
> &"center"&CHAR(34)&">"&IF(F2<>"",("<img src="&CHAR(34)&F2&".gif"&CHAR(34)
> &" border="&CHAR(34)&"0"&CHAR(34)&">"),(CHAR(38)&"nbsp"&CHAR(59)))&"</TD>
> <TD>"&IF(G2<>"",G2,(CHAR(38)&"nbsp"&CHAR(59)))&IF(H2<>"",("<BR><I>"&H2
> &"</I>"),"")&"</TD></TR>"
>
> I use this spreasheet to manage Web bookmarks and to publish them into
HTML
> table format. In this exemple, each line is a record and contents the
> following in respective columns:
>
> A = url in text mode
> B = name of web site
> C = name of logo image file (may be empty)
> F = score (transform into image file name, may be empty)
> G = description (may be empty)
> H = comment (may be empty)
> I = the formula
>
> Using this formula allows me to cut and paste one or few adjacent cells
and
> have a nicely formatted HTML table row with inline image as a result (try
> it!).
>
> Note that this is only one exemple. I have actually other complex formulas
> for differents needs.
>
> I have now over a thousands bookmarks.
>
> As present, I recopy formulas to every line each time I change them, but I
> would like to have only one occurence of each formulas, and be able to
tell
> each line to look for these formulas and apply them locally. The reason is
> that I edit these formulas quite often, and to recopy them over thousands
> of cells each time is time-consuming.
>
> Is this possible ?
>
> >
> >Nelson
> >Kansas City, MO USA
> >
> >Gozo <ROT13=Tb...@fbpvbybtvfg.pbz> wrote in message
> >news:8F6C25811...@63.211.125.90...
> >> I've developped a data sheet involving a very very complex string
> >> concatenation formula that need to be tuned-up and adapted quite
> >> frequently. The formula has to be applied to each record/line of my
> >> data sheet which are actually over a thousand...
> >>
> >> I would like to avoid having to coopy my formula from its master
> >> location to every record/line EACH time I edit it ! Is there a way to
> >> say that a given cell use the formula found in another cell, but
> >> *adapted* to the first's cell relative location ? I could then have
> >> only one occurence of the formula somewhere, all other lines/record
> >> using an absolute addressing to the master formula and adapting it to
> >> their location.
> >>
> >> Thanks for your help
> >>
> >> --
> >> Gozo
> >>
> >
> >
> >
>
>
> --
> Gozo
>
David McRitchie
Hi Gozo,
Excel Help and Excel VBA Help are kept separate. The
VBA help is obtained where you can enter macros. So in
Excel 97 and up Alt+F11 and the F1 for Help, if you had
Excel 95, you would invoke help from a module sheet.
But you are correct there is not much to help you in the transition
area. Such HELP as is available is found in the VBA Help area.
Since you managed to install and use macro, I expect you are
set.
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Gozo <ROT13=Tb...@fbpvbybtvfg.pbz> wrote in message
news:8F6C9615B...@63.211.125.90...
> Gosh! The solution is so simple that I feel stupid (but I should not: just
> few minutes ago, I knew nothing about User-Defined functions and VBA !!!).
>
> After having read your answer, I made a quick search on the Internet and
> easily found few good web pages to learn how to create udf and other VBA
> modules. Pretty easy ! Actually it's so simple, I wonder why this feature
> isn't documented in Excel Help file...
>
> Thanks a lot for your script ! I've done some QuickBasic DOS programming
> (almost ten years ago), so VBA seems quite straightforward to me. I feel
> now quite comfortable to tune-it up and create other functions as I need
> them.
>
> Your reply was very helpful.
>
> Best regards and thanks a lot once again.
>
> Gozo
>
> phil...@email.msn.com (philwwebb) wrote in
> <Ogjprya6$GA.279@cppssbbsa04>:
> --
> Gozo
>
David McRitchie
Using the Function supplied by Phil, you might want to simply
generate your HTML file separately. Code attached after signature.
related: http://www.geocities.com/davemcritchie/excel/xl2htmlx.htm
Netscape 3.x used to sort bookmarks by name within categories,
as long as category began with a blank name, of course you have
added two icons or logos. Using Excel to sort your bookmarks
into categories will help. You could sort on a concatenation of
topics ( level1, level2, level3. ...)
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
===============coding================
Option Explicit
Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal _
lpOperation As String, ByVal lpFile As _
String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal _
nShowCmd As Long) As Long
Declare Function GetForegroundWindow Lib "user32" () As Long
Sub CreateBookmarks()
'UNFINISHED>>>>>>>>>>>>>>>>>>>>>>>>>
'This would use the udfGenerageHTML supplied by Phil
'You need only select in column A
Dim r%
Dim nr As Long
Dim lastcell As Range
Dim filename As Variant
nr = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
If nr > lastcell.Row Then nr = lastcell.Row
filename = InputBox("Supply filename for HTML generated from " _
& "selected range in Column A", "Filename for CreateBookmarks", _
"c:\temp\XL2test.htm")
'FileName = "c:\temp\XL2test.htm"
Close #1
Open filename For Output As 1
Print #1, "<html><head></head><body>"
Print #1, "<!-- ======================================= -->"
Print #1, "<table border=""1"" bgcolor=""#FFFFFF""" _
& " cellspacing=""0"" cellpadding=""0"">"
Dim xStr As String
For r = 1 To nr ' Columns: A, B, C, F, G, H
xStr = udfGenerateHTML(Range(Cells(r, 1).Address), Range(Cells(r,
2).Address), _
Range(Cells(r, 3).Address), Range(Cells(r, 6).Address), _
Range(Cells(r, 7).Address), Range(Cells(r, 8).Address))
Print #1, xStr
Next r
Print #1, "</body></html>"
Print #1, "<!-- ======================================= -->"
Close #1
MsgBox "CreateBookmarks placed your HTML code in" & Chr(10) & filename
ShellExecute 0, "open", filename, "", "", 0
End Sub
============ end coding ==================
David McRitchie
Hi Gozo,
The code automatically opens the HTML code for viewing.
The actual purpose is that if it
is acceptable is to view the source extract the table and
paste it where wanted. If not wanted it was a temporary
file anyway. I have lots of tables within tables
in my finished code, depending on my layout, I demark the
generated table with a comment using a series of equal signs
to help with demarcation.