Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

If function contains a word Results

In Excel, is there a way to find out the character width of the data in a
column?
I have a column with 60,000 records and each record contains Street names. I
want to know what is the longest possible street name. I also want to look at
the distribution of the character or pixel width of all the street names.
Similarly, I have about 100 other columns with 60,000 records which contain
varying length of text, numeric or memo field data and again I want to find
the character width edge cases and distribution. The end goal is to give
guidance to UI design folks on mean and edge case character width so they can
decide on column width and word wrap.
Excel functions work with the data itself while I am interested in the width
of the data.
Any idea how one could possibly do this in Excel? If its possible in Visual
Basic, can you provide step by step directions since I am a novice in
programming. Thanks!

I have a requirement to dynamically build/create a hyperlink in a workbook
which would allow the user to quickly move between worksheets. ALL
informataion is contained within the workbook so there is no need (or
desire) to have any external links. The workbook would be laid out as
follows:

Worksheets:

Summary
Project 1
Project 2
Project 3

Each of the Project worksheets would have a project title located in cell I8

On the summary worksheet, each row would provide summary information pulled
directly off the Project worksheet. The worksheet name would be place in
column N therefore all pulls of information from that project would be done
referencing the worksheet name found in column N.

The requirement would be to dynamically build a hyperlink from the summary
worksheet to the respective project worksheet with the link being
effectively the title of the project (pulled for the project worksheet) and
the linke place in column B.

I have tried the HYPERLINK function as the help text implies this will
work - any suggestions?? - Here is information from the Help Text:

You can create hyperlinks within a worksheet to jump from one cell to
another cell. For example, if the active worksheet is the sheet named June
in the workbook named Budget, the following formula creates a hyperlink to
cell E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the
sheet in the link. In the previous example, to create a link to cell E56 on
the September sheet, change the word "June" to "September."

Compile error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late bound functions

The following code is called from the Public Sub reMain() procedure. The purpose of which is to count the number of letters in each paragraph (assume that the Word file referred to-- see below-- has already been opened at this point) and then return this value to a paragraph header line that also contains some identifying information and finally dump this data to an excel worksheet and from there it will be further analyzed.

Background Information: As it stands now, this procedure takes 3-4 minutes to execute. I want to reduce this time as much as possible. The overall goal being to analyze the Word document referred to above 3390 times. The Word file contains approx. 3.5 million characters-- without spaces (i.e. the doc is one big paragraph). Each time the program is run it is broken into fragments-- 'paragraphs', based on a particular sequence of characters that are unique each time the program is run.

