Replies

Hello Wassim,

There is an additional check you could make if you want to make the effort.
You could verify the entry after the last dot by using a lookup table or array containing all the country codes plus the conventional endings… com, net, gov, edu etc.
I complied a list of 256 country codes to satisfy my curiosity about where people were posting from.
You can do the same by melding lists from…

http://www-old.ics.uci.edu/pub/websoft/www…untry-codes.txt
http://www.iso.org/iso/en/prods-services/i…t-en1-semic.txt

There are other very similar lists available by doing a google search for “country codes”.

Hope this helps.

Jim Cone
San Francisco, CA

Further to Legare’s post…

If you put the “Pricing Table A16:B19” in ascending order then your formula works.
As follows:

12 Pallets 5
3 Pallets 2
3-6 Pallets 3
6-12 Pallets 4

Regards,

Jim Cone
San Francisco, CA

Ricky,

The attachment show one approach. It has formulas added in Row 1, 2 and 4.
The formulas are hidden with the custom number format: “;;;” (three semicolons).
Andrew’s formula (adjusted) is used in Row 3.
Be aware that if the sales are zero, then a 0 must be filled in. Tthe Count function depends on this.

Regards,

Jim Cone
San Francisco, CA

This ought to work…

1 Click the chart.
2 On the Tools menu, click Options, and then click the Chart tab.
3 Select the Plot visible cells only check box.

Regards,

Jim Cone
San Francisco, CA

‘This should get you started…

‘Paste this in a module in the workbook in question, click inside the pasted text and press the F5 key.
‘Adds a new sheet to the active workbook.
‘Lists all Names in the workbook and what the name refers to.
‘———————————————————–
Sub FindAndListNames()
Dim NewSheet As Worksheet
Dim WBname As Name
Dim N As Long

N = 3
Set NewSheet = Worksheets.Add(after:=Sheets(Sheets.Count), Count:=1)
On Error Resume Next
NewSheet.Name = “Names List”
On Error GoTo 0
For Each WBname In ActiveWorkbook.Names
NewSheet.Cells(N, 1).Value = WBname.Name
NewSheet.Cells(N, 2).Value = “‘” & WBname.RefersTo
N = N + 1
Next ‘WBname
NewSheet.Columns(“A”).AutoFit
Set NewSheet = Nothing
End Sub
‘———————————————————–

Regards,

Jim Cone
San Francisco, CA

Hey all,

You’ve got him outnumbered, but he is not alone.
I have both Excel 97 and Excel 2002 installed on my system.
In my Excel 97 – floating toolbars do not move when adjacent rows are selected.
In my Excel 2002 – they move with the selected rows.

An interesting find was this quote from XL2002 help:

“Turn on or off transparent floating toolbars
On the View menu, point to Toolbars, and then click Customize.
Click the Options tab.
Under Other, clear or select the Transparent floating toolbars check box. ”

Of course the check box they refer to is not there.

Regards,

Jim Cone
San Francisco, CA

Ronny,

If you are up to a little VBA the following code will work.
‘———————————————————–
‘Random numbers entered into Range(“A2:B11”) – 2 x 10 grid
‘Count of pairs will show in Range(“D2:M11”) – 10 x 10 grid

Sub AddCountToTable()
Dim RandNum1 As Long
Dim RandNum2 As Long
Dim Rng As Range

Range(“D2:M11”).ClearContents
For Each Rng In Range(“A2:A11”)
RandNum1 = Rng.Value
RandNum2 = Rng(1, 2).Value
With Cells(RandNum1 + 1, RandNum2 + 3)
.Value = .Value + 1
End With
Next
Set Rng = Nothing
End Sub
‘———————————————————–
The code should be adjusted if you change the location of the tables.

Regards,

Jim Cone
San Francisco, CA

Kerry,

Do you want to add a number to each added sheet – so that the number added to sheet 4 would be 1 and the number added to sheet 5 would be 2, etc?
If so, add the following after the line that reads: oNewSheet.Range(“P26”).Formula = “='” & strPrevSheet & “‘!P28”.
Note: The last character is a capital i

‘—————————————————
oNewSheet.Range(“P43″).Value = I
‘————————————————–

