| Article | Body |
| 424 |
thanks for your help...will check it out!
cheers
"" <DavidH@OzGrid.com wrote in message
news:L6zQ8.24$ye5.40110@vicpull1.telstra.net...
Forgot to mention that "a difference of around 10,000,000" may or may not
be
considerable! The only way to tell is to get the % difference.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"" <DavidH@OzGrid.com wrote in message
news:r4zQ8.23$ye5.40152@vicpull1.telstra.net...
| Hi Pauline
|
| I am certainly no expert on the 2 so I will take the cheats way and
refer
| you to the methods used in the 2. The trend uses a method know as "The
least
| squares method" You can read the technical details on this here:
| /math/leastsquares/leastsquares.cfm. On this site
you
| should also find some explanation of the 2. There is also an "Ask an
Expert"
| page that could help you.
|
| There will no doubt someone in here that knows the technical difference,
but
| unfortunately there may also be some that only think they know, like me
:o)
|
| Neither is walk in the park!
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
|
|
|
|
| "Pauline's News Reader" <pauline@icfrith.com.au wrote in message
| news:lEyQ8.21$ye5.38955@vicpull1.telstra.net...
| | I have created a table for sales history for the past 5 years eg
| | 96,97,98,99,2000. with appropriate dollar figures.
| |
| | When I "Trend" for 2001 I get X value
| |
| | When I "Forecast" for 2001 I get Y value.
| |
| | (a difference of around 10,000,000.)
| |
| | What is the difference between the two and why should I use one over
the
| | other.
| |
| | Clarification of the two would be much appreciated.
| |
| | cheers.
| |
| |
|
|
|
| 433 |
Hi AussieDave
Right click on the sheet name tab, select "View Code" and paste in this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$A$1" And IsEmpty(Target) Then
Select Case Target.Interior.ColorIndex
Case 3
Target.Interior.ColorIndex = 46
Case 46
Target.Interior.ColorIndex = 10
Case Else
Target.Interior.ColorIndex = 3
End Select
End If
End Sub
--
**Possible solution has NOT been tested**
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
"AussieDave" <dave@optushome.com.au wrote in message
news:qcBQ8.57670$_j6.3018857@bin3.nnrp.aus1.giganews.com...
| When clicking on a specific empty cell, I need to color it red after the
| first click, orange after the second and (you guessed it!!) green after
the
| third.
| I haven't got a clue, except I guess it's tied up with Click Events.
Any
| help much appreciated. xl97 by the way.
| --
| Dave from Down Under
| --
|
|
|
| 438 |
Hi Scott
try:
=IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"")
--
"Scott Cardais" <Scott@Cardais.com wrote in message
news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10...
| A B C D M N O
| 1 8/1/02 9/1/02 10/1/02
| 2 Account 1
| 3 Close Month 7/1/2002
| 4 License Fee $20,000
| 5 Annual Maint $24,000 2,000 2,000 2,000
| 6 Installation $23,000
|
| Formula in Row 5, column E and beyond =IF
| (M$1$D3,$D5/12,"")
|
| In the sample shown above, the value of $2,000 is placed
| in cells M5, N5, etc. since the date in M1 is greater
| than the date in D3. If the date in D3 is changed to
| 8/1/02, the 2,000 entries are shifted right one month.
|
| This is exactly what I want but if no date is entered in
| D3, I want the nothing or zero entered in row 5 but the
| formula shown above doesn't give me that result. (It puts
| 2,000) in every cell in Row 5)
|
| I suspect that I need nested IF statements using the
| ISBLANK function but I'm not having any success with this.
|
| For what it's worth, this is a cash forecasting worksheet
| that lists all pending sales and their forecasted close
| dates and revenue projections. Changing the Close Date
| helps us construct different cash positions. Entering no
| date in the Close Month cell would eliminate that
| particular account from the forecast.
|
| Any help would be appreciated.
|
| Scott C.
|
| 440 |
Hi Dave:
I pasted this into my June 02 columns and beyond and it
resulted in entering 2,000 in June even if the close date
was August.
Just to be clear, if the close date is August and the
annual amount is 24,000; I want 2,000 entered in
September and beyond AND if the close date is blank, I
want nothing entered in any months.
Thanks very much for your help.
-----Original Message-----
Hi Scott
try:
=IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"")
--
"Scott Cardais" <Scott@Cardais.com wrote in message
news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10...
| A B C D M N O
| 1 8/1/02 9/1/02
10/1/02
| 2 Account 1
| 3 Close Month 7/1/2002
| 4 License Fee $20,000
| 5 Annual Maint $24,000 2,000 2,000 2,000
| 6 Installation $23,000
|
| Formula in Row 5, column E and beyond =IF
| (M$1$D3,$D5/12,"")
|
| In the sample shown above, the value of $2,000 is
placed
| in cells M5, N5, etc. since the date in M1 is greater
| than the date in D3. If the date in D3 is changed to
| 8/1/02, the 2,000 entries are shifted right one month.
|
| This is exactly what I want but if no date is entered
in
| D3, I want the nothing or zero entered in row 5 but the
| formula shown above doesn't give me that result. (It
puts
| 2,000) in every cell in Row 5)
|
| I suspect that I need nested IF statements using the
| ISBLANK function but I'm not having any success with
this.
|
| For what it's worth, this is a cash forecasting
worksheet
| that lists all pending sales and their forecasted close
| dates and revenue projections. Changing the Close Date
| helps us construct different cash positions. Entering
no
| date in the Close Month cell would eliminate that
| particular account from the forecast.
|
| Any help would be appreciated.
|
| Scott C.
.
|
| 441 |
Dave:
My mistake. Your formula DOES work. I had to make one
small change to reference the proper cell in my
spreadsheet.
Thanks very much.
Scott C.
-----Original Message-----
Hi Scott
try:
=IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"")
--
"Scott Cardais" <Scott@Cardais.com wrote in message
news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10...
| A B C D M N O
| 1 8/1/02 9/1/02
10/1/02
| 2 Account 1
| 3 Close Month 7/1/2002
| 4 License Fee $20,000
| 5 Annual Maint $24,000 2,000 2,000 2,000
| 6 Installation $23,000
|
| Formula in Row 5, column E and beyond =IF
| (M$1$D3,$D5/12,"")
|
| In the sample shown above, the value of $2,000 is
placed
| in cells M5, N5, etc. since the date in M1 is greater
| than the date in D3. If the date in D3 is changed to
| 8/1/02, the 2,000 entries are shifted right one month.
|
| This is exactly what I want but if no date is entered
in
| D3, I want the nothing or zero entered in row 5 but the
| formula shown above doesn't give me that result. (It
puts
| 2,000) in every cell in Row 5)
|
| I suspect that I need nested IF statements using the
| ISBLANK function but I'm not having any success with
this.
|
| For what it's worth, this is a cash forecasting
worksheet
| that lists all pending sales and their forecasted close
| dates and revenue projections. Changing the Close Date
| helps us construct different cash positions. Entering
no
| date in the Close Month cell would eliminate that
| particular account from the forecast.
|
| Any help would be appreciated.
|
| Scott C.
.
|
| 447 |
Hi Dan
You could use the DMAX, this (I think) ignores error values.
--
**Possible solution has NOT been tested**
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
"drwinterton" <daniel_winterton@hk.ml.com wrote in message
news:112fe01c21910$d482aca0$3bef2ecf@TKMSFTNGXA10...
| I am doing some financial calculations. I have excel
| linked to bloomberg, a financial database. Bloomberg
| populates cells C2, D2, and E2 with the 3-month, 6-month,
| and 1-year volatility of a stock. In cell F, I have:
|
| =max(c2,d2,e2)
|
| However, if a certain stock hasn't been around for long
| enough, then bloomberg spits a "Value#" in the
| corresponding cell (for example, a stock has been around 9
| months. It will have a 3- and 6-month volatility, but no
| 1-year volatility)
|
| Apparently, the MAX function does not work if one of the
| cells is "value#". Any suggestions?
|
| Thanks,
| Dan Winterton
|
| 449 |
Just another note that OE comes free with Internet Explorer.
--
"Debra Dalgleish" <dsd@contextures.com wrote in message
news:3D130610.4010104@contextures.com...
| Contact your local Microsoft office, and perhaps they can help you.
|
| /worldwide/
|
| globird wrote:
| in attempting to open excel or outlook express (meaning my
| email through my cable address), I get a window saying
| there's a missing MSO97.DLL file. I was able to download
| this file from Dell phone support, but am still unable to
| open excell. Then I was told to uninstall outlook express
| and reinstall, but I can't find the CD or disk to
| reinstall. How do I solve this? HELP!!!
|
|
| --
|
| Debra Dalgleish
|
| Excel FAQ, Tips & Book List
| /tiptech.html
|
|
| 450 |
Yep, just checked Dan. Just use:
<#VALUE!
As the criteria below your copied heading.
=DMAX(B1:B100,1,D4:D5)
Where B1:B100 is the range to find the MAX, 1 is the position of the Column
to return the MAX from and D4:D5 respectively contain a copy of the heading
in B1 and the criteria <#VALUE!
--
"" <DavidH@OzGrid.com wrote in message
news:NDDQ8.15$3n5.8632@vicpull1.telstra.net...
| Hi Dan
|
| You could use the DMAX, this (I think) ignores error values.
|
|
| --
| **Possible solution has NOT been tested**
|
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
|
|
| "drwinterton" <daniel_winterton@hk.ml.com wrote in message
| news:112fe01c21910$d482aca0$3bef2ecf@TKMSFTNGXA10...
| | I am doing some financial calculations. I have excel
| | linked to bloomberg, a financial database. Bloomberg
| | populates cells C2, D2, and E2 with the 3-month, 6-month,
| | and 1-year volatility of a stock. In cell F, I have:
| |
| | =max(c2,d2,e2)
| |
| | However, if a certain stock hasn't been around for long
| | enough, then bloomberg spits a "Value#" in the
| | corresponding cell (for example, a stock has been around 9
| | months. It will have a 3- and 6-month volatility, but no
| | 1-year volatility)
| |
| | Apparently, the MAX function does not work if one of the
| | cells is "value#". Any suggestions?
| |
| | Thanks,
| | Dan Winterton
|
|
|
| 470 |
Try Bill Manville's add-in Findlink.zip at:
/MVP/Default.htm
or read John Walkenbach's advice:
-walk.com/ss/excel/usertips/tip054.htm
HTH
Jason
Atlanta, GA
-----Original Message-----
Hello,
Could someone tell me how to delete links between
worksheet, please?
I'm assigned a worksheet with links (not hyperlink) on
it.
I have no idea where the links are on the sheet; however,
when I go to Edit/Links, I see about 5 links in there.
I don't want those links any more, but I don't know how
to
delete them.
Thank you for your help.
.
|
| 496 |
Believe there was a typo with the Additional Range.
This applies the filter starting in row 2, (it isn't clear what your intent
is) Possibly Dave meant to add an Offset(1,0) and typed an extra range by
mistake.
Sub Tester7()
If ActiveSheet.AutoFilterMode Then Range("B3").AutoFilter
Range("B3", Range("IO2").End(xlToLeft).Offset(1, 0)).AutoFilter
End Sub
But if the headers are really in row 2, then just deleting the extra range
should work.
Sub Tester7()
If ActiveSheet.AutoFilterMode Then Range("B3").AutoFilter
Range("B3", Range("IO2").End(xlToLeft)).AutoFilter
End Sub
<DavidH@OzGrid.com wrote in message
news:H6RQ8.6$os5.17928@vicpull1.telstra.net...
Hi Steve
I may be missing something here but you could replace you entire procedure
with:
Range("B3", Range("IO2").Range.End(xlToLeft)).AutoFilter
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"stindle" <stindle@yahoo.com wrote in message
news:f4cc01c2195e$ae8fe000$37ef2ecf@TKMSFTNGXA13...
| Sub AutoFilterRange()
| 'find last column used in Row 2
| LastColumn = Range("IO2").End(xlToLeft).Column
| 'select autofilter range on Row 3
| Range("B3:" & LastColumn & "3").Select
| Selection.AutoFilter 'AutoFilter Off
| Selection.AutoFilter 'AutoFilter On
| End Sub
|
| The problem is LastColumn is numeric (16)
| I need LastColumn to be alpha ("P")
|
| Is there a command to handle this?
|
| If I use .Address instead of .Column I could
| strip the "P" but once the database gets past
| column "AA" it would be even more coding.
|
| I understand using Offset but was hoping for
| cleaner/faster code.
|
| Thanks,
| Steve
|
| 511 |
Dave,
That's great, thanks.
Is there a way I can ask for a password in order to run
this macro - if not correct then it exits? Then I could
leave this macro in the workbook and just protect the code.
Rob.
-----Original Message-----
Hi Rob
This code will toggle sheet protection on/off
Sub ToggleProtectContent()
Dim i As Integer
For i = 3 To 50
If Sheets(i).ProtectContents = False Then
Sheets(i).Protect Password:="rob1"
Else
Sheets(i).Unprotect Password:="rob1"
End If
Next i
End Sub
It only checks for Content protection.
--
"Rob Pearce" <rob.pearce@consignia.com wrote in message
news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06...
| I have a workbook with 50 worksheets.
|
| I protect sheets 3 to 50 [all being the same template
for
| data entry] with the same password.
|
| When I want to change some formula/formatting on these
48
| sheets I have to unprotect each sheet individually so
that
| I can eventually group them together to alter.
|
| Would it be possible to have some code that will loop
from
| shhet 3 to 50 and unprotect - the password is rob1 if
| anyone could lend a hand - and conversely then to re
| protect.
|
| I would obviously remove the macros from the workbook
| afterwards!
|
| Thanks,
|
| Rob.
.
|
| 513 |
Thanks very much for your help, now for the next question.
can the time also include the date and decimal parts of a
second? For example 06-22-02 08:17:54.379
,
Chuck
-----Original Message-----
Hi Chuck
You can copy paste special as values the Now() function
over the top of
itself, or use the simple macro:
Push Alt+F11
Go to InsertModule
Paste in the code below
Push Alt+Q and Save
Push Alt+F8
Click "AddTime" then Options
assign a shortcut key
Sub AddTime()
ActiveCell = Time
End Sub
--
"chuck conroy" <cconroy@jps.net wrote in message
news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08...
| I would like to have Excel input the time in this format
| hh:mm:ss
| I have tried two different things.
| 1. The now statement =now() this works fine however the
| next time I enter it the previous time gets updated to
the
| new time. I want each cell to display the exact time
that
| is is entered and then to retain this time.
| 2. I have also tried control shift : this does
display
| a different time in each cell, however it only displays
| the hour and minute, the seconds are always 00 no mater
| how I format the cell.
| Does anyone have any suggestions?
| Thanks,
| Chuck Conroy
.
|
| 516 |
Try
Sub EnterDate_Time()
ActiveCell.Value = Now
ActiveCell.NumberFormat = "mm-dd-yy hh:mm:ss"
End Sub
I don't think you can get excel to record less than a second although it is possible
to enter in a cell..
--
Regards,
Peo Sjoblom
"chuck conroy" <cconroy@jps.net wrote in message news:1249c01c219ff$8b3e5c70$35ef2ecf@TKMSFTNGXA11...
Thanks very much for your help, now for the next question.
can the time also include the date and decimal parts of a
second? For example 06-22-02 08:17:54.379
,
Chuck
-----Original Message-----
Hi Chuck
You can copy paste special as values the Now() function
over the top of
itself, or use the simple macro:
Push Alt+F11
Go to InsertModule
Paste in the code below
Push Alt+Q and Save
Push Alt+F8
Click "AddTime" then Options
assign a shortcut key
Sub AddTime()
ActiveCell = Time
End Sub
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"chuck conroy" <cconroy@jps.net wrote in message
news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08...
| I would like to have Excel input the time in this format
| hh:mm:ss
| I have tried two different things.
| 1. The now statement =now() this works fine however the
| next time I enter it the previous time gets updated to
the
| new time. I want each cell to display the exact time
that
| is is entered and then to retain this time.
| 2. I have also tried control shift : this does
display
| a different time in each cell, however it only displays
| the hour and minute, the seconds are always 00 no mater
| how I format the cell.
| Does anyone have any suggestions?
| Thanks,
| Chuck Conroy
.
|
| 527 |
Here is one approach. This will not re-protect the
sheets. You will have to use the code from the previous
response for that.
Sub Get_Password()
On Error GoTo errPassword
Dim strPassword As String
Dim i As Integer
'*** GET THE PASSWORD FROM USER
strPassword = InputBox("Enter Password", "Unprotection
Password")
'*** TRY TO UNPROTECT THE FIRST SHEET
Sheets(3).Unprotect Password:=strPassword
'*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS
For i = 4 To 50
Sheets(i).Unprotect Password:=strPassword
Next i
Exit Sub
errPassword:
'*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT
MsgBox "You entered and incorrect password!"
End Sub
-----Original Message-----
Dave,
That's great, thanks.
Is there a way I can ask for a password in order to run
this macro - if not correct then it exits? Then I could
leave this macro in the workbook and just protect the
code.
Rob.
-----Original Message-----
Hi Rob
This code will toggle sheet protection on/off
Sub ToggleProtectContent()
Dim i As Integer
For i = 3 To 50
If Sheets(i).ProtectContents = False Then
Sheets(i).Protect Password:="rob1"
Else
Sheets(i).Unprotect Password:="rob1"
End If
Next i
End Sub
It only checks for Content protection.
--
"Rob Pearce" <rob.pearce@consignia.com wrote in message
news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06...
| I have a workbook with 50 worksheets.
|
| I protect sheets 3 to 50 [all being the same template
for
| data entry] with the same password.
|
| When I want to change some formula/formatting on
these
48
| sheets I have to unprotect each sheet individually so
that
| I can eventually group them together to alter.
|
| Would it be possible to have some code that will loop
from
| shhet 3 to 50 and unprotect - the password is rob1 if
| anyone could lend a hand - and conversely then to re
| protect.
|
| I would obviously remove the macros from the workbook
| afterwards!
|
| Thanks,
|
| Rob.
.
.
|
| 548 |
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 551 |
Have you installed the add-inn? If not you have to download it at Microsoft's download site
/downloads/2002/acclnk.aspx
--
Regards,
Peo Sjoblom
"Fran" <fkwlau@hotmail.com wrote in message news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 556 |
Hi Fran -
I think I've done this by opening Access and importing the spreadsheet.
Access' wizard will turn the flat spreadsheet into a relational database.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 559 |
Yes i've installed it from the cd. Is it the same as downloading it from
the site?
"Peo Sjoblom" <terre08@mvps.org wrote in message
news:uP8lSCjGCHA.2668@tkmsftngp13...
Have you installed the add-inn? If not you have to download it at
Microsoft's download site
/downloads/2002/acclnk.aspx
--
Regards,
Peo Sjoblom
"Fran" <fkwlau@hotmail.com wrote in message
news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 560 |
And btw, i am using excel 2000 not 2002.
"Peo Sjoblom" <terre08@mvps.org wrote in message
news:uP8lSCjGCHA.2668@tkmsftngp13...
Have you installed the add-inn? If not you have to download it at
Microsoft's download site
/downloads/2002/acclnk.aspx
--
Regards,
Peo Sjoblom
"Fran" <fkwlau@hotmail.com wrote in message
news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 561 |
Yes i know. But I want access to append the data and/or remove any
duplicates. How would i do that?
"Jon Peltier" <jonpeltier@yahoo.com wrote in message
news:#ilZ6NjGCHA.2388@tkmsftngp09...
Hi Fran -
I think I've done this by opening Access and importing the spreadsheet.
Access' wizard will turn the flat spreadsheet into a relational database.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 573 |
Hi Richard
I would say the background fill color has been changed to White on these
cells.
--
"buckjordan" <buckjordan@thegrid.net wrote in message
news:yE9R8.18567$Fv1.1255901@newsread2.prod.itd.earthlink.net...
| The lines separating cells - and columns and rows - have simply
disappeared
| in a small area of a long list of stock market data. Specifically there
are
| no cell lines from columns A through N at rows 155 through 161. What have
I
| done and how do I undo it? The View menu under Tools/Options has the
| gridline box checked. Must be something else. Please help.
| --------------------------------------------------------------------------
--
| ---- Richard B. Jordan buckjordan@thegrid.net (Fresno)559-439-9550
| (Cambria)805-927-3897 (Fresno Fax)559-447-0124
|
|
|
| 574 |
Hi John
Try using a Named range to refer indirectly to the cells storing the
addresses (InsertNameDefine), should work.
--
**Possible solution has NOT been tested**
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
"John Vera" <emperorj@hotmail.com wrote in message
news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03...
|
|
| Hi again. I'm trying to automatize my graphs so they read
| from a cell the range in which they are supposed to get
| points. Say, I have X data in column B and Y data in
| colmun D. My spreadsheet locates the appropriate range and
| displays it as text in four cells (upper and lower x
| range, upper and lower y range), say H4 says "$B$10" H5
| says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the
| ranges in the graph to "read" the text strings in H4, H5
| as the range in X and I4,I5 as the range for Y. Help very
| much appreciated. Typing indirect in the graph range is
| invalid (Excel 2000).
|
| Thanks,
| John
|
| 581 |
Francis -
I'm trying to work this out myself. I'm not an Access guy (not yet, anyway),
and they're giving me about 15 minutes a day to work on it. So far I've
printed out a bunch of help files to read at home, but instead I'm reading
the Excel groups, hehe.
What I gather is that Access is lots faster at this than Excel, especially at
weeding out duplicates in a large unsorted table.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <MY6R8.104837$831.61951@news01.bloor.is.net.cable.rogers.com,
Francis said...
Yes i know. But I want access to append the data and/or remove any
duplicates. How would i do that?
"Jon Peltier" <jonpeltier@yahoo.com wrote in message
news:#ilZ6NjGCHA.2388@tkmsftngp09...
Hi Fran -
I think I've done this by opening Access and importing the spreadsheet.
Access' wizard will turn the flat spreadsheet into a relational database.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said...
Hi,
I am trying to convert an Excel list to an Access db. I
went to the help in Excel and it said that all I need to
do is click on the Data menu - click Convert to MS
Access.
The problem is I don't find this feature, despite numerous
attemps to install Excel and add-ins.
I made a very simple list,
Type Price
Dairy 45
Food 23
and clicked in one of the cells before I looked for that
feature, but it still wouldn't give me anything in the
data menu.
Any suggestion is appreciated.
Fran
|
| 582 |
Hi Dave -
I just responded to an earlier post by John. The problem with charting from
a named range, is that while it seems as though it should work, and while the
same named range works in a worksheet, Excel's charting engine chokes on
named ranges that use INDIRECT in their RefersTo formula. A few other
functions also cause problems.
I suggested a slightly different approach using OFFSET, which works fine with
charts.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said...
Hi John
Try using a Named range to refer indirectly to the cells storing the
addresses (InsertNameDefine), should work.
--
**Possible solution has NOT been tested**
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
"John Vera" <emperorj@hotmail.com wrote in message
news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03...
|
|
| Hi again. I'm trying to automatize my graphs so they read
| from a cell the range in which they are supposed to get
| points. Say, I have X data in column B and Y data in
| colmun D. My spreadsheet locates the appropriate range and
| displays it as text in four cells (upper and lower x
| range, upper and lower y range), say H4 says "$B$10" H5
| says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the
| ranges in the graph to "read" the text strings in H4, H5
| as the range in X and I4,I5 as the range for Y. Help very
| much appreciated. Typing indirect in the graph range is
| invalid (Excel 2000).
|
| Thanks,
| John
|
| 583 |
Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic
range, it would accept other named ranges that uses the INDIRECT.
--
"Jon Peltier" <jonpeltier@yahoo.com wrote in message
news:#U$mtjmGCHA.2452@tkmsftngp08...
| Hi Dave -
|
| I just responded to an earlier post by John. The problem with charting
from
| a named range, is that while it seems as though it should work, and while
the
| same named range works in a worksheet, Excel's charting engine chokes on
| named ranges that use INDIRECT in their RefersTo formula. A few other
| functions also cause problems.
|
| I suggested a slightly different approach using OFFSET, which works fine
with
| charts.
|
| - Jon
| -------
| Jon Peltier, Microsoft Excel MVP
| /jonpeltier/Excel/index.html
| _______
|
| In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said...
|
| Hi John
|
|
| Try using a Named range to refer indirectly to the cells storing the
| addresses (InsertNameDefine), should work.
|
|
| --
| **Possible solution has NOT been tested**
|
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
|
| "John Vera" <emperorj@hotmail.com wrote in message
| news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03...
| |
| |
| | Hi again. I'm trying to automatize my graphs so they read
| | from a cell the range in which they are supposed to get
| | points. Say, I have X data in column B and Y data in
| | colmun D. My spreadsheet locates the appropriate range and
| | displays it as text in four cells (upper and lower x
| | range, upper and lower y range), say H4 says "$B$10" H5
| | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the
| | ranges in the graph to "read" the text strings in H4, H5
| | as the range in X and I4,I5 as the range for Y. Help very
| | much appreciated. Typing indirect in the graph range is
| | invalid (Excel 2000).
| |
| | Thanks,
| | John
|
|
|
|
| 585 |
Hi Andonny
If you are converting numeric text constants to numbers then a Loop is a
pretty slow method, as loops are generally slow. Try the code below:
Sub ConvertTextToNumberFaster()
Dim rCell As Range, Rng1 As Range
Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
If Rng1 Is Nothing Then
MsgBox "No constants"
Exit Sub
End If
Range("IV65536").Copy
Rng1.PasteSpecial , xlPasteSpecialOperationAdd
Application.CutCopyMode = False
End Sub
Just select the entire Column and run the code. It assumes that cell IV65536
is empty.
--
"Andonny" <wje@multiline.com.au wrote in message
news:#5hmxioGCHA.2324@tkmsftngp09...
| Hi,
| I am trying to convert this macro that it works on the column selected
| instead of column A:A.
| I just like to highlight the column in question and then run the macro.
|
| Your help is very much appreciated
| Andonny
|
| Sub ConvertTextToNumber()
| Dim Cell As Range, Rng1 As Range
| Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
| For Each Cell In Rng1
| If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
| Cell = CDbl(Cell)
| End If
| Next Cell
| End Sub
|
|
|
| 590 |
Hi Dave,
Thanks a million for your reply. This is great. Even though the code I
mentioned is slow it also does something which I need quite often. It places
the minus from data extracted from JDEdwards to the front. Like 22- to -22
which is a good thing in my case. So if I could get the previous code to
work with only selected columns would help me a great deal. Then I could use
both in various situations.
Thanks
Andonny
<DavidH@OzGrid.com wrote in message
news:W5fR8.16$TG5.28441@vicpull1.telstra.net...
Hi Andonny
If you are converting numeric text constants to numbers then a Loop is a
pretty slow method, as loops are generally slow. Try the code below:
Sub ConvertTextToNumberFaster()
Dim rCell As Range, Rng1 As Range
Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
If Rng1 Is Nothing Then
MsgBox "No constants"
Exit Sub
End If
Range("IV65536").Copy
Rng1.PasteSpecial , xlPasteSpecialOperationAdd
Application.CutCopyMode = False
End Sub
Just select the entire Column and run the code. It assumes that cell
IV65536
is empty.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Andonny" <wje@multiline.com.au wrote in message
news:#5hmxioGCHA.2324@tkmsftngp09...
| Hi,
| I am trying to convert this macro that it works on the column selected
| instead of column A:A.
| I just like to highlight the column in question and then run the macro.
|
| Your help is very much appreciated
| Andonny
|
| Sub ConvertTextToNumber()
| Dim Cell As Range, Rng1 As Range
| Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
| For Each Cell In Rng1
| If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
| Cell = CDbl(Cell)
| End If
| Next Cell
| End Sub
|
|
|
| 592 |
Hi Andonny
I have modified my code to reverse any imported numbers like: 55- I have
also modified your original code to work on only the selection.
Sub ConvertTextToNumber()
Dim Cell As Range, Rng1 As Range
Set Rng1 = Intersect(ActiveSheet.UsedRange, Selection)
For Each Cell In Rng1
If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
Cell = CDbl(Cell)
End If
Next Cell
End Sub
=========================================
Sub ConvertTextToNumberFaster()
Dim rCell As Range, Rng1 As Range
Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
If Rng1 Is Nothing Then
MsgBox "No constants"
Exit Sub
End If
Rng1.EntireColumn.Insert
Rng1.Offset(0, -1).FormulaR1C1 = _
"=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _
& "*-1,RC[1]*1)"
Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value
'OPTIONAL
Rng1.EntireColumn.Delete
Application.CutCopyMode = False
End Sub
I think you will find the second more effcient as it works on the range as a
whole and not one cell at a time.
--
"Andonny" <wje@multiline.com.au wrote in message
news:OquvRTpGCHA.2808@tkmsftngp10...
| Hi Dave,
| Thanks a million for your reply. This is great. Even though the code I
| mentioned is slow it also does something which I need quite often. It
places
| the minus from data extracted from JDEdwards to the front. Like 22- to -22
| which is a good thing in my case. So if I could get the previous code to
| work with only selected columns would help me a great deal. Then I could
use
| both in various situations.
|
| Thanks
| Andonny
|
|
| <DavidH@OzGrid.com wrote in message
| news:W5fR8.16$TG5.28441@vicpull1.telstra.net...
| Hi Andonny
|
| If you are converting numeric text constants to numbers then a Loop is
a
| pretty slow method, as loops are generally slow. Try the code below:
|
| Sub ConvertTextToNumberFaster()
| Dim rCell As Range, Rng1 As Range
|
| Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
|
| If Rng1 Is Nothing Then
| MsgBox "No constants"
| Exit Sub
| End If
|
| Range("IV65536").Copy
| Rng1.PasteSpecial , xlPasteSpecialOperationAdd
| Application.CutCopyMode = False
| End Sub
|
|
| Just select the entire Column and run the code. It assumes that cell
| IV65536
| is empty.
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
| "Andonny" <wje@multiline.com.au wrote in message
| news:#5hmxioGCHA.2324@tkmsftngp09...
| | Hi,
| | I am trying to convert this macro that it works on the column selected
| | instead of column A:A.
| | I just like to highlight the column in question and then run the
macro.
| |
| | Your help is very much appreciated
| | Andonny
| |
| | Sub ConvertTextToNumber()
| | Dim Cell As Range, Rng1 As Range
| | Set Rng1 = Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A:A"))
| | For Each Cell In Rng1
| | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell)
Then
| | Cell = CDbl(Cell)
| | End If
| | Next Cell
| | End Sub
| |
| |
|
|
|
|
|
| 597 |
Hi Justin
you will need to be a bit more specific. Values are all you see from
formulas by default. As a guess try using Ctrl+`
--
"Justin" <r4al98gy@aol.com wrote in message
news:1124601c21a9d$cde21a50$9ae62ecf@tkmsftngxa02...
| How can I do this? Thanks in advance.
|
| 598 |
Hi Anne
Type: What's new in Microsoft Excel? into the help
--
"Anne Lambert" <AnneTLambert@hotmail.com wrote in message
news:ODTBn7pGCHA.2448@tkmsftngp10...
| Thanks for this. But I don't want to *use* XL 97: I just want to read the
help files. I use XL 2002, and I can't remember all that's
| changed over the years.
|
| Anne
|
|
| "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message
news:O07bGzpGCHA.2992@tkmsftngp13...
| You must install the help files Anne.
| If you install Office custom the vba helpwill not be installed I
believe.??
|
| So run your setup again and install it
|
| Regards Ron
|
| "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht
| news:uvAYyupGCHA.1772@tkmsftngp09...
| Are the Excel 97 Help files available on the Microsoft site? I can
find
| the 2000 Help text, but in spite of a lot of searching, I
| can't find the Excel 97 help. I particularly need the VBA help
| information.
|
| If they're available, I'd be grateful for a URL.
|
| Thanks.
|
| Anne
|
|
|
|
|
|
|
|
|
| 600 |
Hi Dave
Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the
Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out
if the text of the XL 97 help files is also available somewhere.
Anne
"" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net...
Hi Anne
Type: What's new in Microsoft Excel? into the help
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Anne Lambert" <AnneTLambert@hotmail.com wrote in message
news:ODTBn7pGCHA.2448@tkmsftngp10...
| Thanks for this. But I don't want to *use* XL 97: I just want to read the
help files. I use XL 2002, and I can't remember all that's
| changed over the years.
|
| Anne
|
|
| "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message
news:O07bGzpGCHA.2992@tkmsftngp13...
| You must install the help files Anne.
| If you install Office custom the vba helpwill not be installed I
believe.??
|
| So run your setup again and install it
|
| Regards Ron
|
| "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht
| news:uvAYyupGCHA.1772@tkmsftngp09...
| Are the Excel 97 Help files available on the Microsoft site? I can
find
| the 2000 Help text, but in spite of a lot of searching, I
| can't find the Excel 97 help. I particularly need the VBA help
| information.
|
| If they're available, I'd be grateful for a URL.
|
| Thanks.
|
| Anne
|
|
|
|
|
|
|
|
|
| 604 |
AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's
Guide may help:
/library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp
Anne Lambert wrote:
Hi Dave
Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the
Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out
if the text of the XL 97 help files is also available somewhere.
Anne
"" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net...
Hi Anne
Type: What's new in Microsoft Excel? into the help
--
"Anne Lambert" <AnneTLambert@hotmail.com wrote in message
news:ODTBn7pGCHA.2448@tkmsftngp10...
| Thanks for this. But I don't want to *use* XL 97: I just want to read the
help files. I use XL 2002, and I can't remember all that's
| changed over the years.
|
| Anne
|
|
| "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message
news:O07bGzpGCHA.2992@tkmsftngp13...
| You must install the help files Anne.
| If you install Office custom the vba helpwill not be installed I
believe.??
|
| So run your setup again and install it
|
| Regards Ron
|
| "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht
| news:uvAYyupGCHA.1772@tkmsftngp09...
| Are the Excel 97 Help files available on the Microsoft site? I can
find
| the 2000 Help text, but in spite of a lot of searching, I
| can't find the Excel 97 help. I particularly need the VBA help
| information.
|
| If they're available, I'd be grateful for a URL.
|
| Thanks.
|
| Anne
|
|
|
|
|
|
|
|
--
Debra Dalgleish
Excel FAQ, Tips & Book List
/tiptech.html
|
| 605 |
Hi Debra
Yes, indeed. Thanks for this.
Anne
"Debra Dalgleish" <dsd@contextures.com wrote in message news:3D15B24B.2040004@contextures.com...
AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's
Guide may help:
/library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp
Anne Lambert wrote:
Hi Dave
Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the
Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find
out
if the text of the XL 97 help files is also available somewhere.
Anne
"" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net...
Hi Anne
Type: What's new in Microsoft Excel? into the help
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Anne Lambert" <AnneTLambert@hotmail.com wrote in message
news:ODTBn7pGCHA.2448@tkmsftngp10...
| Thanks for this. But I don't want to *use* XL 97: I just want to read the
help files. I use XL 2002, and I can't remember all that's
| changed over the years.
|
| Anne
|
|
| "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message
news:O07bGzpGCHA.2992@tkmsftngp13...
| You must install the help files Anne.
| If you install Office custom the vba helpwill not be installed I
believe.??
|
| So run your setup again and install it
|
| Regards Ron
|
| "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht
| news:uvAYyupGCHA.1772@tkmsftngp09...
| Are the Excel 97 Help files available on the Microsoft site? I can
find
| the 2000 Help text, but in spite of a lot of searching, I
| can't find the Excel 97 help. I particularly need the VBA help
| information.
|
| If they're available, I'd be grateful for a URL.
|
| Thanks.
|
| Anne
|
|
|
|
|
|
|
|
--
Debra Dalgleish
Excel FAQ, Tips & Book List
/tiptech.html
|
| 612 |
Just to add to this thread:
SpecialCells requires error handling in case the range does not contain
constants, e.g.:
On Error Resume Next
Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
' Rest of code...
"" <DavidH@OzGrid.com wrote in message
news:W5fR8.16$TG5.28441@vicpull1.telstra.net...
Hi Andonny
If you are converting numeric text constants to numbers then a Loop is a
pretty slow method, as loops are generally slow. Try the code below:
Sub ConvertTextToNumberFaster()
Dim rCell As Range, Rng1 As Range
Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
If Rng1 Is Nothing Then
MsgBox "No constants"
Exit Sub
End If
Range("IV65536").Copy
Rng1.PasteSpecial , xlPasteSpecialOperationAdd
Application.CutCopyMode = False
End Sub
Just select the entire Column and run the code. It assumes that cell
IV65536
is empty.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Andonny" <wje@multiline.com.au wrote in message
news:#5hmxioGCHA.2324@tkmsftngp09...
| Hi,
| I am trying to convert this macro that it works on the column selected
| instead of column A:A.
| I just like to highlight the column in question and then run the macro.
|
| Your help is very much appreciated
| Andonny
|
| Sub ConvertTextToNumber()
| Dim Cell As Range, Rng1 As Range
| Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
| For Each Cell In Rng1
| If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
| Cell = CDbl(Cell)
| End If
| Next Cell
| End Sub
|
|
|
| 637 |
Jan's method works with foreground colors. Dave's works with
background colors. Both are great, and I can make use of this myself.
The major problem with both methods is that when you change a color,
the sum or the count does not change. For example, if I change a
green color to a red, neither the green totals nor the red totals
changes. Can either of you offer a solution to this?
Many thanks,
Paul
"" <DavidH@OzGrid.com wrote in message news:<jiDR8.19$926.7591@vicpull1.telstra.net...
Hi Debbie
I have a UDF that does this here:
/VBA/Sum.htm
It looks remarkably similar to Jans.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message
news:1186301c21b6d$0194cf30$9be62ecf@tkmsftngxa03...
| Hi,
|
| I assumed the text has the color, not the interior.
| Try this user defined function:
|
| Option Explicit
|
| Function CountColour(rRange As Range, iColor As Integer)
| Dim rCell As Range
| Dim lTotal As Long
| Application.Volatile
| For Each rCell In rRange
| If rCell.Font.ColorIndex = iColor Then
| lTotal = lTotal + 1
| End If
| Next
| CountColour = lTotal
| End Function
|
| Now to count red cells in the range A1:A5, use this
| function:
|
| =CountColour(A1:A5,3)
|
| How to enter a macro (=VBA code):
|
| 1. Open the Excel file you want to add the code to.
|
| 2. Choose Tools, Macro, Visual Basic Editor to open the
| Visual Basic Editor (or press Alt+F11). This is the
| design environment that stores the VBA code. If this
| is
| the first time you have opened the editor, you will
| probably see three windows: the Project window, the
| Properties window, and the Code window.
|
| 3. In the Project window, select the name of the workbook
| you want to add the sub to (one project is listed for
| each open file).
|
| 4. After selecting the project icon, choose Insert,
| Module.
| This inserts a VBA code module into the project and
| places the insertion point in the Code window of that
| module.
|
| 5. You can either type the snippet code exactly as
| written,
| or you can copy the code directly from this message
| and
| paste it into the Code window.
|
| Regards,
|
| Jan Karel Pieterse
| Excel TA/MVP
|
| -----Original Message-----
| I am wanting to count dates in a range, but by colour.
| Ie, all red dates to give a total number, all blue dates
| to give a total number.
|
| Can anyone help??
|
| Debbie.
| .
|
|
| 671 |
No problem..we all have those days :)
"" <DavidH@OzGrid.com wrote in message
news:28yR8.29$FU5.40386@vicpull1.telstra.net...
Sorry Tim.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Tim Zych" <tzych@earthlink.net wrote in message
news:OteXv7zGCHA.1932@tkmsftngp10...
| "" <DavidH@OzGrid.com wrote in message
| news:bKxR8.27$FU5.37950@vicpull1.telstra.net...
| You took
| the time to point out possible errors but offered no alternative.
|
| Yikes...are we both reading the same thread?
|
| Here's a copy of my first response. (Note the 'On Error Resume Next'
I
| inserted).
|
|
| | "Tim Zych" <tzych@earthlink.net wrote in message
| | news:#QxLrqtGCHA.1156@tkmsftngp13...
| | | Just to add to this thread:
| | |
| | | SpecialCells requires error handling in case the range does not
| contain
| | | constants, e.g.:
| | |
| | | On Error Resume Next
| | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
| | | On Error GoTo 0
| | | ' Rest of code...
|
|
| Contructive critisism is good, outright negativity is bad, don't you
agree
| ?
|
| Um, yeah.
|
|
| "" <DavidH@OzGrid.com wrote in message
| news:bKxR8.27$FU5.37950@vicpull1.telstra.net...
| Bugger :o) Who removed the On Error Resume Next.
|
| It would have been more helpful to the OP if you guys demonstrated the
use
| of an error handler rather than just saying it doesn't have one. You
took
| the time to point out possible errors but offered no alternative.
| Contructive critisism is good, outright negativity is bad, don't you
agree
| ?
|
|
|
| Sub ConvertTextToNumberFaster()
| Dim rCell As Range, Rng1 As Range
|
| On error Resume next
| Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
|
| If Rng1 Is Nothing Then
| MsgBox "No constants"
| On error GoTo 0
| Exit Sub
| End If
|
| On error GoTo 0
| Rng1.EntireColumn.Insert
| Rng1.Offset(0, -1).FormulaR1C1 = _
| "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _
| & "*-1,RC[1]*1)"
| Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value
| 'OPTIONAL
| Rng1.EntireColumn.Delete
| Application.CutCopyMode = False
| End Sub
|
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
|
|
| "Tim Zych" <tzych@earthlink.net wrote in message
| news:#duQFnzGCHA.2448@tkmsftngp10...
| | Hmmm, Excel returns an error (1004, no cells were found) for me if
there
| are
| | no "special cells". I'm using XL2000 and I know the same occurs with
97.
| |
| |
| | "" <DavidH@OzGrid.com wrote in message
| | news:qRwR8.19$FU5.33944@vicpull1.telstra.net...
| | I agree Tim, but my code already has this covered:o)
| |
| |
| |
| | --
| |
| |
| |
| |
| | FREE EXCEL NEWSLETTER
| | /News/2home.htm
| | Get the OzGrid Add-in
| | /Services/AddinExamples.htm
| | If it's Excel, then it's us!
| |
| | "Tim Zych" <tzych@earthlink.net wrote in message
| | news:#QxLrqtGCHA.1156@tkmsftngp13...
| | | Just to add to this thread:
| | |
| | | SpecialCells requires error handling in case the range does not
| contain
| | | constants, e.g.:
| | |
| | | On Error Resume Next
| | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
| | | On Error GoTo 0
| | | ' Rest of code...
| | |
| | |
| | | "" <DavidH@OzGrid.com wrote in message
| | | news:W5fR8.16$TG5.28441@vicpull1.telstra.net...
| | | Hi Andonny
| | |
| | | If you are converting numeric text constants to numbers then a
| Loop
| | is
| | a
| | | pretty slow method, as loops are generally slow. Try the code
| below:
| | |
| | | Sub ConvertTextToNumberFaster()
| | | Dim rCell As Range, Rng1 As Range
| | |
| | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants)
| | |
| | | If Rng1 Is Nothing Then
| | | MsgBox "No constants"
| | | Exit Sub
| | | End If
| | |
| | | Range("IV65536").Copy
| | | Rng1.PasteSpecial , xlPasteSpecialOperationAdd
| | | Application.CutCopyMode = False
| | | End Sub
| | |
| | |
| | | Just select the entire Column and run the code. It assumes
that
| cell
| | | IV65536
| | | is empty.
| | |
| | |
| | | --
| | |
| | |
| | |
| | |
| | | FREE EXCEL NEWSLETTER
| | | /News/2home.htm
| | | Get the OzGrid Add-in
| | | /Services/AddinExamples.htm
| | | If it's Excel, then it's us!
| | |
| | | "Andonny" <wje@multiline.com.au wrote in message
| | | news:#5hmxioGCHA.2324@tkmsftngp09...
| | | | Hi,
| | | | I am trying to convert this macro that it works on the
column
| | selected
| | | | instead of column A:A.
| | | | I just like to highlight the column in question and then run
the
| | macro.
| | | |
| | | | Your help is very much appreciated
| | | | Andonny
| | | |
| | | | Sub ConvertTextToNumber()
| | | | Dim Cell As Range, Rng1 As Range
| | | | Set Rng1 = Intersect(ActiveSheet.UsedRange,
| | ActiveSheet.Range("A:A"))
| | | | For Each Cell In Rng1
| | | | If Not IsEmpty(Cell) And Not Cell.HasFormula And
| IsNumeric(Cell)
| | Then
| | | | Cell = CDbl(Cell)
| | | | End If
| | | | Next Cell
| | | | End Sub
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|
|
| 681 |
I have beat my head against that wall so many times. The ranges work fine in
the worksheet, and either don't work at all in the chart, or only show one
point, or other weird things happen. I stick to the OFFSETs now, because I
know they will work.
What can be done, of course, is define the named range with INDIRECT, then
array enter it into a worksheet range, and then refer to this range in the
chart, either directly through the address, or through another dynamic range,
if the number of cells changes.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <J9cR8.7$TG5.21637@vicpull1.telstra.net, said...
Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic
range, it would accept other named ranges that uses the INDIRECT.
--
"Jon Peltier" <jonpeltier@yahoo.com wrote in message
news:#U$mtjmGCHA.2452@tkmsftngp08...
| Hi Dave -
|
| I just responded to an earlier post by John. The problem with charting
from
| a named range, is that while it seems as though it should work, and while
the
| same named range works in a worksheet, Excel's charting engine chokes on
| named ranges that use INDIRECT in their RefersTo formula. A few other
| functions also cause problems.
|
| I suggested a slightly different approach using OFFSET, which works fine
with
| charts.
|
| - Jon
| -------
| Jon Peltier, Microsoft Excel MVP
| /jonpeltier/Excel/index.html
| _______
|
| In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said...
|
| Hi John
|
|
| Try using a Named range to refer indirectly to the cells storing the
| addresses (InsertNameDefine), should work.
|
|
| --
| **Possible solution has NOT been tested**
|
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
|
| "John Vera" <emperorj@hotmail.com wrote in message
| news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03...
| |
| |
| | Hi again. I'm trying to automatize my graphs so they read
| | from a cell the range in which they are supposed to get
| | points. Say, I have X data in column B and Y data in
| | colmun D. My spreadsheet locates the appropriate range and
| | displays it as text in four cells (upper and lower x
| | range, upper and lower y range), say H4 says "$B$10" H5
| | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the
| | ranges in the graph to "read" the text strings in H4, H5
| | as the range in X and I4,I5 as the range for Y. Help very
| | much appreciated. Typing indirect in the graph range is
| | invalid (Excel 2000).
| |
| | Thanks,
| | John
|
|
|
|
| 694 |
Hi Mr NDP
Not sure what you really need but is you want "Plot" to become the range A1,
A2 etc you must use the Set statement (same for most Objects). Once you have
the variable Plot set to a range you can then easily get the cells value,
address or any other Property from it. Just type: Plot. and you will get a
list of all Properties and Methods. Run the example below to see what I
mean.
Sub test()
Dim Plot As Range
Dim CN As Integer
For CN = 0 To 7
Set Plot = Range("A2").Offset(0, CN)
MsgBox (Plot & Chr(13) & Contents)
Next CN
End Sub
--
"Mr NDP" <mr_ndp@yahoo.com wrote in message
news:12f1001c21c07$ae3dcdf0$35ef2ecf@TKMSFTNGXA11...
| All,
|
| In the following example, I want the variable "Plot" to
| equal the Location (A1, A2, A3, etc.). Instead, I
| get "True". I'm still learning. Any suggestions?
|
| Sub test()
| For CN = 0 To 7
| Contents = Range("A2").Offset(0, CN).Value
| Plot = Range("A2").Offset(0, CN).Select
| MsgBox (Plot & Chr(13) & Contents)
| Next CN
| End Sub
|
|
| 695 |
Hi Mr NDP
Not sure whether you set your Workbook to manual calculation on purpose or
not, but if you do be very careful that you do not feed yourself erroneous
results. It's all too easy to forget to force a calculation when not in
Automatic. If you have a Workbook that needs to run in manual calculation it
normally means you have a design problem. See the links below for
spreadsheet designs:
/News/CalculationRangeIndirect.htm
/News/ConFormatCheckFind.htm
/News/GoodVsBadDesignSpeedUpEvents.htm
--
"Mr NDP" <mr_ndp@yahoo.com wrote in message
news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10...
| That was it. I had my calculations set to manual.
|
| Thanks for the help...
|
|
| -----Original Message-----
| Hi:
|
| Do you have the calculation mode set to manual? If so,
| hitting F9 should
| update the formulas.
| --
| Regards,
|
| Vasant.
|
| **No direct emails please--keep discussion in
| newsgroup.**
|
|
| "Mr NDP" <mr_ndp@yahoo.com wrote in message
| news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11...
| All,
|
| I've noticed in my spreadsheets where I use the countif
| function that the cells will not always update. Saving
| the document seems to refresh the sheet and the
| calculations are emplemented. Is there a way to
| refresh
| the formulas in a worksheet without having to save it?
|
| Thanks,
|
| Mr NDP
|
|
| .
|
|
| 697 |
Hi Dave,
Not sure if I agree on the design problem Dave.
I have a not-too-large workbook, but since it contains
lots of array formula's it's recalc is very time
consuming. When I need to do some data entry in that
workbook I am glad I set calc to manual...
Regards,
Jan Karel Pieterse
Excel TA/MVP
-----Original Message-----
Hi Mr NDP
Not sure whether you set your Workbook to manual
calculation on purpose or
not, but if you do be very careful that you do not feed
yourself erroneous
results. It's all too easy to forget to force a
calculation when not in
Automatic. If you have a Workbook that needs to run in
manual calculation it
normally means you have a design problem. See the links
below for
spreadsheet designs:
/News/CalculationRangeIndirect.htm
/News/ConFormatCheckFind.htm
/News/GoodVsBadDesignSpeedUpEvents.ht
m
--
"Mr NDP" <mr_ndp@yahoo.com wrote in message
news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10...
| That was it. I had my calculations set to manual.
|
| Thanks for the help...
|
|
| -----Original Message-----
| Hi:
|
| Do you have the calculation mode set to manual? If so,
| hitting F9 should
| update the formulas.
| --
| Regards,
|
| Vasant.
|
| **No direct emails please--keep discussion in
| newsgroup.**
|
|
| "Mr NDP" <mr_ndp@yahoo.com wrote in message
| news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11...
| All,
|
| I've noticed in my spreadsheets where I use the
countif
| function that the cells will not always update.
Saving
| the document seems to refresh the sheet and the
| calculations are emplemented. Is there a way to
| refresh
| the formulas in a worksheet without having to save
it?
|
| Thanks,
|
| Mr NDP
|
|
| .
|
.
|
| 699 |
Hi Jan
If you read the links you will see that I say array formulae are the main
cause of sloooowww calculations. Arrays (IMO) are a quick fix superficial
approach to a possible complex problem. The use of Excels database functions
can often be used in place of arrays as can Pivot Tables etc
I still hold firm that any spreadsheet that requires manual calculation has
a spreadsheet design problem. You run the risk of reading uncalculated
results. It's a bit like you car brakes are rubbing (and slowing you down)
so you disconnect them and rely on the handbrake.
--
"Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message
news:ff2101c21c0e$9c93ffa0$9ee62ecf@tkmsftngxa05...
| Hi Dave,
|
| Not sure if I agree on the design problem Dave.
| I have a not-too-large workbook, but since it contains
| lots of array formula's it's recalc is very time
| consuming. When I need to do some data entry in that
| workbook I am glad I set calc to manual...
|
| Regards,
|
| Jan Karel Pieterse
| Excel TA/MVP
|
| -----Original Message-----
| Hi Mr NDP
|
| Not sure whether you set your Workbook to manual
| calculation on purpose or
| not, but if you do be very careful that you do not feed
| yourself erroneous
| results. It's all too easy to forget to force a
| calculation when not in
| Automatic. If you have a Workbook that needs to run in
| manual calculation it
| normally means you have a design problem. See the links
| below for
| spreadsheet designs:
|
| /News/CalculationRangeIndirect.htm
| /News/ConFormatCheckFind.htm
| /News/GoodVsBadDesignSpeedUpEvents.ht
| m
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
| "Mr NDP" <mr_ndp@yahoo.com wrote in message
| news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10...
| | That was it. I had my calculations set to manual.
| |
| | Thanks for the help...
| |
| |
| | -----Original Message-----
| | Hi:
| |
| | Do you have the calculation mode set to manual? If so,
| | hitting F9 should
| | update the formulas.
| | --
| | Regards,
| |
| | Vasant.
| |
| | **No direct emails please--keep discussion in
| | newsgroup.**
| |
| |
| | "Mr NDP" <mr_ndp@yahoo.com wrote in message
| | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11...
| | All,
| |
| | I've noticed in my spreadsheets where I use the
| countif
| | function that the cells will not always update.
| Saving
| | the document seems to refresh the sheet and the
| | calculations are emplemented. Is there a way to
| | refresh
| | the formulas in a worksheet without having to save
| it?
| |
| | Thanks,
| |
| | Mr NDP
| |
| |
| | .
| |
|
|
| .
|
|
| 703 |
Hi drwinterton
I am sure someone will come along with a possible answer for you. I just
wanted to see if your area aware of the EditLinksChange source option
available in Linked Workbooks.
--
"drwinterton" <daniel_winterton@hk.ml.com wrote in message
news:1223601c21c1a$285a9e30$3aef2ecf@TKMSFTNGXA09...
| I have a problem that appears to be fairly widespread. I
| haven't, however, found a solution to my exact problem on
| this site...
|
| Here's the trouble. I have a system of files that I need
| to be able to transfer over to a different drive.
|
| I've read that as long as everything is in the same
| directory and the sub-directory structure and names of the
| sub-directories are left intact that Excel will pretend
| the references are relative (aka, I can move the files and
| they will work). Alas, it doesn't appear to be working
| for me.
|
| Here's the magnitude of data that I'm worried about: 26
| directories, 25 of which are designed to collect data from
| different users (the whole reason this all isn't just one
| file anymore), and 1 directory to compile this information
| (and also serve as a source of a single piece of
| information for every file in the other directories). In
| each of the 25 directories, I will have 9 or 36
| (undecided) files that I need to reference.
|
| The collection folder contains two files - fetcher (which
| gets the data for me) and sesdata (which the input files
| need to reference). The collection folder is
| named "master", the other inputs are all numbered. All
| these directories are in the directory "qpa."
|
| I try to move the directory containing both master and the
| input folders (qpa), and all my links which, if relative,
| would read "../master/sesdata" break. I would like to be
| able to design this system on my pc, record it to a CD,
| then copy it onto different computers. Due to the number
| of links, the edit links option simply won't work. I am
| willing to move the directory structure (to some extent),
| try VBA (throw code at me, I don't do Basic), or whatever.
| Or if I need to try to use Quattro Pro, tell me.
|
|
|
| 709 |
Hi Jason
I am sure Tom will be along soon to help you, but in the mean time you might
find these links of use:
/VBA/VBACode.htm
/VBA/SpeedingUpVBACode.htm
/VBA/VBALoops.htm
Hope they help
--
"ims" <kft_icq@myrealbox.com wrote in message
news:#jjYydCHCHA.368@tkmsftngp13...
| Dear Tom
|
| Hope you remember me whom you helped to write the macro below.
| This really helpful to my job, however when I apply it to an excel
worksheet
| of 15 columns, 40 thousand records I find it has to work for several
hours,
| which is impractical.
| I don't really know which part of the marco waste most of the time.
| So I write here and want to know if the marco can be trimmed so as to
make
| it run more faster.
| Your help is very much appreciated!
|
| Best regards
|
| Jason
|
|
|
| ----- Original Message -----
| From: "Tom Ogilvy" <twogilvy@msn.com
| Newsgroups: microsoft.public.excel.misc
| Sent: Monday, May 13, 2002 5:46 AM
| Subject: Re: How to handle worksheet and save it as xls.file?
|
|
| That revision was based on an earlier, slightly different version of the
| original - it had hard coded paths for the file operations. This has
the
| sPath variable (like the original) to make it easier to specify the
| location
| for the new files:
|
| Sub StartSplitout()
| Dim sh As Worksheet
| Dim sh1 As Worksheet
| Dim varr As Variant
| Dim rng As Range
| Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft))
| varr = rng.Value
| Cells(1, 1).EntireRow.Delete
| ActiveSheet.Copy
| Set sh = ActiveSheet
| Splitout sh, varr
| ThisWorkbook.Activate
| Cells(1, 1).EntireRow.Insert
| Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr
| End Sub
|
| Sub Splitout(sh As Worksheet, varr As Variant)
| Dim bContinue As Boolean
| Dim rng As Range, Cell As Range
| Dim rw As Long, sh1 As Worksheet
| Dim sPath As String
| sPath = "C:\Data\"
| bContinue = False
| Set rng = sh.Range(sh.Cells(1, 1), _
| sh.Cells(1, 1).End(xlDown))
| For Each Cell In rng
| If Cell.Row < 1 Then
| If Cell.Value < _
| Cell.Offset(-1, 0).Value Then
| bContinue = True
| rw = Cell.Row
| Exit For
| End If
| End If
| Next
| If bContinue Then
| sh.Copy
| Set sh1 = ActiveSheet
| sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _
| EntireRow.Delete
| sh.Range(sh.Cells(rw, 1), _
| sh.Cells(Rows.Count, 1).End(xlUp)) _
| .EntireRow.Delete
| sh.Cells(1, 1).EntireRow.Insert
| sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr
| On Error Resume Next
| Kill sPath & sh.Cells(2, 1).Value & _
| ".xls"
| On Error GoTo 0
| sh.Parent.SaveAs sPath & _
| sh.Cells(2, 1).Value & ".xls"
| sh.Parent.Close SaveChanges:=False
| Splitout sh1, varr
| Else
| On Error Resume Next
| Kill sPath & sh.Cells(2, 1).Value & _
| ".xls"
| On Error GoTo 0
| sh.Cells(1, 1).EntireRow.Insert
| sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr
| sh.Parent.SaveAs sPath & _
| sh.Cells(2, 1).Value & ".xls"
| sh.Parent.Close SaveChanges:=False
| End If
| End Sub
|
| Regards,
| Tom Ogilvy
|
|
|
|
|
| 715 |
Hi John
Try formatting the cells (before pasting) as Text.
--
"John Ricketts" <master@westnet.com.au wrote in message
news:erNSZ6CHCHA.2436@tkmsftngp11...
| When copying and pasting ( say a table from FrontPage to Excel ) fractions
| like 6/11 get translated into dates. I have a large number of data tables
| to copy and paste and going through editing each cell with a fraction is
not
| an option.
|
| How does one prevent Excel from making this conversion?
|
|
|
|
|
| 717 |
Don't think INDIRECT works for closed Workbooks <bg
--
"Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message
news:1005401c21c35$215b2120$9ee62ecf@tkmsftngxa05...
| Hi,
|
| Some questions if I may.
|
| - How did you copy your files?
| - Did you copy the complete tree with the files?
| - what is a correct link and what does it look like after
| copying you files (and opening the master copy)
|
| I guess simplest should be to simply copy the entire tree
| to another drive. But if the links travel with that also
| depends on their exact type. If they are direct cell to
| cell links, AFAIK they should adjust to the new location.
|
| But if any part of them are hardcoded (like in the
| INDIRECT function), well... But I guess you already knew
| that <g.
|
|
| Regards,
|
| Jan Karel Pieterse
| Excel TA/MVP
|
| -----Original Message-----
| I have a problem that appears to be fairly widespread. I
| haven't, however, found a solution to my exact problem on
| this site...
|
| Here's the trouble. I have a system of files that I need
| to be able to transfer over to a different drive.
|
| I've read that as long as everything is in the same
| directory and the sub-directory structure and names of
| the
| sub-directories are left intact that Excel will pretend
| the references are relative (aka, I can move the files
| and
| they will work). Alas, it doesn't appear to be working
| for me.
|
| Here's the magnitude of data that I'm worried about: 26
| directories, 25 of which are designed to collect data
| from
| different users (the whole reason this all isn't just one
| file anymore), and 1 directory to compile this
| information
| (and also serve as a source of a single piece of
| information for every file in the other directories). In
| each of the 25 directories, I will have 9 or 36
| (undecided) files that I need to reference.
|
| The collection folder contains two files - fetcher (which
| gets the data for me) and sesdata (which the input files
| need to reference). The collection folder is
| named "master", the other inputs are all numbered. All
| these directories are in the directory "qpa."
|
| I try to move the directory containing both master and
| the
| input folders (qpa), and all my links which, if relative,
| would read "../master/sesdata" break. I would like to be
| able to design this system on my pc, record it to a CD,
| then copy it onto different computers. Due to the number
| of links, the edit links option simply won't work. I am
| willing to move the directory structure (to some extent),
| try VBA (throw code at me, I don't do Basic), or
| whatever.
| Or if I need to try to use Quattro Pro, tell me.
|
|
| .
|
|
| 718 |
Thanks for the suggestion but I had already tried that.
I had also tried formatting as Fraction but that didn't help the cause
either.
"" <DavidH@OzGrid.com wrote in message
news:gTXR8.7$1h6.12510@vicpull1.telstra.net...
Hi John
Try formatting the cells (before pasting) as Text.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"John Ricketts" <master@westnet.com.au wrote in message
news:erNSZ6CHCHA.2436@tkmsftngp11...
| When copying and pasting ( say a table from FrontPage to Excel )
fractions
| like 6/11 get translated into dates. I have a large number of data
tables
| to copy and paste and going through editing each cell with a fraction is
not
| an option.
|
| How does one prevent Excel from making this conversion?
|
|
|
|
|
| 720 |
You should get the Paste special option when you right click? Maybe one of
these options will help, a normal paste would undo the Text format.
--
"John Ricketts" <master@westnet.com.au wrote in message
news:uE0TjlDHCHA.704@tkmsftngp08...
| Thanks for the suggestion but I had already tried that.
| I had also tried formatting as Fraction but that didn't help the cause
| either.
|
|
| "" <DavidH@OzGrid.com wrote in message
| news:gTXR8.7$1h6.12510@vicpull1.telstra.net...
| Hi John
|
| Try formatting the cells (before pasting) as Text.
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
|
| "John Ricketts" <master@westnet.com.au wrote in message
| news:erNSZ6CHCHA.2436@tkmsftngp11...
| | When copying and pasting ( say a table from FrontPage to Excel )
| fractions
| | like 6/11 get translated into dates. I have a large number of data
| tables
| | to copy and paste and going through editing each cell with a fraction
is
| not
| | an option.
| |
| | How does one prevent Excel from making this conversion?
| |
| |
| |
| |
|
|
|
|
|
| 721 |
Hi, Itried creating an add-in tohave some Macros and
functions of my own available on any workbook, but Ican't
seem to be able access the macros and funs on the add-in I
create after I install it with the add-in manager.
I can access the code with the VB Editor, but can't find
neither macros' name on the "run macro" menu nor
functions' name in the insert "function menu"
Guess there's something wrong with the code itself, but I
can't figure out what exactly.
Please mail me an answer directly, since I got problem
accessing newsgroups.
In case you do, please remove "NOSPAM" from address.
Thanks a lot
|
| 724 |
Hi,
I am able to pass parameters to a VBA macro from the 'Assign Macro'
dialog by enclosing in single quotes.
e.g.
Book1!'Msg "Hello"'
to execute the macro in the open workbook 'Book1':
Public Sub Msg(m As String)
MsgBox m
End Sub
However I am unable to do the same for a macro in an open Add-in.
If I try
'MyAddin.xla'!'Foo "param1"'
I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot
be found.
I can invoke Macros with no parameters in the Add-in.
Anybody know why this is not working, and if there is a way to make it
work?
Thanks in advance.
|
| 731 |
Jan, I agree to disagree. If your methods work for you then stick with them.
--
"Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message
news:11fa701c21c4e$f10c30d0$b1e62ecf@tkmsftngxa04...
| Hi Dave,
|
|
|
| If you read the links you will see that I say array
| formulae are the main
| cause of sloooowww calculations. Arrays (IMO) are a quick
| fix superficial
| approach to a possible complex problem.
|
| I totally disagree on the last remark. Yes, they tend to
| be slow. But they are often an elegant solution to an
| indeed complex problem. They are far from superficial and
| seldomly a quick fix.
|
| The use of Excels database functions
| can often be used in place of arrays as can Pivot Tables
| etc
|
| That may go for extracting information from simple tables.
| As soon as one has to combine data from different subsets,
| XL's database functions just aren't good enough. Sometimes
| an array formula can then come to rescue.
|
|
| I still hold firm that any spreadsheet that requires
| manual calculation has
| a spreadsheet design problem. You run the risk of reading
| uncalculated
| results.
|
| Disagreed again. I just happen to have a lot of (very
| complex) data and calc sometimes. But there is always the
| risk of uncalculated data, indeed. It is a matter of
| knowing ones business. These workbooks are not for the
| masses, they are for me. Printouts are for my colleagues,
| which are always automatically calculated.
|
|
| Regards,
|
| Jan Karel Pieterse
| Excel TA/MVP
|
|
| 735 |
A solution using VBA is fine, I just have a lot to learn
in that area (writing them myself, I mean).
The only hitch with your answer is that there are usually
multiple (anywhere from 1 to 20) hidden columns
throughout each worksheet (possible example: A, B, C, F,
G, J, T, S, and AB are hidden). That would mean that I
would have to check manually which columns are hidden and
then type them each into the name box to delete them. I
am looking for something a little more functional.
Thanks!
Laura
-----Original Message-----
Hi Laura
I assume you mean without VBA, if so assume Column "C"
is hidden, type C:C
in the name box (left of the formula bar) and push
enter. Now go to
EditDelete. There is no doubt a shortcut key for
deleting Columns, you
should find it here:
/Excel/ExcelKeyBoardShortcutKeys.htm
--
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07...
| Is it possible to delete hidden columns without
unhiding
| them? I get many workbooks sent to me with hidden
| columns that contain data I do not need. Some are
| formulas but I usually "copy" and "paste values" so
there
| is no worry of linking errors.
|
| Thanks for any help you can give me.
|
| Laura
.
|
| 738 |
Hi Laura:
Sub DeleteHiddenRows()
Dim c As Range
For Each c In Rows(1).Cells
If c.Width = 0 Then c.EntireColumn.Delete
Next
End Sub
--
Regards,
Vasant.
**No direct emails please--keep discussion in newsgroup.**
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09...
A solution using VBA is fine, I just have a lot to learn
in that area (writing them myself, I mean).
The only hitch with your answer is that there are usually
multiple (anywhere from 1 to 20) hidden columns
throughout each worksheet (possible example: A, B, C, F,
G, J, T, S, and AB are hidden). That would mean that I
would have to check manually which columns are hidden and
then type them each into the name box to delete them. I
am looking for something a little more functional.
Thanks!
Laura
-----Original Message-----
Hi Laura
I assume you mean without VBA, if so assume Column "C"
is hidden, type C:C
in the name box (left of the formula bar) and push
enter. Now go to
EditDelete. There is no doubt a shortcut key for
deleting Columns, you
should find it here:
/Excel/ExcelKeyBoardShortcutKeys.htm
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07...
| Is it possible to delete hidden columns without
unhiding
| them? I get many workbooks sent to me with hidden
| columns that contain data I do not need. Some are
| formulas but I usually "copy" and "paste values" so
there
| is no worry of linking errors.
|
| Thanks for any help you can give me.
|
| Laura
.
|
| 739 |
Hi Laura
Push Alt+F11, then go InsertModule and paste in the code below:
Sub DeleteHiddenCols()
Dim rCell As Range
'Runs on the active sheet
Application.Calculation = xlCalculationManual
For Each rCell In Rows(1)
If rCell.EntireColumn.Hidden = True Then
rCell.EntireColumn.Delete
End If
Next rCell
Application.Calculation = xlCalculationAutomatic
End Sub
Psuh Alt+Q, then Alt+F8 click "DeleteHiddenCols" then Options and asign a
shortcut key.
--
Hope this helps.
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09...
| A solution using VBA is fine, I just have a lot to learn
| in that area (writing them myself, I mean).
|
| The only hitch with your answer is that there are usually
| multiple (anywhere from 1 to 20) hidden columns
| throughout each worksheet (possible example: A, B, C, F,
| G, J, T, S, and AB are hidden). That would mean that I
| would have to check manually which columns are hidden and
| then type them each into the name box to delete them. I
| am looking for something a little more functional.
|
| Thanks!
|
| Laura
|
|
| -----Original Message-----
| Hi Laura
|
|
| I assume you mean without VBA, if so assume Column "C"
| is hidden, type C:C
| in the name box (left of the formula bar) and push
| enter. Now go to
| EditDelete. There is no doubt a shortcut key for
| deleting Columns, you
| should find it here:
| /Excel/ExcelKeyBoardShortcutKeys.htm
|
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
|
| "Laura Wilde" <lwilde@new.rr.com wrote in message
| news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07...
| | Is it possible to delete hidden columns without
| unhiding
| | them? I get many workbooks sent to me with hidden
| | columns that contain data I do not need. Some are
| | formulas but I usually "copy" and "paste values" so
| there
| | is no worry of linking errors.
| |
| | Thanks for any help you can give me.
| |
| | Laura
|
|
| .
|
|
| 741 |
As long as your careful with your formulas/values:
Option Explicit
Sub delhiddencols()
Dim iCol As Long
Dim deleteme As Range
For iCol = 1 To 256
If Columns(iCol).Hidden = True Then
If deleteme Is Nothing Then
Set deleteme = Cells(1, iCol)
Else
Set deleteme = Union(Cells(1, iCol), deleteme)
End If
End If
Next iCol
If deleteme Is Nothing Then
'can't do anything
Else
deleteme.EntireColumn.Delete
End If
End Sub
You may want to add some checks for protection/filters, etc. Things that will
stop you from deleting columns. Maybe even a little check to ask "are you
sure"?
Laura Wilde wrote:
A solution using VBA is fine, I just have a lot to learn
in that area (writing them myself, I mean).
The only hitch with your answer is that there are usually
multiple (anywhere from 1 to 20) hidden columns
throughout each worksheet (possible example: A, B, C, F,
G, J, T, S, and AB are hidden). That would mean that I
would have to check manually which columns are hidden and
then type them each into the name box to delete them. I
am looking for something a little more functional.
Thanks!
Laura
-----Original Message-----
Hi Laura
I assume you mean without VBA, if so assume Column "C"
is hidden, type C:C
in the name box (left of the formula bar) and push
enter. Now go to
EditDelete. There is no doubt a shortcut key for
deleting Columns, you
should find it here:
/Excel/ExcelKeyBoardShortcutKeys.htm
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07...
| Is it possible to delete hidden columns without
unhiding
| them? I get many workbooks sent to me with hidden
| columns that contain data I do not need. Some are
| formulas but I usually "copy" and "paste values" so
there
| is no worry of linking errors.
|
| Thanks for any help you can give me.
|
| Laura
.
--
Dave Peterson
ec35720@msn.com
|
| 743 |
Thanks everyone...all work perfectly!
-----Original Message-----
A solution using VBA is fine, I just have a lot to learn
in that area (writing them myself, I mean).
The only hitch with your answer is that there are
usually
multiple (anywhere from 1 to 20) hidden columns
throughout each worksheet (possible example: A, B, C, F,
G, J, T, S, and AB are hidden). That would mean that I
would have to check manually which columns are hidden
and
then type them each into the name box to delete them. I
am looking for something a little more functional.
Thanks!
Laura
-----Original Message-----
Hi Laura
I assume you mean without VBA, if so assume Column "C"
is hidden, type C:C
in the name box (left of the formula bar) and push
enter. Now go to
EditDelete. There is no doubt a shortcut key for
deleting Columns, you
should find it here:
/Excel/ExcelKeyBoardShortcutKeys.ht
m
--
"Laura Wilde" <lwilde@new.rr.com wrote in message
news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07...
| Is it possible to delete hidden columns without
unhiding
| them? I get many workbooks sent to me with hidden
| columns that contain data I do not need. Some are
| formulas but I usually "copy" and "paste values" so
there
| is no worry of linking errors.
|
| Thanks for any help you can give me.
|
| Laura
.
.
|
| 747 |
Mel,
What version of Excel are you using? In Excel 2002, you would go to
Tools - Macro - Security and then on the Trusted Sources tab, check the
box: Trust all installed add-ins and templates.
Paul
"Mel" <shakey@sonic.net wrote in message
news:3D18F17A.5AC5C7CD@sonic.net...
I do not know what I did but lately I must go thru three screens of
marco warnings to open a blank or any excel sheet.
First--- C:\whatever\library\sumif.xla
then-----C:\whatever\analysis\analys32.xll
then-----c:\whatever\start\personal.xls
I can understand peresonal and then my requested sheet as they are
medium security but what made the first two start requesting permission,
they are of MS origion and should always be safe.
Mel S.
--
Nothing will ever be attempted if all possible objections must first be
overcome.
|
| 752 |
Paul Watson wrote:
Mel,
What version of Excel are you using? In Excel 2002, you would go to
Tools - Macro - Security and then on the Trusted Sources tab, check the
box: Trust all installed add-ins and templates.
Paul
"Mel" <shakey@sonic.net wrote in message
news:3D18F17A.5AC5C7CD@sonic.net...
I do not know what I did but lately I must go thru three screens of
marco warnings to open a blank or any excel sheet.
First--- C:\whatever\library\sumif.xla
then-----C:\whatever\analysis\analys32.xll
then-----c:\whatever\start\personal.xls
I can understand peresonal and then my requested sheet as they are
medium security but what made the first two start requesting permission,
they are of MS origion and should always be safe.
Mel S.
--
Nothing will ever be attempted if all possible objections must first be
overcome.
Thanks Paul,
That works in 2000 version as well. Darned if I know how it got unchecked.
Mel
--
Nothing will ever be attempted if all possible objections must first be
overcome.
|
| 756 |
Thanks Dave,
However my problem is not calling it from within a VBA macro but I want
an Excel developer who may know nothing about VBA to pass parameters to a
VBA macro contained in a loaded Add-in from a button he has added to the
spreadsheet.
The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign
Macro dialog of the excel button control seemed to be ignoring the
'MyAddin.xla' prefix and assuming it was in the workbook containing the
button (Book1.xls).
Steve
What I was hoping for was
"Dave Peterson" <ec35720@msn.com wrote in message
news:3D18E8DF.4D4DEB54@msn.com...
When I tried to pass a string to your Msg sub, I could do it like this:
Application.Run "'book1.xla'!msg", "hi there!"
I could also do it like this, if I did a Tools|references and set a
reference to
that other project.
msg "hi there2"
But I'm not sure if this was the answer to your question....
Steve Legg wrote:
Hi,
I am able to pass parameters to a VBA macro from the 'Assign Macro'
dialog by enclosing in single quotes.
e.g.
Book1!'Msg "Hello"'
to execute the macro in the open workbook 'Book1':
Public Sub Msg(m As String)
MsgBox m
End Sub
However I am unable to do the same for a macro in an open Add-in.
If I try
'MyAddin.xla'!'Foo "param1"'
I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot
be found.
I can invoke Macros with no parameters in the Add-in.
Anybody know why this is not working, and if there is a way to make it
work?
Thanks in advance.
--
Dave Peterson
ec35720@msn.com
|
| 759 |
Hi Bruce
Try something like
Columns(1).entireColumn.Hidden= (WorksheetFunction.Counta(Columns(1)=0)
Columns(2).entireColumn.Hidden= (WorksheetFunction.Counta(Columns(2)=0)
--
"Bruce" <bruce@nha.co.za wrote in message
news:108e501c21ce9$b7e82fc0$36ef2ecf@tkmsftngxa12...
| Hi
|
| I have a macxro which prints a certain section of a
| spreadsheet. I have two comlumns which are optional - if
| these hav not been used then I would like to prevent them
| from being printed. If they have values then they can be
| printed - is there a way to do this?
|
| any help would be much appreciated.
|
| 764 |
Hi Wim
Look into DataConsolidation.
--
"Wim Vanmaele" <vanmaelew@hotmail.com wrote in message
news:9a87c3e7.0206260115.1484365@posting.google.com...
| Hi,
|
| I made a XLS-woorkbook in which data are used obtaining statistical
| results and charts. All data used, could be found on several
| XLS-sheets which have a uniform form. Is there someone who can tell
| me, how I can insert one of thes xls-sheets dynamicaly. So, when
| calculating, I can enter somewhere the wanted xls-file-name and all
| data are so automatically retrieved.
|
| Thanks,
|
| Wim Vanmaele
|
| 767 |
Hi Dolores
DataFilterAuto filter
Custom
Does not contain: +
OK
F5
Special
Visible cells
Copy
Paste
You could also use the Advanced Filter with:
<*+
as the criteria below the copied heading
--
"Dolores" <dolores_s24@yahoo.com wrote in message
news:1259e01c21cf4$0023a850$9be62ecf@tkmsftngxa03...
| I have a column containing cells with only text. Like this;
|
| HHT
| AFG
| PJK
| AFG+
| BSSR
| PJK+
| YTTX
|
| I would like to make a filtered copy of this column where
| I don't have the cells containing the "+".
|
| How do I do this?
|
|
| 775 |
Hi Jeffrey
Just to add to what Charles has said, any Function which *requires* a range
as it's argument(s) cannot be used in a Calculated field of a Pivot Table.
That's about 90% of Excels functions :o(
--
"Charles Williams" <Charles@DecisionModels.com wrote in message
news:3d199c6b$0$27345$afc38c87@news.easynet.co.uk...
| Hi Jeffrey,
|
| Excel will not let you use DSUM as a function in a Pivot Table calculated
| field, you would have to create extra columns.
|
| Charles Williams
| ______________________
| Decision Models
| The Excel Calculation Site
| www.DecisionModels.com
|
| "Jeffrey S. Pidel" <Jeffrey.Pidel@comcast.net wrote in message
| news:ORF#KsKHCHA.1604@tkmsftngp12...
| I am trying to create a Pivot tables with calculated fields
| that compare
| YTD and average per month per account
| of previous month
| versus
| YTD and actual per month per account
| of current month
| I am thinking of using dsum and daverage for the
| calculated fields. Do you think this will work
|
| Table layout
| Previous Month Current Month
| Accounts YTD Average YTD Actual
| 1000
| 2000
| 3000
| 4000
| .
| .
|
|
|
|
|
|
|
|
|
| 778 |
Hi Ireland
If only text use:
=INDEX(B1:B100,MATCH("*",B1:B100,-1),1)
If Numeric use:
=INDEX(B1:B100,MATCH(-9E+306,B1:B100,-1),1)
Both will look past blank cells.
You might also find this link of great use:
/Excel/DynamicRanges.htm
--
"IrelandA" <IrelandA@Syntegra.Bt.Co.Uk wrote in message
news:afc854$r0f$1@pheidippides.axion.bt.co.uk...
| Dear All
|
| Is there a formula which would show me the last popuated cell contents in
a
| column:
| e.g.
|
| Column B
| Row 1 J Smith
| Row 2 J Bloggs
| Row 3 J Doe
| Row 4 <blank
| Row 5 <blank
|
| Formula would return "J Doe".
| Later, if I entered "P Jones" in cell B4, the formula would automatically
| update with the new details.
|
| Thanks for any advice
|
| Al
|
|
|
| 794 |
In article <s2DR8.14$926.5678@vicpull1.telstra.net, DavidH@OzGrid.com
says...
Hello Dave,
When this goes to different filenames - I do not have the option of
replacing filenames. Then it is like - say - total of A7 of sheet JT101
of 100 different files named serially (though the formulas would turn out
to be lot more complex than this).
Or is there any other way that I can pick up data from different sheets
of different files dynamically and cummulate that into a results a new
file / sheet?
Thanx for the help.
--
Jasvinder Taneja
Hi Jasvinder
RE: That would be very cumbersome
What is so cumbersome about pushing Ctrl+H an typing 2 sheet names?
RE: they would not even reside in the same file
Then the INDIRECT function will be of no use. The ADDRESS and INDIRECT
function are both Volatile functions, so if you are too liberal with their
use you will be heading in recalculation problems.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
If yu
"Jasvinder Taneja" <jassitCORRECT@THIShotmail.com wrote in message
news:MPG.17814d5763ebe694989681@msnews.microsoft.com...
| In article <u4BR8.7$926.1707@vicpull1.telstra.net, DavidH@OzGrid.com
| says...
|
| Hi Dave
|
| That would be very cumbersome and moreover the sheets would go on
| increasing. In the long run, they would not even reside in the same file.
| So I would also need to somehow build the file names into the formula
| (the filenames could also be standardized also).
|
|
| --
|
| Jasvinder Taneja
|
|
| Better ignore that I misread the question :o)
|
| Just copy them into the other Columns, then use EditReplace to convert
the
| sheet names.
|
| 805 |
I posted this through google, but it seemed to crash. So
I thought I'd use CDO:
I _think_ I understand:
I dropped a button from the forms toolbar onto a
worksheet. I have 3 examples of the .onaction stuff.
Comment out two of them.
Option Explicit
Sub testme()
Dim cb As Button
Set cb = ActiveSheet.Buttons(1)
Dim c As Range
Set c = Range("A1")
c.Value = "rangeA1value"
Dim i As Long
i = 8
With cb
.OnAction = ThisWorkbook.Name _
& "!'testme1 " & Chr(34) & c.Value & Chr(34)
& "'"
.OnAction = ThisWorkbook.Name _
& "!'testme2 ""Hello"", 3'"
.OnAction = Workbooks("book2.xls").Name _
& "!'testme3 " & Chr(34) _
& "different wkbk" & Chr(34) & "'"
End With
End Sub
Sub testme1(myval As Variant)
MsgBox "From Testme1: " & myval
End Sub
Sub testme2(mystr As String, myval As Long)
MsgBox "From Testme2: " & mystr & " " & myval * 10
End Sub
and from Book2.xls
Option Explicit
Sub testme3(mystr As String)
MsgBox "From " & ThisWorkbook.Name & ": " & mystr
End Sub
I've never used the CDO before. Watch out for linewrap
problems.
-----Original Message-----
Thanks Dave,
However my problem is not calling it from within a
VBA macro but I want
an Excel developer who may know nothing about VBA to pass
parameters to a
VBA macro contained in a loaded Add-in from a button he
has added to the
spreadsheet.
The syntax I used ('MyAddin.xla'!'Foo "param1"') from
within the Assign
Macro dialog of the excel button control seemed to be
ignoring the
'MyAddin.xla' prefix and assuming it was in the workbook
containing the
button (Book1.xls).
Steve
What I was hoping for was
"Dave Peterson" <ec35720@msn.com wrote in message
news:3D18E8DF.4D4DEB54@msn.com...
When I tried to pass a string to your Msg sub, I could
do it like this:
Application.Run "'book1.xla'!msg", "hi there!"
I could also do it like this, if I did a
Tools|references and set a
reference to
that other project.
msg "hi there2"
But I'm not sure if this was the answer to your
question....
Steve Legg wrote:
Hi,
I am able to pass parameters to a VBA macro from
the 'Assign Macro'
dialog by enclosing in single quotes.
e.g.
Book1!'Msg "Hello"'
to execute the macro in the open workbook 'Book1':
Public Sub Msg(m As String)
MsgBox m
End Sub
However I am unable to do the same for a macro in an
open Add-in.
If I try
'MyAddin.xla'!'Foo "param1"'
I am getting an error saying The
macro 'Book1'!'Foo "param1"' cannot
be found.
I can invoke Macros with no parameters in the Add-in.
Anybody know why this is not working, and if there is
a way to make it
work?
Thanks in advance.
--
Dave Peterson
ec35720@msn.com
.
|
| 835 |
Hi Gianfranco, (posted with email copy)
Add-ins, Private Subs, and User Defined Functions
to not appear in the macro list. Was that the only
problem, or are they also not working.
Email copy sent, if you wish to continue this question
please keep conversation in this thread within the newsgroup.
You are connected directly to the newsgroups using
Microsoft CDO there have been recent problems
but they are fixed on Microsoft's side. I would suggest
that Outlook Express is a better vehicle for reading
newsgroups than anything web based.
microsoft.public.excel no longer exists on microsoft
servers, the correct newsgroup is
microsoft.public.excel.misc
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Gianfranco Pellacani" <gianfranco_pellacani@NOSPAMcomergroup.com wrote in message
news:ec6901c21c3b$41462740$95e62ecf@tkmsftngxs02...
Hi, Itried creating an add-in tohave some Macros and
functions of my own available on any workbook, but Ican't
seem to be able access the macros and funs on the add-in I
create after I install it with the add-in manager.
I can access the code with the VB Editor, but can't find
neither macros' name on the "run macro" menu nor
functions' name in the insert "function menu"
Guess there's something wrong with the code itself, but I
can't figure out what exactly.
Please mail me an answer directly, since I got problem
accessing newsgroups.
In case you do, please remove "NOSPAM" from address.
Thanks a lot
|
| 836 |
Hi Dave,
I think I may have made it unclear what I
am trying achieve.
I would like any old Excel developer to
call an existing macro in an excel add-in I
have written, passing parameters to it without
the need to create a Macro themselves.
The way I perceived it would work is by the
developer dragging a button from the 'Forms'
toolbar (which I wrongly called a control) and
then right-button mouse clicking on the button
and selecting the 'Assign Macro...' dialog, and
from there just typing in something like:
'MyAddin.xla'!'MyMacro "param1","param2"'
without worrying about any VBA code.
Sorry I confused things with the example I gave..
Thanks again,
Steve
"Dave Peterson" <ec35720@msn.com wrote in message
news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05...
I posted this through google, but it seemed to crash. So
I thought I'd use CDO:
I _think_ I understand:
I dropped a button from the forms toolbar onto a
worksheet. I have 3 examples of the .onaction stuff.
Comment out two of them.
Option Explicit
Sub testme()
Dim cb As Button
Set cb = ActiveSheet.Buttons(1)
Dim c As Range
Set c = Range("A1")
c.Value = "rangeA1value"
Dim i As Long
i = 8
With cb
.OnAction = ThisWorkbook.Name _
& "!'testme1 " & Chr(34) & c.Value & Chr(34)
& "'"
.OnAction = ThisWorkbook.Name _
& "!'testme2 ""Hello"", 3'"
.OnAction = Workbooks("book2.xls").Name _
& "!'testme3 " & Chr(34) _
& "different wkbk" & Chr(34) & "'"
End With
End Sub
Sub testme1(myval As Variant)
MsgBox "From Testme1: " & myval
End Sub
Sub testme2(mystr As String, myval As Long)
MsgBox "From Testme2: " & mystr & " " & myval * 10
End Sub
and from Book2.xls
Option Explicit
Sub testme3(mystr As String)
MsgBox "From " & ThisWorkbook.Name & ": " & mystr
End Sub
I've never used the CDO before. Watch out for linewrap
problems.
-----Original Message-----
Thanks Dave,
However my problem is not calling it from within a
VBA macro but I want
an Excel developer who may know nothing about VBA to pass
parameters to a
VBA macro contained in a loaded Add-in from a button he
has added to the
spreadsheet.
The syntax I used ('MyAddin.xla'!'Foo "param1"') from
within the Assign
Macro dialog of the excel button control seemed to be
ignoring the
'MyAddin.xla' prefix and assuming it was in the workbook
containing the
button (Book1.xls).
Steve
What I was hoping for was
"Dave Peterson" <ec35720@msn.com wrote in message
news:3D18E8DF.4D4DEB54@msn.com...
When I tried to pass a string to your Msg sub, I could
do it like this:
Application.Run "'book1.xla'!msg", "hi there!"
I could also do it like this, if I did a
Tools|references and set a
reference to
that other project.
msg "hi there2"
But I'm not sure if this was the answer to your
question....
Steve Legg wrote:
Hi,
I am able to pass parameters to a VBA macro from
the 'Assign Macro'
dialog by enclosing in single quotes.
e.g.
Book1!'Msg "Hello"'
to execute the macro in the open workbook 'Book1':
Public Sub Msg(m As String)
MsgBox m
End Sub
However I am unable to do the same for a macro in an
open Add-in.
If I try
'MyAddin.xla'!'Foo "param1"'
I am getting an error saying The
macro 'Book1'!'Foo "param1"' cannot
be found.
I can invoke Macros with no parameters in the Add-in.
Anybody know why this is not working, and if there is
a way to make it
work?
Thanks in advance.
--
Dave Peterson
ec35720@msn.com
.
|
| 842 |
Thank you Steven for the information...
They also mentioned Lookup......but I can't seem to be able to add that
function in.
It's an add-in.
Thanks....
JR
"Steven McDonald" <a@a.com wrote in message
news:12a8c01c21d27$b9729720$3aef2ecf@TKMSFTNGXA09...
Hi,
You don't really need a macro for this. The VLOOKUP
commmand should do the job nicely. Just search for vlookup
in excel help for all the info you need.
It would be easier if your sheet containing part numbers
and descriptions was a different sheet in the same
workbook, rather than in another workbook.
HTH,
Steve
-----Original Message-----
Hello,
I have a form template (write-up rev 031902) and need
help with the
following:
In my form I have B14......B29 ...in these cells I put
in a part number...
and would like the description for that part number to be
added into
C14....C29
For the description and part number I have created
another workbook
named "partdesc"......were column A..has the part numbers
and they start
from Cell 3 down and column B has the description for
those part numbers.
How can I write a macro for this...
Thanks
--
JR
.
|
| 864 |
You can quickly get a list of duplicate names
listed along with their counts by using the
Excel add-in "Refinate". It is free to try.
To count duplicates:
-------------------------------------
Turn off Refinate's GoTo_Duplicates option.
Select the list of names and click Get/Test.
-----Done counting duplicates--------
Every duplicate will be counted and the name
along with it's count will be in a comment box.
You can paste the contents of the comment box
into cells to do more work on those results.
Use =proper(B1) to set the case if necessary.
Refinate works on comma delimited entries so, if
your addresses have commas, then first use
ExcelReplace to change commas to another
character (e.g. " ` ") until after the duplicate
test and then Replace it back with a comma.
That will count duplicates - not singles.
You can get counts of singles, too. One way would
be to copy the column of interest into another
column and select both columns at once and click
Refinate's Get/Test. Now there will be a
duplicate of everything -- the count is doubled.
To divide by 2, copy the contents of the comment
box into cells and apply Refinate's TextToColumns
to put the counts into a separate column. Then copy
a cell containing the value 2 to the clipboard.
Select the totals and EditPasteSpecialDivide.
Now you have the count for singles and duplicates.
If you would like to be brought to each duplicate
and have a tag placed at each one, turn on
Refinate's GoTo_Duplicates. If you edit the
duplicate, the changes will be noted in the comment
tag as a log.
--Brian Taylor
Refinate (C)2001, 20 free trials.
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, much more.
www.adetaylor.com
"larry" <lwhersey@aol.com wrote in message
news:10a8701c21dab$b1b9eb80$9ee62ecf@tkmsftngxa05...
if I am entering a list of names or addresses in a
column
How can I count the number of times the same name or
address appears.
Example
Column A
smith
jones
AL
Smith
frank
todd
smith=2
|
| 971 |
Yep. the way it's written.
If you wanted the cell formatting to revert to none when you clearcontents, you
could do something like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing _
And Date = DateSerial(2002, 6, 1) Then
If Not IsEmpty(Target) Then
Target.Interior.ColorIndex = 36
Else
Target.Interior.ColorIndex = xlNone
End If
End If
End Sub
You could still change the cell background via the toolbar icon or by
Format|cells if you want to have something in the cell and non-red fill.
Garry Sabino wrote:
Ooopss.... it seems that I cant undo any changes I made on the cells were
the event is applied. Is it meant to work that way?
Garry
"Garry Sabino" <oas@brunet.bn wrote in message
news:uYuYdraHCHA.1604@tkmsftngp09...
Super duper. Thanks a lot Dave, you're a Hero.
Garry
"" <DavidH@OzGrid.com wrote in message
news:lJyS8.38$eA6.34565@vicpull1.telstra.net...
Hi Gary
This one will apply to A1:A10
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Me.Range("A1:A10")) _
Is Nothing And Date = DateSerial(2002, 6, 1) Then
Target.Interior.ColorIndex = 36
End If
End Sub
If the cells are non-contiguous then use something like:
If Not Intersect(Target, Me.Range("A1:A10,C5:D20"))
--
FREE EXCEL NEWSLETTER
/News/2home.htm
Get the OzGrid Add-in
/Services/AddinExamples.htm
If it's Excel, then it's us!
"Garry Sabino" <oas@brunet.bn wrote in message
news:OZPKxcaHCHA.1604@tkmsftngp09...
| That's super but it only applies to cell A1. How can I apply the same
on
a
| range of cells.
|
| Thanks a lot,
|
| Garry
|
|
| "" <DavidH@OzGrid.com wrote in message
| news:lvxS8.27$eA6.30865@vicpull1.telstra.net...
| Hi Gary
|
| Right click on the sheet name tab, select "View Code" and paste in
the
| code
| below:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| If Target.Cells.Count 1 Then Exit Sub
|
| If Target.Address = "$A$1" And _
| Date = DateSerial(2002, 6, 1) Then
| Target.Interior.ColorIndex = 36
| End If
| End Sub
|
| Just change A1 to suit your needs. I have a UDF here:
| /VBA/ReturnCellColor.htm that you can use to
easily
| get
| the ColorIndex Property of a cells fill color.
|
|
| Hope this helps
|
|
| --
|
|
|
|
| FREE EXCEL NEWSLETTER
| /News/2home.htm
| Get the OzGrid Add-in
| /Services/AddinExamples.htm
| If it's Excel, then it's us!
|
|
|
| "Garry Sabino" <oas@brunet.bn wrote in message
| news:OAkolkZHCHA.2200@tkmsftngp08...
| | Hi,
| |
| | How do I change the color of a cell value if it is entered or
modified
| after
| | an specific date.
| |
| | Say I'd like all cells value that are modified or entered after
June
1,
| 2002
| | to become red in color.
| |
| | Any suggestions would be highly appreciated.
| |
| | Garry.
| |
| |
|
|
|
|
--
Dave Peterson
ec35720@msn.com
|
| 1012 |
In the userform's code module, assuming that your text box is named
textBox1, add the following code:
Private Sub TextBox1_Change()
'call the validate routine and pass to it the textbox object
Validate_Number_Entry Me.TextBox1
End Sub
In a regular module, put the following:
Sub Validate_Number_Entry(oBox)
Dim tempS As String
'store the text in a variable for later use
tempS = oBox.Text
'if user has removed all entries, just exit
If tempS = "" Then Exit Sub
'if the entry is numeric, then exit
If IsNumeric(tempS) Then Exit Sub
'if the entry is not numeric, remove the last entry
oBox.Text = Mid(tempS, 1, Len(tempS) - 1)
End Sub
Bob Flanagan
Macro Systems
-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
"phil" <pperry@acsoft.co.uk wrote in message
news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12...
Hi,
how can i allow only numbers to be entered into a textbox
on a user form?
thanks
phil
|
| 1015 |
excellent,
thanks
phil
-----Original Message-----
In the userform's code module, assuming that your text
box is named
textBox1, add the following code:
Private Sub TextBox1_Change()
'call the validate routine and pass to it the textbox
object
Validate_Number_Entry Me.TextBox1
End Sub
In a regular module, put the following:
Sub Validate_Number_Entry(oBox)
Dim tempS As String
'store the text in a variable for later use
tempS = oBox.Text
'if user has removed all entries, just exit
If tempS = "" Then Exit Sub
'if the entry is numeric, then exit
If IsNumeric(tempS) Then Exit Sub
'if the entry is not numeric, remove the last entry
oBox.Text = Mid(tempS, 1, Len(tempS) - 1)
End Sub
Bob Flanagan
Macro Systems
-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel
"phil" <pperry@acsoft.co.uk wrote in message
news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12...
Hi,
how can i allow only numbers to be entered into a
textbox
on a user form?
thanks
phil
.
|
| 1094 |
Hi
Thanks for the replies!!!
Well the spreadsheets are from my companies supplier: and are a list of
products.
From time to time the product prices change, new products appear and some
obviously get discontinued.
There are over 1500 different products on this speadsheet!
The row consists of fields stating "product category", "product code",
"description", "price ex VAT", "price Inc VAT",
The product code is unique.
When I am sent an updated spreadsheet, I want to be able to compare this
with the previous, and easily see what products have changed, and what
products are new!
Hopefully you have the answer! I am looking at the Compare add-in stated by
Harald. Each time a new spreadsheet is recieved, the total number of
products is likely to be different!
Many thanks again in advance
Matt
"Tom Ogilvy" <twogilvy@msn.com wrote in message
news:uDxyeQ3HCHA.1712@tkmsftngp08...
That should be doable.
Since you don't define what makes a row the same or different or how to
compare between sheets or how your data is laid out, it would be difficult
to provide any additional information.
Regards,
Tom Ogilvy
Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message
news:afjoak$fag$1@news6.svr.pol.co.uk...
Hi All
I have a workbook that contains similar worksheets of data.
Is there an easy way of taking two worksheets, original and new and
running
a function that will compare the |