Problem: I want to create a 'temporary' array, fill it with data (e.g. character count, the starting/ending character sequence number-- which assumes that this first character in the Word doc is #1, and then some identifying information, etc...), and then when the program has finished searching the document, copy the data to an Excel worksheet. I am working with a module contained within the PERSONAL.XLS project folder-- I don't think that is particular relevant but I want to be thorough.

What I Have Done So Far:
1. Created user-defined data type (module level declaration):
Option Explicit
Type FragmentInfo
xlRestrictionFragmentID As String
FragmentStart As Long
FragEnd_dBase As Long
CaraCount As Long
End Type
2. Tested program w/o printing array to worksheet. In the immediate window, I saw the correct data.

So the array has been correctly filled, but when I try to output the data:
With xlNewSheet
.Activate
Set rgOutput = .Range(Cells(2, 1), Cells(NextRowDown, 4))
rgOutput.Value = FragmentArray
End With
I get the error message described above. ANY suggestions would be greatly appreciated. TIA. --tiger_PRM.
Private Sub bpCount()
Dim rgColumnMaxMin As Range
Dim MaxFrag As Long
Dim MinFrag As Long
Dim FragmentStart As Long
Dim FragmentEnd As Long
Dim FragEnd_dBase As Long
Dim CycleNumber As Long
Dim xlPDBRestrictionFragmentID As String
Dim f As Long
Dim rgOutput As Excel.Range
NextRowDown = 1

For Each oPara In gFile.Paragraphs
f = f + 1
CycleNumber = CycleNumber + 1
NextRowDown = NextRowDown + 1
Set rgColumnMaxMin = xlNewSheet.Columns("D")
MaxFrag = WorksheetFunction.Max(rgColumnMaxMin)
MinFrag = WorksheetFunction.Min(rgColumnMaxMin)
With oPara
Set oParaRg = .Range
CaraCount = oParaRg.Characters.Count - 1
Select Case CaraCount
Case Is > 0
Select Case CircularAnswer
Case Is = vbYes
Select Case CycleNumber
Case Is = 1
FragmentStart = CYFPCaraCount + 1
FragmentEnd = CaraCount + FragmentStart
FragEnd_dBase = FragmentEnd - 1
Case Is < ParaCount
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd - 1
Case Is = ParaCount
FragmentStart = FragmentEnd
FragmentEnd = CYFPCaraCount
FragEnd_dBase = FragmentEnd
End Select
Case Is = vbNo
Select Case CycleNumber
Case Is = 1
FragmentStart = 1
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
Case Is > 1
FragmentStart = FragmentEnd
FragmentEnd = FragmentEnd + CaraCount
FragEnd_dBase = FragmentEnd
End Select
End Select
RestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93) & Chr(13)
xlRestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber)
xlPDBRestrictionFragmentID = RestrictionEnzyme + CStr(CycleNumber) & Chr(59) & _
Chr(32) & CStr(CaraCount) & "bp" & Chr(91) & CStr(FragmentStart) & _
Chr(45) & CStr(FragEnd_dBase) & Chr(93)
oParaRg.InsertBefore ">" & CStr(RestrictionFragmentID)
oParaRg.Style = wdStyleNormal
If CaraCount > MaxFrag Then
maxCaraCount = CaraCount
End If
If CaraCount < MinFrag Then
minCaraCount = CaraCount
End If
ReDim Preserve FragmentArray(1 To f) As FragmentInfo
FragmentArray(f).xlRestrictionFragmentID = xlRestrictionFragmentID
Debug.Print FragmentArray(f).xlRestrictionFragmentID
FragmentArray(f).FragmentStart = FragmentStart
Debug.Print FragmentArray(f).FragmentStart
FragmentArray(f).FragEnd_dBase = FragEnd_dBase
Debug.Print FragmentArray(f).FragEnd_dBase
FragmentArray(f).CaraCount = CaraCount
Debug.Print FragmentArray(f).CaraCount
' With xlNewSheet
' .Activate
' Cells(NextRowDown, 1) = xlRestrictionFragmentID
' Cells(NextRowDown, 1).HorizontalAlignment = xlLeft
' Cells(NextRowDown, 2) = FragmentStart
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 3) = FragEnd_dBase
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' Cells(NextRowDown, 4) = CaraCount
' Cells(NextRowDown, 4).HorizontalAlignment = xlCenter
' End With
Call SearchProteindBase(ByVal FragmentStart, FragmentEnd, xlPDBRestrictionFragmentID, CycleNumber)
Case Is = 0
oParaRg.Delete
End Select
End With
Next oPara
With xlNewSheet
.Activate
Set rgOutput = .Range(Cells(2, 1), Cells(NextRowDown, 4))
rgOutput.Value = FragmentArray
End With
End Sub

Firstly, sorry if the subject makes no sense or doesn't properly relate to my
question, I couldn't think of a way to word it in less than 70 characters.

I am compiling a budget spreadsheet to keep track of expenses, within it I
have two sheets, a summary giving monthly totals and a detailled sheet
containing each expense/income. I am trying to write a function that will be
able to take all occurrences of an expense, sum the total value of those
expenses and then output the total to the summary sheet for example:

SUMMARY SHEET

[B1]JAN [C1]FEB (etc)
[A2]Rent 500 ...
[A3]Shopping (25+15+40)
[A4]Gas (10+10+17)

DETAILED SHEET

[A1]DATE [B1]EXPENSE TYPE(from drop down list) [C1]VALUE
[A2]01/02/07 Rent
500
[A3]02/02/07 Shopping 15
[A4]02/02/07 Gas
17
[A5]05/02/07 Shopping 40
[A6]20/02/07 Gas
10
[A7]28/02/07 Gas
10
[A8]03/03/07 Shopping 25

So basically I want Exel to take the three instances of Gas in DETAILED
SHEET, Cells [B4], [B6], [B7] and sum their values ([C4]+[C6]+[C7]) and put
that total in SUMMARY SHEET Cell [B4]. I hope this is clear enough

Can anyone help on this? I'm too much of a novice to think of a way to do
this. I thought of an IF/THEN function but couldn't figure out a way for it
to accept multiple instances of a true result, without overwriting the value
in the summary sheet cell.

Thanks in advance

I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's all one
column, but each cell has three others in between them). So the way I was/am
trying to write C into the formula was by doing, in parentheses, each cell
separated by commas, figuring that creates the 15 cell block I need. So one
question is whether that's the right way to indicate in an array formula
(shft+ctrl+enter, is what I mean by array in this case, in case there is any
other definition). Or is there a function name of some sort that indicates
to the array the "this is the block you need to evaluate as a contiguous set"?