Also, if you really want the instruction sheet to show when the code completes…
Move the line: ” Worksheets(1).Activate” to the end of the Sub.

Regards,

Jim Cone
San Francisco, Ca USA

Sam,

This ought to do it….

Sub WhichRowAtBottom()
Dim LastVisRow As Long

With ActiveWindow.VisibleRange
LastVisRow = .Rows(.Rows.Count).Row
End With
MsgBox LastVisRow
End Sub

Regards,

Jim Cone
San Francisco, CA

Jim,

…
HOWTO: Install an Excel Add-In (XLA or XLL) with Automation
The information in this article applies to:
Microsoft Excel 2000
Microsoft Excel 97 for Windows
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Excel 2002…

http://”%5Dhttp://support.microsoft.com/?kbid=280290″>http:// http://support.microsoft.com/?kbid=280290[/url]

Regards,

Jim Cone
San Francisco, CA

Joe,

To add to Deb’s post…
When installing / using multiple versions of MS suites or applications:

1. Install the oldest version first
2. Install in separate directories or partitions
3 If you want to keep your start menu shortcuts for all versions then rename the older ones before installing the newer version.
4. Only one version of Outlook can be installed on a single operating system.
5. Do a custom install and specify when asked to not overwrite/replace the older versions.
6, You should specify the version of Excel you want used to open files by registering it in ‘Start – Run’.
(Enter the full file name of Excel, note that /regserver is outside the quote marks.)

“C:Program FilesMicrosoft OfficeOfficeExcel.exe” /regserver

The above will register Excel 97 as the default on most systems – change the file path / file name as appropriate.

7. Do Not run multiple versions at the same time. Close XL 97 before opening XL 2000 and vice versa.
8. MS references follow in no particular order:

290576
218861
214388
292584
210391

Regards,

Jim Cone
San Francisco, CA

Another, less well known utility to list or break links is the one I wrote: “Locate Links”.
It creates a list of all links and provides options to delete them one by one or all at once.
Very simple to use. It is an Excel 97 (or later) Add-in and comes with instructions on how to install the Add-in.
If interested, I will Email it. (124KB)
The attached word document contains the instructions.

Jim Cone
San Jose, CA

Andrew,

The following is a VBA solution, which you might or might not be able to use.
It is not a practical solution if the range of names is several thousands rows long. (Too much time required)
A non-VBA solution would be to use the “V-Lookup” function.
Code follows:

‘——————————————————————————–
‘Finds a specific name in specified range.
‘Written by Jim Cone 11/13/2002

Function FindTheRightValue(NameToFind As String) As String
Dim oCell As Range
Dim NameRange As Range

Set NameRange = Range(“A1:C5″)
NameToFind = LCase$(NameToFind)

For Each oCell In NameRange
If LCase$(oCell.Text) = NameToFind Then
‘Assumes the look up value is always in column 4
FindTheRightValue = Cells(oCell.Row, 4).Text
Exit For
End If
Next
If Len(FindTheRightValue) Then MsgBox FindTheRightValue _
Else MsgBox NameToFind & ” was not found.”

Set oCell = Nothing
Set NameRange = Nothing
End Function

‘Call the function.
Sub WhereIsIt()
FindTheRightValue (“Mary”)
End Sub
‘————————————————————————–

Jim Cone
San Jose, CA

Hello,

Your post said you wanted to do this in VBA, so I have created an answer that might work for you.
It works for me, but since you did not post a sample of your code, its hard to tell…
——————————-

Sub ChangeTheLabelValues()
Dim WorkingChart As Chart
Dim SeparateLabel As DataLabel
Set WorkingChart = ActiveSheet.ChartObjects(1).Chart

For Each SeparateLabel In WorkingChart.SeriesCollection(4).DataLabels
SeparateLabel.Text = CLng(SeparateLabel.Text) 10
Next

Set SeparateLabel = Nothing
Set WorkingChart = Nothing
End Sub

————————————————

Jim Cone
San Jose, CA

John,

A resource: XL VBA help – “Using ActiveX Controls on Sheets”

Jim Cone
San Jose, CA