Second, the starting value of the C cells, if I can avoid having to change
this, is the word "discount" (it is a percentage formatted cell, but I need
it to say this word to instruct the person on what it is). I figured that
because it is being used as a straight multiplicative variable, I needed to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style for all
15 cells),(C written as 15 cells),1)". I was hoping the way it would be
evaluated by the array would be to take each of A and B and do the IF on each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's because of
the disjointed C cells or the attempt at IF or what. I know that when I
create a separate little area on the sheet with a contiguous block of cells
that just references each of the C cells, I can make that contiguous block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured them out,
I figured I'd give this one to the experts to see if I am missing something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris

Hi.
The cell A1 contains:
Here's the shopping list: pig, dog, hamburger, chocolate..., beef. Please
purchase all of them. Thanks!

I would like to extract each item separately to different cells, ie
B1:
=Display1stItem
B2:
=Display2ndItem
....
B?:
=DisplayLastItem + CropText ". Please purchase all of them. Thanks!"

Reminder:
There are different contents with different item list.
Plus the wording of the contents and items are subject to change.
So something like: MID(A1, 24, 5) is not preferred.

Probably they can figure out which word to extract by pattern.
In my case, when the list starts, it must start with colon (.
For each item, comma (,) is used to separate each of them.

So it would be if a function manages to work like the following:
.... ...: pig, cow, button. ...

.... Read (. the extracting starts -->
Read pig --> Read (,) --> the item is extracted.
Read cow --> Read (,) --> the item is extracted.
Read button --> Read (.) --> the item is extracted, and stop reading after that
fullstop(.)

How to do?

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

This code by Myrna Larson will do it (read the directions).

Option Explicit

Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
'
' Posted by Myrna Larson
' July 25, 2000
' Microsoft.Public.Excel.Misc
' Subject: Combin
'
'
'Since you asked, here it is. It is generic, i.e. it isn't written
specifically
'for a given population and set size, as yours it. It will do permutations
or
'combinations. It uses a recursive routine to generate the subsets, one
routine
'for combinations, a different one for permutations.

'To use it, you put the letter C or P (for combinations or permutations) in
a
'cell. The cell below that contains the number of items in a subset. The
cells
'below are a list of the items that make up the population. They could be
'numbers, letters and symbols, or words, etc.

'You select the top cell, or the entire range and run the sub. The subsets
are
'written to a new sheet in the workbook.
'
'

Sub ListPermutations()
Dim Rng As Range
Dim PopSize As Integer
Dim SetSize As Integer
Dim Which As String
Dim N As Double
Const BufferSize As Long = 4096

Set Rng = Selection.Columns(1).Cells
If Rng.Cells.Count = 1 Then
Set Rng = Range(Rng, Rng.End(xlDown))
End If

PopSize = Rng.Cells.Count - 2
If PopSize < 2 Then GoTo DataError

SetSize = Rng.Cells(2).Value
If SetSize > PopSize Then GoTo DataError

Which = UCase$(Rng.Cells(1).Value)
Select Case Which
Case "C"
N = Application.WorksheetFunction.Combin(PopSize, SetSize)
Case "P"
N = Application.WorksheetFunction.Permut(PopSize, SetSize)
Case Else
GoTo DataError
End Select
If N > Cells.Count Then GoTo DataError

Application.ScreenUpdating = False

Set Results = Worksheets.Add

vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
ReDim Buffer(1 To BufferSize) As String
BufferPtr = 0

If Which = "C" Then
AddCombination PopSize, SetSize
Else
AddPermutation PopSize, SetSize
End If
vAllItems = 0

Application.ScreenUpdating = True
Exit Sub

DataError:
If N = 0 Then
Which = "Enter your data in a vertical range of at least 4 cells. " _
& String$(2, 10) _
& "Top cell must contain the letter C or P, 2nd cell is the number " _
& "of items in a subset, the cells below are the values from which " _
& "the subset is to be chosen."

Else
Which = "This requires " & Format$(N, "#,##0") & _
" cells, more than are available on the worksheet!"
End If
MsgBox Which, vbOKOnly, "DATA ERROR"
Exit Sub
End Sub

Private Sub AddPermutation(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0)

Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Static Used() As Integer
Dim i As Integer

If PopSize 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
ReDim Used(1 To iPopSize) As Integer
NextMember = 1
End If

For i = 1 To iPopSize
If Used(i) = 0 Then
SetMembers(NextMember) = i
If NextMember iSetSize Then
Used(i) = True
AddPermutation , , NextMember + 1
Used(i) = False
Else
SavePermutation SetMembers()
End If
End If
Next i

If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
Erase Used
End If

End Sub 'AddPermutation

Private Sub AddCombination(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0, _
Optional NextItem As Integer = 0)

Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Dim i As Integer

If PopSize 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
NextMember = 1
NextItem = 1
End If

For i = NextItem To iPopSize
SetMembers(NextMember) = i
If NextMember iSetSize Then
AddCombination , , NextMember + 1, i + 1
Else
SavePermutation SetMembers()
End If
Next i

If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
End If

End Sub 'AddCombination

Private Sub SavePermutation(ItemsChosen() As Integer, _
Optional FlushBuffer As Boolean = False)

Dim i As Integer, sValue As String
Static RowNum As Long, ColNum As Long

If RowNum = 0 Then RowNum = 1
If ColNum = 0 Then ColNum = 1

If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
If BufferPtr > 0 Then
If (RowNum + BufferPtr - 1) > Rows.Count Then
RowNum = 1
ColNum = ColNum + 1
If ColNum > 256 Then Exit Sub
End If

Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
= Application.WorksheetFunction.Transpose(Buffer())
RowNum = RowNum + BufferPtr
End If

BufferPtr = 0
If FlushBuffer = True Then
Erase Buffer
RowNum = 0
ColNum = 0
Exit Sub
Else
ReDim Buffer(1 To UBound(Buffer))
End If

End If

'construct the next set
For i = 1 To UBound(ItemsChosen)
sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
Next i

'and save it in the buffer
BufferPtr = BufferPtr + 1
Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub 'SavePermutation

--
Regards,
Tom Ogilvy

"Josh" > wrote in message
...
> How can I take, say, number 1 through 10 and output the actual
> combinations/permutations, not just the result of the combination
function?
>
>

I have a problem getting another XLS file to open from a hyperlink within a
XLS file.

I have a workbook containing several worksheets of business processes. Each
of these worksheets contain hyperlinks to the same related external documents
(doc, xls, ppt etc), i.e. single file linked to from multiple places. Each of
these external documents is distributed around our network and are subject to
being moved about. As an external document may be referenced from upward of
20 locations within the XLS, any changes to the target document causes a
large maintenence effort. So I included an index worksheet which contains 3
columns. One for the hyperlink display text, one for the hyperlink path and
one containing the hyperlink formula. I can now copy the 3rd cell to the rest
of the worksheets where required. The hyperlink path points to a single
(fully quilified) directory containing shortcut files (.lnk) to the external
documents.

On worksheet "Index"
A1 TextToShow
B1 ServernameSharenameshortcutfilename.lnk
C1 =HYPERLINK(Index!$A$1,Index!$B$1)

Thus I can amend the shortcut file to point at a new location should the
target file be moved or renamed etc. I can also I update the path and/or text
on the index worksheet and all the other hyperlinks in the workbook are
updated automatically.

This has worked fine for over a year, and still does for all but XLS files.
Word DOC, powerpoint PPTand even PDF load up with no problem, but an XLS just
results in turning the mouse pointer to a "timer" but still allowing the
normal "arrow" functionality. The hyperlinked XLS file never displays.

If the hyperlink path is pasted into the Start>Run dialogue, then the file
is loaded aok!

This used to work fine for XLS files. Unfortunatley, the last time that I
can verify that ths worked without the XLS issue, is back in October 2005. I
suspect a security settings update has caused this but can't find anything.

Other points:
This problem manifests itself on all our machines.
The standard warning message about possible virus infection from this file
type is displayed (only occurs if EXCELL has located the target file)
Windows Task Manger Processes tab shows that EXCEL memory increases to hold
the file
Windows Task Manger Applications tab does NOT show the called XLS file
PCs are running WinXP Pro, Office 2003, both with latest updates as of
08/02/2006

Anyone got any ideas.

Thanks in advance

WILLING TO PAY $25 to $40 for SOLUTION

I am building a workbook in Excel 2007 which has a dashboard page that will display a number of charts and graphs. These charts and graphs will be dynamic in nature, with the series being built using Named-Ranges built with the OFFSET function. The data set consists of a number of data elements entered per day, by employee.

I successfully built an example graph that does the comparison of two series for each of the 30 or so different data elements that are entered for the particular data set. The data set may start on a random date and may end on a random date (different employees my start or stop work on random dates). I have drop down lists that correctly build the Named Ranges for the "Element to Chart", the "Month to Chart" and the "Month for Comparison".
*note: there is a slight issue here with this example graph in that the first number in the data series for the X axis is being evaluated as a "0" instead of what the named range actually shows, so the series is 'offset' by one day.

So this example chart is very close to doing the job perfectly.

My plan is to use multiple worksheets in a single workbook that allow for multiple employees. Then to build an employee list based on the number of 'employee data input worksheets' which also contain the employee's name.

I have correctly built a dynamic range, and a drop down list that lists the employee names. What I am trying to do is have this list (call it the "Employee to Chart" list) work in conjunction with the aforementioned controls (Element to Chart, Month to Chart, Month for Comparison) such that the dynamic lists and ranges that are displayed in them are specific to the data that is in the 'employee's worksheet.

In other words, even though the data elements are all the same for each employee, the work dates and thus the time frames available for comparison will be different. So as I choose the employee, the months for comparison lists have to be built by the specific data in the employee specific worksheet, as does the data set that will be displayed in the graph.

Currently the workbook structure is Ratios.xlsx - Dashboard(worksheet), Data-Emp1(worksheet),Data-Emp2(worksheet), etc.

I can send the speadsheet if anyone can help.

I'm trying to do a search to determine if any cells contain references to
other worksheets in the same workbook. For instance, if I've got a worksheet
named "Daily" and another worksheet named "YTD", I may have a function like
this:

=MAX(Daily!A2:A999)

I want to identify that cell as referring to another worksheet.

I have a search feature that currently looks for linked worksheets according
to one of three strings (strSearch is a String variable containing the name
of the worksheet I'm searching for):

strSearch & "'"
strSearch & "!"
strSearch & ","

I can't use wildcards (strSearch & "*") because I have other cells that link
to entirely different WORKBOOKS, that may have the word "Daily" as part of
the path (does that make sense)?

Right now I'm executing three different searches, as shown above.

Set objFind = objWorksheet.Find(strSearch & "'", LookIn:=xlFormulas).
etc.

Is there a way I can combine these three parameters into one search?
Something like strSearch & "'" OR strSearch & "!" OR strSearch & ","

I hope this makes sense. TIA.

--
Hmm...they have the Internet on COMPUTERS now!

Hi

I have several macros associated with a worksheet, these macro's perform various simple functions, eg. insert rows, move rows up/down etc.

What I would like is to know how to word error checking so that

Rows cannot be moved above Row 7. (Rows 1-6 contain headings so I do not want a row 7 and Row 6 switched by running this macro.
Rows 1-6 cannot deleted.

Something along the lines of "If Activecell < A7 Then ErrorMessage" etc.

Any ideas?

Apologies if not clear.

Regards
Andy

After a lot of trial and error I came up with a solution for
reformatting a chart such that the inside dimensions are exactly the
ones you defined, e.g. a square of 70 x 70 mm, and no unecessary white
space around it.

For the cases I needed (only XY-scatter) it worked fine but do not be
surprised if miraculous things happen when you try it on your own
charts; a lot of surprising phenomena, or hidden added intelligence if
you like, in the charting world!

I end up with some 530 lines of VBA; too long for this message. To get
an impression of what you may expect I added the comments of the module
below. Please mail me for getting an example sheet containing the code.
Replace xyz with jwe in the published address.

Question:
What am I to do to make it a tool that is available also to other
workbooks like AddIns for functions? (I have only experience (quite a
lot actually) with writing AddIns in PASCAL into an xll library.)

Thank you for the suggestions in this news group.
Janwillem

P.S. I am from the ALGOL-60 days and have ever since made an effort of
not using BASIC. I would therefore not be surprised when some of you can
improve considerably on the coding.

'Macros to resize a chart such that the
'1) "PlotArea proper" has exactly defined dimensions
'2) The chart area tightly fits the plot and text elements of the plot

'Janwillem van Dijk
'August 2005
'email: jwe dot van dot dijk at hccnet dot nl

'Defines:
'Sub SetInsidePlotArea()
'Sub MoveToBottomLeft()
'Sub FitChartAreaToPlot()
'Sub ShowPlotDimentions()
'Sub SaveAsTiff()

'Suggested way of doing things:
'1) Create the plot as an embedded chart as usual
'2) Make the ChartArea sufficiently large
'3) Add all optional elements to the plot
'4) Do all the formatting that affect sizes such as the placing of legends
' and choosing fonts and font sizes
'5) Call SetInsidePlotArea entering the width and height in cm as an array
' e.g. {7,7} for a plot with a square inside plot area of 7 cm (198 X
198 pt)
'6) Place all additional element at the correct position
' text boxes might sometimes need resizing
'7) Call MoveToBottomLeft to move all plot elements to the bottom left
corner of the chart area
' and check layout
'8) Call FitChartAreaToPlot to reduce the size of the ChartArea such that it
' nicely fits the plot.
'The plot is now shaped such that it can be "Edit/Copy" and
' "Edit/Paste Special/As Picture (Enhanced Metafile)" into Word or
Powerpoint
' having the intended dimensions and without unnecessary "white"
around the figure

'Charts with TextBoxes that link to a formula instead of containing
literal text
' tend to pose problems which can be dealt with by afterwards slightly
enlarging
' the chart area and fine tuning the positions of the TextBoxes and than
' re-applying SetInsidePlotArea() without FitChartAreaToPlot()

'Printing from Excel results in sligtly wrong dimensions and aspect ratio's
'Copy and paste as EMF into PowerPoint or Word gives almost exact results,
' as GDI metafile in OpenOffice gives a larger picture but aspect
ratio is preserved

'Partly based on suggestions made on microsoft.public.excel.charting
' on my question "square chart insidewidth insideheight" 30 July 2005
'This in particular the suggestion to use
' ExecuteExcel4Macro("FORMAT.SIZE(" & w & "," & h & ")")
' to set the dimensions of the inside PlotArea

SUMMARY:

I need a simple function to compare the text in two cells on the same row and deduce when they are quite similar. I'd like the function to generate a score or similar statistic describing the percent or degree of similarity. Alternatively, I'd like the function to allow me to set a level of similarity I consider acceptable and return one of the two values.

BACKGROUND:

I provide property data to the insurance industry. Much of my data comes from Central Appraisal District (CAD) listings which are public information.

Homeowner's insurance is written only for owner occupied homes, wherease, other policy types are written for rental, leased, renovating or foreclosed properties. Among these homeowner's policies generate the highest commission for the agent. So, owner occupied data is what's in demand.

CADs list both the owner's mailing address and the property street address. If these two match, we assume the owner lives on the site and include the property record in the data we provide to agents.

Unfortunately, the CADs aren't perfect. About 10% of time the data entry clerks don't type in the same information for both data fields and are off by a character or two. So, about 10% of the otherwise qualified properties are unnecessarily deleted from our data offerings.

CADs take great care to make sure the mailing address is correct because they mail the tax bills to that address. The property address accuracy is not considered important by the CADs.

AIM:

So, what I'd like to do is to compare two non-identical but similar addresses and calculate a percent or degree of similarity between the two. If they are "sufficiently" similar (perhaps within 5 - 10%) I'd like to return the mailing address to the cell containing the function or I'd like to return the words (Owner Occupied).

CONSTRAINTS:

The function must be small and efficient as we routinely work with sets of property records that number near a million rows with 25 or more columns. Resource hogs won't work in this situation.

THANKS:

Any assistance would be greatly appreciated.

LongFisher

I am doing this on another forum, too. I have some general knowledge of the worksheet functions in Excel, but I have begun work on one too big for me to manage well without some extra help. So here goes...

I'm working on giving Chinese vocabulary lessons in supplement to grammar tutorials.

However, in order to avoid errors in other language books books, I only want to introduce so many characters at a time into the vocabulary, but at the same time, only the words that contain the more frequent characters first.

I found that giving an example helps best.

Let's say that I have a vocabulary list in Column X:

幸好
能幹
好幾
幾個
幾乎沒有
幾何學
幾天來
幾個小時
好幾年
幾年來
有序

Now, I want to filter out all of the results unless they contain ONLY THESE CHARACTERS: 好, 幾, 年, 來. The characters, in order of frequency, are put in column Y2:Y2551.

If the filter is done correctly, then it should make something in Column Z:

好幾
好幾年
幾年來

Now, if I tried doing this long-hand in Column Z, it would read something like this:
=IF(NOT(OR(ISNUMBER(SEARCH(跑,X1)),ISNUMBER(SEARCH(工,X1)),ISNUMBER(SEARCH(忙,X1)),ISNUMBER(SEARCH(相,X1 )),ISNUMBER(SEARCH(光 ,X1))...),X1,"")

The problem is that with only six characters, the equation would be ridiculously big, with 2496 of those ISNUMBER(SEARCH(Character in Cell Y,Cell X)). I don't think I could even fit 2500 words into the equation without causing an error.

Is there a method that allows me to enter multiple rows into the SEARCH() function? Is there any other potential shortcut that could get a product as I need it?

I have three separate 15 cell blocks of cells. I need to do a
SUM(A/(1+B)*C), where each letter is the blocks of cells. Problem is
two-fold. First, while A and B are contiguous blocks, C is not (it's all one
column, but each cell has three others in between them). So the way I was/am
trying to write C into the formula was by doing, in parentheses, each cell
separated by commas, figuring that creates the 15 cell block I need. So one
question is whether that's the right way to indicate in an array formula
(shft+ctrl+enter, is what I mean by array in this case, in case there is any
other definition). Or is there a function name of some sort that indicates
to the array the "this is the block you need to evaluate as a contiguous set"?

Second, the starting value of the C cells, if I can avoid having to change
this, is the word "discount" (it is a percentage formatted cell, but I need
it to say this word to instruct the person on what it is). I figured that
because it is being used as a straight multiplicative variable, I needed to
make sure it was a number, so in place of "C", I tried using an
"if(isnumber(C written as previously mentioned in a cell-comma style for all
15 cells),(C written as 15 cells),1)". I was hoping the way it would be
evaluated by the array would be to take each of A and B and do the IF on each
of C, and then put that result into the SUM.

Anyway, it's giving me a VALUE answer, and I don't know if it's because of
the disjointed C cells or the attempt at IF or what. I know that when I
create a separate little area on the sheet with a contiguous block of cells
that just references each of the C cells, I can make that contiguous block
work properly in the formula. But since I am all about trying to be
efficient, and have been marveling at arrays since I first figured them out,
I figured I'd give this one to the experts to see if I am missing something
that could help me do this calc in a self-contained way.

Thanks for any insight.

--
Boris

I have a list of tasks in a master table that I split out by function (AP, AR, purchasing, etc.). I have columns set up for each function, which I then tickmark with a "1" indicating that the task in that row corresponds to that function

I have a script that takes a specific function (say, purchasing) and places it in a separate worksheet. It copies specific information from about each task to the co-responding worksheet.

Everything except for the "% Complete" works fine, every time. The "% Complete", however, gives me different numbers depending on how I sort the master table (ex, ascending vs descending, based on date, ID #, etc.). In other words, task "A" might show as 100% complete if the master table is sorted ascending by date, but 0% complete if sorted descending by date--even though everything else about the task (the name, dates, description, etc.) all get copied correctly. I'm at a loss as to why that is.

Here's a snippit of the code:

Sub PURTasks()

Set m = Sheets("Master Project Plan") 'Worksheet that contains Master Task List
Set t = Sheets("PUR") 'Target worksheet that contains individual function tasks

Dim x
Dim y As Integer
Dim z As Integer

x = "AP" 'Row in MPP worksheet that contains tickmarks for specific function
y = 3 'Starting row in MPP worksheet (after headers)
z = 7 'Starting row in target worksheet (first row after headers)

t.Activate
t.Range("A7:M1000").Clear 'readies the target worksheet

Do While IsEmpty(m.Range("A" & y)) = False 'Checks to see if a task exists and ends the script if one
doesn't--there are no gaps between tasks

    If m.Range(x & y) = "1" Then
        t.Range("B" & z).Value = m.Range("A" & y).Value 'Phase
        t.Range("C" & z).Value = m.Range("B" & y).Value 'WBS
        t.Range("D" & z).Value = m.Range("D" & y).Value 'ID #
        t.Range("E" & z).Value = m.Range("F" & y).Value 'Task Name
        t.Range("F" & z).Value = m.Range("G" & y).Value 'Start
        t.Range("G" & z).Value = m.Range("H" & y).Value 'Due
        t.Range("H" & z).Value = m.Range("J" & y).Value 'Day Type
        t.Range("I" & z).Value = m.Range("K" & y).Value 'Required Attendees
        t.Range("J" & z).Value = m.Range("L" & y).Value 'Assigned To
        t.Range("K" & z).Value = m.Range("S" & y).Value 'Room / Address
        t.Range("L" & z).Value = m.Range("T" & y).Value 'Equipment
        t.Range("M" & z).Value = m.Range("E" & y).Value '% Complete <---this is the only one
that comes out wrong.
        z = z + 1
    End If
    
    y = y + 1

Loop

End Sub
Like I said, I'm at a loss as to what is happening. It seems like it should pull correctly since it's the same code that is used to pull everything else.

I can manually observe that nothing changes to the task when changing the sorting. In other words, task "A" on the master list will show 0% complete, irregardless of how the table is sorted, however it may get copied as 100% or 0%, depending on how the table is sorted.

I have a Column which contains about 425 fields. Of the 425, there are only 7 or 8 unique values, actually they are words (names). Another column on the same worksheet has amounts which I would like to do this with......

I need to create a a new sheet with totals of each name. In other words.....if I have:
Postage 27
Tech Support 49
Tech Support 57
Postage 32
Maintenance 77

I need to have a formula or function compute the total for each category. Please help! Thanks!

Hi

Attached is a WB that contains some columns and rows of data.

Based on some links below from KYLE123 and others

'======================
' History
'======================
'12/19/2011
'From VTHOKIE11 referenceing Kyle123 previous post
'http://www.excelforum.com/excel-programming/806281-jquery-google-visualizations-intro-tag-word-cloud-generator.html

'12/6/2009
'knaledge with a response from Kyle123
'http://www.excelforum.com/excel-programming/804464-tag-clouds-using-free-form-text-entry-single-column.html

'4/22/2009
'From SweetRevalation
'http://www.excelforum.com/excel-programming/680810-create-tag-cloud-in-vba-possible.html

I modified (googled) code to write Excel data to Web via VBA. In effect, VBA is used
to create HTML tags and build javascript functions that incorporate Excel data.

I was wondering if anyone here was down-stream of this and (SOMEHOW) writing
the Web table values back to excel? Almost as if there was an EXCEL - AddEventListener
thingie.

As is - the code takes advantage of a Google API which raises the question
does Microsoft have an equivalent?

Please see attached WB

EDIT: I am aware of Visual Studio, etc. but (as is right now)
there is no need for ADO or ADO.NET and I am trying to find
a way to keep this as "light-weight" as possible.

EDIT #2:
Forgot. You will need a sub-directory called OUT under the directory
that the workbook is in.

regards
John

I am trying to write a macro that concatenates the content of an unknown number of rows in to a single cell and separates the values with a CHAR(10). The groups of data I want to concatenate are separated by a blank row so I imagine that the Macro will need to be terminated via a check for two blank rows on the trot or the presence of a terminating value (in my feeble attempts so far I have used the word 'end' as the terminator). Its complicated further by the fact that some of the cells that need to be merged contain date & time values so they will need to be converted to text.

Below is the skeleton of my initial attempts at getting my head around the algorithm but the main function of actually concatenating the cells is missing.

I have also attached an example worksheet that shows the original data and the desired output (ignore the cell merging, that’s just to make it easier to read).

I hope this makes some kind of sense... Thanks in advance for your help :-)

Stu

Sub Merge_Progress()

selectRow = 1
selectColumn = 1

Cells(1, 1).Select

Do While Not ActiveCell = "end"

    If ActiveCell = "" Then
        selectRow = selectRow + 1
        Cells(selectRow, selectColumn).Select
    Else
        Do While Not ActiveCell = ""
            'Add the content of the current cell to the concatenated cell
            selectRow = selectRow + 1
            cells(selectRow, selectColumn).select
        Loop
    End If
Loop
End Sub


I have a problem getting another XLS file to open from a hyperlink within a
XLS file.

I have a workbook containing several worksheets of business processes. Each
of these worksheets contain hyperlinks to the same related external documents
(doc, xls, ppt etc), i.e. single file linked to from multiple places. Each of
these external documents is distributed around our network and are subject to
being moved about. As an external document may be referenced from upward of
20 locations within the XLS, any changes to the target document causes a
large maintenence effort. So I included an index worksheet which contains 3
columns. One for the hyperlink display text, one for the hyperlink path and
one containing the hyperlink formula. I can now copy the 3rd cell to the rest
of the worksheets where required. The hyperlink path points to a single
(fully quilified) directory containing shortcut files (.lnk) to the external
documents.

On worksheet "Index"
A1 TextToShow
B1 ServernameSharenameshortcutfilename.lnk
C1 =HYPERLINK(Index!$A$1,Index!$B$1)

Thus I can amend the shortcut file to point at a new location should the
target file be moved or renamed etc. I can also I update the path and/or text
on the index worksheet and all the other hyperlinks in the workbook are
updated automatically.

This has worked fine for over a year, and still does for all but XLS files.
Word DOC, powerpoint PPTand even PDF load up with no problem, but an XLS just
results in turning the mouse pointer to a "timer" but still allowing the
normal "arrow" functionality. The hyperlinked XLS file never displays.

If the hyperlink path is pasted into the Start>Run dialogue, then the file
is loaded aok!

This used to work fine for XLS files. Unfortunatley, the last time that I
can verify that ths worked without the XLS issue, is back in October 2005. I
suspect a security settings update has caused this but can't find anything.

Other points:
This problem manifests itself on all our machines.
The standard warning message about possible virus infection from this file
type is displayed (only occurs if EXCELL has located the target file)
Windows Task Manger Processes tab shows that EXCEL memory increases to hold
the file
Windows Task Manger Applications tab does NOT show the called XLS file
PCs are running WinXP Pro, Office 2003, both with latest updates as of
08/02/2006

Anyone got any ideas.

Thanks in advance


No luck finding an answer? You could always try Google.