add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 27 | I believe I am just making the built in scrollbar visible. How do you add code to make the contents of a form "scroll?" -----Original Message----- Set the width and height properties to increase or decrease the size. Are you just making the builtin scrollbar visible? Have you added code to the scroll event of the userform to manage the userform? Martin <MartinCregg@ncodeinc.com wrote in message news:d44101c2141c$7ca0fbc0$36ef2ecf@tkmsftngxa12... I am adding controls to a form directly using Excel VBA. Depending on certain previously selected options the number of controls I add wiil increase thus they appear "off the form." I was hoping to add a scrollbar and scroll down to reveal the added forms. However, the scroll bar does nothing. On a related note, is there a way of changing the size of a form once it is active. . |
| 32 | Hi, Tom. I want to "set" the custom format for Textbox2. I am getting the value for Textbox2 from Textbox1. As I exit Textbox1, I give Textbox2 the value of (1-Textbox1.value). For example, I don't want to see "0.5" in Textbox2, but ".5". On the Excel spreadsheet, I have a custom NumberFormat ".0". I unsuccessfully tried Format for both Value and Text: Private Sub Textbox2_Change() Format (tbxTextbox2.Value), ".0" ' Format (tbxTextbox2.Text), ".0" End Sub and, also Private Sub Textbox1_Exit() Format (tbxTextbox2.value), ".0" End Sub Any ideas, Tom? Thanks for your help. "Tom Ogilvy" <twogilvy@msn.com wrote in message news:e##sjhVFCHA.1716@tkmsftngp07... for the most part, it should work. You have to use the Format command. If you are filling the textbox from the cell with code, it might be easier to do Textbox1.Text = Range("A1").Text You might want to post the specifics. Regards, Tom Ogilvy Splash <splash@mosquitonet.com wrote in message news:ugpfmqgvnqmec7@corp.supernews.com... I have defined a custom "NumberFormat" for my Excel cells. Is there a way to make that same custom format apply to a textbox in a userform that runs from an Excel macro? (I've asked the VBA group, with no responses). Thanks, guys. |
| 53 | I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins |
| 63 | John Walkenbach has some example code for playing a .wav file: -walk.com/ss/excel/tips/tip59.htm -walk.com/ss/excel/tips/tip87.htm Should get you going into the right direction... HTH Jason Atlanta, GA -----Original Message----- I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins . |
| 111 | I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 114 | Dan, ActiveWorkbook.Worksheets.Count Workbooks("My Workbook.xls").Worksheets.Count HTH, Bernie Dan Flannery wrote: I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 139 | Because you are creating multiple inter-related sets of data (relational tables) for inventory, invoicing, payables, and jobs, MS Access is the optimum application to use. Access has the ability to create data entry and display forms, as well as reports that provide the specific management information structure desired. However, the flat tables in MS Excel, if constructed properly, can be related using MS Query, and reports can be generated with VBA Code and Userforms. -----Original Message----- Hi all I work for a small building business in the uk. We need a program that will moniter all our stock as well as the the jobs in progress ie: Moniter stock Moniter payments in(interim etc) Moniter payments out Moniter several ongoing jobs at once We've have had no luck as yet in our search (Iv'e been on google all day) I was wondering if it might be better to MS access or excel Any input most welcome Cheers Jill . |
| 151 | I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 162 | Sub CreateList3() For Each ws In Sheets If ws.Name < "Foremen" Then w = ws.Name With Sheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets(w).Cells(4, 2) .Cells(Rows.Count, "B").End(xlUp).Offset(1) = Sheets(w).Cells(5, 2) .Cells(Rows.Count, "C").End(xlUp).Offset(1) = Sheets(w).Cells(7, 1) End With End If Next End Sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bill Kelly" <bkelly@pilchuck-usa.com wrote in message news:OJAXLPuFCHA.1916@tkmsftngp13... I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 164 | You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom |
| 202 | Option Explicit Sub testme() CommandBars("Toolbar List").Enabled = False CommandBars("Tools").Controls("Customize...").Enabled = False End Sub The top one stops the right clicking on an empty spot on the toolbar area and choosing "customize..." from the bottom. The second one stops the Tools|Customize... (But you can still double click on an empty spot on the toolbar area and get the customize dialog.) Matthew Jackson wrote: I'm having trouble trying to figure out how to disable the "Customize..." Dialog Box in Excel 2000. If someone could send me a VBA code snippet that would be great. Thanks, Matt -- Dave Peterson ec35720@msn.com |
| 217 | Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 223 | See my page on Events /dmcritchie/excel/event.htm Unlike regular macros which are installed in regular modules, Workbook Events are installed in ThisWorkBook the following manner: F11 (Visual Basic Editor), Ctrl+R (VBA Project), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7). HTH, , Microsoft MVP - Excel My Excel Macros: /dmcritchie/excel/excel.htm "LK" <lazark@precisemailing.com wrote in message news:e4f701c21718$438b45d0$2ae2c90a@hosting.microsoft.com... Well as a novice, this makes no sense to me at all. Can you repost this making it easier to understand. Thanks -----Original Message----- One way for the novice users in thisworkbook Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) ' will clear the clipboard when the sheet is deactivated, will fire each 'time the user switches to a new sheet to paste the data. Application.CutCopyMode = False End Sub Dave -----Original Message----- Howdy, Does anyone know how to prevent someone from using the copy function on an excel file. I want to be able to distribute a file to my office and only allow people to view it. I know how to block them from edit the file itself by protecting the workbook, but people can still copy and past the info into a new sheet. Is there a way to stop this? Thanks, Eddie LeBreton . . |
| 227 | Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 228 | Oops, the hyphen must be the last character in the list! Change it to this: Const CharList As String = "[A-Za-z0-9._-]" -John "John Walkenbach" <john@j-walk.com wrote in message news:O4TZOCyFCHA.2076@tkmsftngp04... Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 311 | Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 375 | thanks, this is a great start! but, boy, the palette size is an annoying limitation. some more questions: 1. can i set up a new color palette by assigning my own specific rgb values to the 56 colors in the palette? 2. can i change color palettes when i change worksheets? that way i could give the "illusion" of having more colors by only having 56 per page...? thanks, mark "John Walkenbach" <john@j-walk.com wrote in message news:uQxYnG9FCHA.1424@tkmsftngp04... Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 380 | 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. |
| 387 | Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com |
| 393 | I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 395 | Hi Dave, Just tried your suggestion and no luck. I rebooted and opened the file in Excel - didn't ask if there were any macros. I then followed your instructions to see if there were any macros - there are none. I'm going to download the program you just mentioned and try that. I'll post back here if that doesn't work. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D125E1D.FB0944ED@msn.com... I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 400 | It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 425 | Hi Dave, I think you've hit on something! I just tried Excel in Safe Mode and it will print! It will not, however, when I'm in regular mode. When I enter VBE, the following are listed in the Project - Project VBA window - Start (OR Start.xla) TbRun97Project (TRRUN9.XLS) VBAPROJECT (FERPA.xls) The are sub headings under the last one, which is also the current workbook. The first two items are mysteries to me and, when I click on them, ask for a password. (Why? I haven't a clue.) I also just tried unchecking all of the addins in Tools/Options (there was only one checked) and tried to find the XLStart folder (I couldn't find it using Excel but believe it may be under Windows, Application Data, Microsoft, Excel. I removed the one file found there and rebooted Excel. Still can't print. This is VERY strange - apparently, you did find something as I can print the file in safe mode. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D127B06.5AB8B95D@msn.com... It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 426 | Thnaks for your help! -----Original Message----- Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com . |
| 455 | Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 500 | Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 506 | You could have a macro open each text(?) file individually and then have your macro do the copy and paste. But if you recall your old DOS commands, there was a way to merge text files into one. Let's say they are named MyFile0001.txt through myFile0999.txt. And they're in the same folder. Just shell to DOS (Windows Start button|Programs|MS DOS Prompt (for win98)) May be "command prompt" in NT (IIRC). Then traverse to that folder. when you're there, just type: copy myfile*.txt allfiles.txt All the files that start with myfile and have an extension of .txt will be copied into allfiles.txt. Then Exit from the dos prompt and open that one in excel. ======== There are a few warnings about the DOS copy command. You couldn't do this without getting into trouble. copy *.txt allfiles.txt It would try to add allfiles.txt to allfiles.txt. You could even do something like: copy c:\mydir\*.* c:\allfiles.txt by putting the "to" file into a separate folder, we can combine all the files using a wildcard. ======== If you still want a macro to open up each text file and do the copy and paste, post back with some more details. Are the files all in one folder? Are they the only files in that folder? If not is there a unique identifier for the group? (It's kind of the same answers you need to do the DOS copy solution, too.) good luck, KK wrote: Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK -- Dave Peterson ec35720@msn.com |
| 533 | Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 538 | Excel 2002 Power Programming With VBA by John Walkenbach is one (I have that one), he also wrote a dummies book although I would never buy a dummies book albeit being one, it's too insulting <g -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:O$uAHWhGCHA.2684@tkmsftngp10... Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 553 | Phil - 1/ You could look up Data Type Summary in VBA help, which leads to a description of the various data types. 2/ You could set up a moving average in your worksheet. Say you have values in B2:B100, and you want a moving average of six consecutive numbers. In C7, enter this formula =average(B2:B7) And drag this down to C100. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1075a01c21858$c6327760$9be62ecf@tkmsftngxa03, Phil said... Hi, I'm using excel to plot some experimentally determined data, however the resulting chart has glitches due to rounding errors. To what accuracy does excel store numbers? And, is there a smoothing type of function for plotted data, whereby say, 6 consecutive numbers, for example, are averaged to give a smoother curve? Thanks Phil |
| 593 | 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 |
| 594 | 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 |
| 596 | 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 |
| 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 | | | | | | | | |
| 602 | Thanks for this recommendation. Does it matter that John's book covers XL2002 (and I have XL2000)? In other words, is the VBA the same 'version' for each? "Peo Sjoblom" <terre08@mvps.org wrote in message news:e8g6CxhGCHA.2580@tkmsftngp09... Excel 2002 Power Programming With VBA by John Walkenbach is one (I have that one), he also wrote a dummies book although I would never buy a dummies book albeit being one, it's too insulting <g -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:O$uAHWhGCHA.2684@tkmsftngp10... Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 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 |
| 607 | Thank you for your comprehensive reply. "Peo Sjoblom" <terre08@mvps.org wrote in message news:e$g#P8rGCHA.612@tkmsftngp08... There are some new things that relates to 2002 only but on the whole everything you need for 2000 is there as well, I think that it could be named "Microsoft Excel 2000/2002: Power Programming with VBA, there is a 2000 version named "Microsoft Excel 2000: Power Programming with VBA", don't know if it is still in print but a quick web search at half.com turned up several new copies.. Since 2002 is the latest version I'd recommend that book in case you have to work with someone who has 2002... -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:Oi5MweqGCHA.2568@tkmsftngp10... Thanks for this recommendation. Does it matter that John's book covers XL2002 (and I have XL2000)? In other words, is the VBA the same 'version' for each? "Peo Sjoblom" <terre08@mvps.org wrote in message news:e8g6CxhGCHA.2580@tkmsftngp09... Excel 2002 Power Programming With VBA by John Walkenbach is one (I have that one), he also wrote a dummies book although I would never buy a dummies book albeit being one, it's too insulting <g -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:O$uAHWhGCHA.2684@tkmsftngp10... Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 609 | "Max Foo" <maxfoo@punkass.com wrote in message news:i9qbhu01v2lckrtn77r14ng8tqf4j73b04@4ax.com... On Sun, 23 Jun 2002 14:50:32 GMT, "Mike" <mike@nospam.com wrote: "Max Foo" <maxfoo@punkass.com wrote in message news:j5ibhuc7fqdidrd9j1ifvu8eau5ag3b3ps@4ax.com... What exactly was the probably you were having with Excel. Every one of the requirements you listed can be done in Excel. Not really. Engineering units, for example, in which the powers of 10 used are multiples of 3, aren't possible in Excel. It would be even nicer if yes, this can be done easily.. right click select 'format cell', go to custom or run this macro with the cell you want formatted changed. Selection.NumberFormat = "##0.0E+0" Hey, imagine that. It actually works... but there's certainly nothing obvious about it, is there? There's nothing in the help under "Engineering Notation," or under Number, or under Format... In fact, here's the complete text of the scientific number format help from the number format codes help page: ------------------- Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. If a format contains a 0 (zero) or # (number sign) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of 0's or #'s to the right of a code determines the number of digits in the exponent. E- or e- places a minus sign by negative exponents. E+ or e+ places a minus sign by negative exponents and a plus sign by positive exponents. ------------------- I know: I'm looking in the wrong place. I should have looked under number formats on the left side of the decimal point, and inferred what happens when I combine them with scientific notation. It sure would be easier if there was something like an "engineering notation" option, wouldn't it? Excel understood engineering suffixes the way SPICE does, and could use them in plots. As an engineer, 10u is much easier to read and understand than this too can be done.. define a function using VBA. The last thing anyone should have to do is learn a programming language to make a plot. 1E-5. Excel requires that log axes begin and end on powers of 10, so if your data extends from 1 to 5, the axis still extends from 1 to 10. That's just stupid, and it can't be changed. Excel also strangely wants linear axes to again it seems your too lazy to setup the graph manually, or automate it will VBA. Lazy my ass, buddy. I did try it manually, and verified that it works the way I stated, before I sent the first message. Maybe there is some macro that will do it, but it's certainly not obvious, and it certainly doesn't work by using the manual settings. Choose a maximum value of 5 on a log scaled axis, and Excel conveniently changes it to 10 when you click OK. Maybe you could write a macro to do this, but I can't find any documentation on it. Why not? If it's easy to do, shouldn't it be easy to find? ... Mike, you need to learn Excel VBA than anything is possible... Where do you get off with this crud? It'll do what you want, as long as you write the progam to do what you want? -- Mike -- |
| 621 | Unless you are talking about using VBA code to do this, just using worksheet functions - then the formula that displays the results would have to be in the cell where the results would be displayed. In this case, the formula would have to be in column C. Column A would have not role. in C1 =If(len(trim(D1))=0,"",D1) would be an example. John Vera <emperorj@hotmail.com wrote in message news:12cc101c21ba9$faeaf3c0$35ef2ecf@TKMSFTNGXA11... Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). Thanks in advance, John |
| 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. | . | |
| 653 | Hello All, I've been working with charts that generate into userforms and I'm having alot of fun with it (can you tell that I'm in finance...). Anyway, I've been using John Walkenbach's fabulous code from the "Power Programming with VBA". It's working really well right now, but what I'd like to do is have more than one dataobject when I generate the graph. I'll show you what I have so far... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) UserRow = ActiveCell.Row If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then MsgBox "Move the cell cursor to a row that contains data." Exit Sub End If CreateChart (UserRow) UserForm1.Show End Sub Sub CreateChart(r) Dim Charttitle1 As String Dim ChartTitle2 As String Dim Charttitle3 As String Dim Tempchar As Chart Application.ScreenUpdating = False Set CatTitles = ActiveSheet.Range("E2:AZ2") Set ScrRange = ActiveSheet.Range(Cells(r, 5), Cells(r, 52)) Charttitle1 = Cells(r, 2) ChartTitle2 = Cells(r, 3) Charttitle3 = Cells(r, 1) Set SourceData = Union(CatTitles, ScrRange) Set tempchart = Charts.Add With tempchart .ChartType = xlLineMarkers .SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False .ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False .HasTitle = True .Charttitle.Text = "Transit: " & Charttitle3 & " " & Charttitle1 & "-" & ChartTitle2 .Charttitle.Font.Bold = True .Charttitle.Font.Size = 12 '.Axes(xlValue).MaximumScale = 30000 .Axes(xlCategory).TickLabels.Font.Size = 6 .Axes(xlCategory).TickLabels.Orientation = xlHorizontal .Location where:=xlLocationAsObject, Name:="Volumes" End With With ActiveSheet.ChartObjects(1) .Width = 600 .Height = 300 .Visible = False End With Private Sub UserForm_Initialize() Set currentchart = ActiveSheet.ChartObjects(1).Chart fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif" currentchart.Export Filename:=fname, filtername:="GIF" Sheets("volumes").ChartObjects(1).Delete Image1.Picture = LoadPicture(fname) UserForm1.Caption = "Charts 'n Stuff" Application.ScreenUpdating = True End Sub Is there any way to modify this code so that the user can select more than one row and be able to compare and contrast the data? Any help would be greatly appreciated, and a special thanks to John for the great resource book he's written, Thanks in advance, Mike |
| 667 | 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. Any help is greatly appreciated. |
| 683 | Tom - Did you use the Save As HTML command to make your GIFs? Make sure you have the latest version of the Internet Assistant? I think I remember reading that the earlier version that shipped with Office 97 didn't work as well as later versions. According to MSKB article Q158079 at /default.aspx?scid=kb;en-us;Q158079 "To obtain the latest version of the Internet Assistant Wizard, go to the following Microsoft Web site: /downloads/9798/html.aspx" Did you try the chart.export method in VBA? Did this give better results? - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <ewtdjZ#GCHA.2056@tkmsftngp12, Tom Moers said... Running XL97 in WinXP, charts saved as .gif file is truncated (right portion is missing). These charts were created in XL7.0 in Win95 and saved as .gif files work perfectly in that envorinment. I have just completed running sr2 to update XL97 and that didn't fix the problem. Any thoughts would be appreciated. Tom |
| 700 | 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. |
| 701 | On Tue, 25 Jun 2002 02:00:07 GMT, Knowledge Seeker <AbsolutelyNoSpamAccepted@earthlink.net wrote: I have a spreadsheet with about 1000 rows that has a column for name. The following are sample names: J. K. Rowling George R. R. Martin Robert J. Sawyer Ken MacLeod Lois McMaster Bujold Walter M. Miller, Jr. C. J. Cherryh Michael P. Kube-McDowell Jerry Pournelle and Larry Niven I need a formula that will take the name and create two new columns. The first new column will contain the last name and the second new column will contain the rest of the name. Can you help me?? -- Knowledge Seeker To reply to me directly, please replace the garbage before the @ sign with the name m-e-s-s-a-g-e-r-e-a-d-e-r (without the dashes) I wrote 2 horrendously complicated VBA functions (100+ lines) to do something very similar, splitting names into First, Mid and Last names columns. I also parsed Dutch style surnames (van der Valk, de Zeuuw) with the prefix as the Middle name, while with French names (Le Brun) all parts are treated as surnames. Of course then, the problem is that both French and Dutch names use 'De' as a surname prefix... Vasant Nanavati wrote in another reply "It's almost impossible to write a parser that will take all possible variations into account." Too right!!! The best you can do is a good guess, based on knowledge of the data you've got. My technique involved splitting the name on whitespace into Collection objects (lists), and then doing various horrendous manipulations with these. It is about 99% successful (on *my* data...) I'll post it if you're interested, but I'd need to clean up work-specific data first. I think that a *better* way to do this would involve regular-expressions, which are a very powerful way of manipulating text. They are already in VBA for Office 2000 (need to set a reference to Windows Scripting Regular Expressions) or something like that. For Office 97, you need to download the Scripting extension from Microsoft's website. (If this is just for personal use, and doesn't require MS-only extensions, you could download Perl from - this contains a PerlScript .dll that you can embed in Excel, and ship out Regular Expression work to that object. Maybe a bit tricky, but very powerful). -------- osfameron /chickenman/ |
| 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 | | | | |
| 713 | Knowledge Seeker <AbsolutelyNoSpamAccepted@earthlink.net wrote in message news:<65jfhu4cnmc8kf1m14rij4lhmhs48m5303@4ax.com... I have a spreadsheet with about 1000 rows that has a column for name. The following are sample names: J. K. Rowling George R. R. Martin Robert J. Sawyer Ken MacLeod Lois McMaster Bujold Walter M. Miller, Jr. C. J. Cherryh Michael P. Kube-McDowell Jerry Pournelle and Larry Niven I need a formula that will take the name and create two new columns. The first new column will contain the last name and the second new column will contain the rest of the name. Can you help me?? As per my last post, here's my sample code: it doesn't do *exactly* what you want, but splits a full-name into First, Mid and Last names. You might be able to modify to your purposes. The meat is the F_M_L_from_F function. Then there are some helper functions, and a test subroutine to show an example. (BTW, if you are using Excel2000, you might not need to define your own Split function, but it doesn't exist in Excel97). This is 268 lines of convoluted horror, and should not be taken as an ideal of programming style... I'm definitely going to refactor/redesign this code at some point, but it might give you some sort of ideas... --------------------- Option Explicit Function F_M_L_from_F(fullname As String) As Collection ' ' Takes a string name, and returns a Collection of ' 3 items (First, Middle, Last) ' ' Horribly complicated because it has to be able to ' deal with quite a lot of complexity ' ' John Brown John Brown ' John M Brown John M Brown ' Hans van Straaten Hans van Straaten ' Hans-van Straaten Hans van Straaten ' Hans van-Straaten Hans van Straaten ' Anne Le Duc Anne Le Duc ' etc. ' (Having said that, it could probably do with being ' rewritten, possibly using something a bit more scalable ' and powerful, like regexes) ' Split the full name into a list (on spaces) Dim names As Collection Set names = split(" ", fullname) ' Create strings for first, mid, last, and various ' temporary variables ' (did I mention that this is a horrible Function) Dim First As String, Mid As String, Last As String Dim newfirst As String Dim mylast As String, myname As String ' Create lists for Middle and Last names Dim mids As New Collection Dim lasts As New Collection ' shift first name onto var 'first' If names.Count 0 Then First = names(1) names.Remove (1) ' Check firstnames for hyphenated suffixes Dim firstnames As New Collection Set firstnames = split("-", First) If firstnames.Count 1 Then newfirst = firstnames(1) firstnames.Remove (1) ' Capitalise first name properly If newfirst Like "[a-z]*" Then newfirst = TCase(newfirst) Dim nextfirst As String nextfirst = firstnames(1) ' If the next 'first name' looks like a surname ' prefix then push it onto the surnames list If nextfirst Like "[a-z]*" _ Or nextfirst Like "Von" Or nextfirst Like "Van" _ Or nextfirst Like "De" Or nextfirst Like "Der" _ Or nextfirst Like "Le" Or nextfirst Like "La" _ Then names.Add join(" ", firstnames), , names.Count First = newfirst Else ' Or, if it is like a middle initial (or two) ' the push explicitly onto the Middle Initials ' list If nextfirst Like "[A-Z]" Or nextfirst Like "[A-Z][A-Z]" Then mids.Add nextfirst firstnames.Remove (1) names.Add join(" ", firstnames), , names.Count First = newfirst End If End If Else: If First Like "[a-z]*" Then First = TCase(First) End If ' Split into Mid & Last Dim is_mid As Boolean is_mid = True While names.Count 0 myname = names(1) names.Remove (1) If is_mid Then ' Some names are added to Middle names, and ' others to Last names. le and la are prepended ' to surnames, while Von, Van, De, Der etc. ' are treated as middle names. If myname Like "[A-Z]" Or myname Like "[A-Z][A-Z]" _ Or (myname Like "[a-z]*" _ And myname < "le" _ And myname < "la") _ Or myname Like "Von" Or myname Like "Van" _ Or myname = "De" Or myname = "Der" Then If myname Like "[a-z]" Then myname = UCase(myname) mids.Add myname Else is_mid = False lasts.Add myname End If Else lasts.Add myname End If Wend ' If we've been too enthusiastic, and not supplied a ' lastname... then get one, either from middle name or ' first name If lasts.Count = 0 Then If mids.Count 0 Then lasts.Add mids(mids.Count) lasts.Add (mylast) mids.Remove (mids.Count) Else lasts.Add (First) First = "" End If End If ' Check last names for hyphenated prefixes Dim newlast As New Collection Dim lastnames As New Collection Set lastnames = split("-", lasts(1)) If lastnames.Count 1 Then lasts.Remove (1) Dim in_prefix As Boolean in_prefix = True While (lastnames.Count 0) mylast = lastnames(1) lastnames.Remove (1) ' If the last name was actually a Middle name ' then push it back onto the middle names If in_prefix = True And (mylast Like "[A-Z]" _ Or mylast Like "[A-Z][A-Z]" _ Or mylast Like "Von" Or mylast Like "Van" _ Or mylast Like "De" Or mylast Like "Der" _ Or mylast Like "von" Or mylast Like "van" _ Or mylast Like "de" Or mylast Like "der") Then mids.Add mylast Else in_prefix = False If mylast Like "[a-z]" Then mylast = TCase(mylast) newlast.Add mylast End If Wend If Not newlast Is Nothing Then Dim pref_last As String Dim mytoken As Variant Dim mytoken1 As String pref_last = join("-", map_TCase(newlast)) If lasts.Count 0 Then lasts.Add pref_last, , 1 Else lasts.Add pref_last End If End If End If ' Generate the complete First, Mid, and Last names ' from the lists we've created Mid = join(" ", mids) Last = join(" ", map_TCase(lasts)) ' Create a new list to put them in Dim res As New Collection res.Add First res.Add Mid res.Add Last ' The return value will be this list Set F_M_L_from_F = res Else ' return a list with 3 empty values... names.Add "" names.Add "" names.Add "" Set F_M_L_from_F = names End If End Function Function join(jstring As String, coll As Collection) As String ' a helper sub. Returns the list supplied interspersed with ' the join string. ' e.g. ' join("-", "Hello", "World", 1,2,3) - Hello-World-1-2-3 Dim token As Variant For Each token In coll If join < "" And token < "" Then join = join & jstring join = join & token Next End Function Function split(split_string As String, orig_string As String) As Collection ' Helper function to return a list from a string ' e.g. ' split(" ", "I like banana splits") ' - "I", "like", "banana", "splits" ' preare the new list Dim coll As New Collection Dim pos pos = 1 Dim token As String Dim my_string As String my_string = orig_string 'copied so as not to modify the original string ' As long as there's some string left to scan, look for the ' split value in it, then add everything to the left of it ' (using the Left operator) to our new list. ' Then Redefine the string as the bit to the right of that ' (using the Mid operator) While my_string < "" And pos < 0 pos = InStr(1, my_string, split_string) If pos 0 Then token = Left(my_string, pos - 1) If token < "" Then coll.Add token my_string = Mid(my_string, pos + Len(split_string)) End If Wend ' By default, only add the string if it's not blank. ' this means that multiple delimiters don't create ' multiple fields. ' e.g. ' split("-", "Hello-----World") ' - "Hello", "World" ' ' This might not be what you want ("Hello","","","","","World") ' but it's the correct default for this particular application. If my_string < "" Then coll.Add my_string Set split = coll End Function Function TCase(orig As String) As String ' Set the string to title case ' e.g. Debug.Print TCase("fRED") - Fred ' ' (Have a feeling that there might be a VBA function ' to do this. If so, then d'oh!) TCase = UCase(Left(orig, 1)) & LCase(Mid(orig, 2)) End Function Function map_TCase(coll As Collection) As Collection ' Set a whole list to Title Case Dim coll2 As New Collection Dim toke As Variant Dim toke2 As String For Each toke In coll ' This kludge with toke2 doesn't always seem ' to be necessary? Very odd! toke2 = toke ' Only modify it if it's all lower or all upper. ' (e.g. we assume that if it already has sOmE capitalization ' then it must be deliberate... e.g. we don't want to flatten ' McCarthy, JoAnn, or other capitalizations from other languages.) If toke2 = LCase(toke2) Or toke = UCase(toke2) Then toke2 = TCase(toke2) coll2.Add toke2 Next Set map_TCase = coll2 End Function Sub test() Dim x As Collection Set x = F_M_L_from_F("willem van der valk") Debug.Print "First: " & x(1) Debug.Print "Mid: " & x(2) Debug.Print "Last: " & x(3) ' Result ' ------ ' First: Willem ' Mid: van der ' Last: Valk End Sub --------------------- Cheerio, osfameron/hakim /chickenman/ |
| 714 | 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. . |
| 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. | | | . | |
| 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. |
| 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 . . |
| 751 | Since XL stores times as fractional days, you can convert minutes to a time by dividing by 24*60, or 1440: A1: 80 A2: =A1/1440 === 1:20 when A2 is formatted as a time. In article <1273e01c21cc9$52668590$19ef2ecf@tkmsftngxa01, Bob <vbats2000@yahoo.com wrote: Here is my dilema I have a simple formula to find minutes and the answer comes out to 80 minutes. In the long run i need that 80 minutes converted to 1h 20 min in any format i would be happy with 1.2 but what i am getting is a percentage such as 1.4. Im sorry if this sounds confusing. What I am doing is ex.=sum(e3/60) where e3 was equal to lets say 80 and it would give me 1.4 instead of what i was looking for in 1.2. Does anyone know the correct formula please. Bob |
| 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 |
| 796 | I am trying to execute a file search with the exact file name specified. The problem is when the exact file does not exist, it still returns file names that are similar, as if there were a wild card specified in the name. I have tried setting .MatchTextExactly to true, but it does not change the result. Is this just a VBA glitch? The following code is what I am using: Set fs = Application.FileSearch With fs .NewSearch 'String containing the path to the file. .LookIn = FilePath .SearchSubFolders = True 'String containing the file name such as "APT.txt" .Filename = UserFileName .MatchTextExactly = True If .Execute 0 Then For I = 1 To .FoundFiles.Count MsgBox .FoundFiles(I) Next I End If End With |
| 797 | I do not understand why the tendency is for suggestions to convert character 160 to nothing. CHR(160) is the non breaking space character ( ) in HTML and should be converted to a normal space. FWIW the CLEAN Worksheet Function is also sometimes suggested and would only affect char(0) through Char(31), Char(129), Char(141), char(143) and Char(144). To fix up unwanted extra spaces afterwards there is TRIM to remove spaces left and right; and replace can be used to replace 2 spaces with 1 space depending on your data. There is a difference between TRIM in Excel and in VBA. Excel will reduce internal spaces, VBA will not. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "George" <george39672002@yahoo.com wrote in message news:1146201c21ab4 I tried the character 160 and wasn't getting any results, so I thought it was my computer and will troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort function works well. Thanks to both of you. |
| 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 . |
| 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 . |
| 853 | In XL: Select your range, then choose Edit/GoTo/Special, select the Blanks radio button. VBA: rng.SpecialCells(xlCellTypeBlanks).Select In article <uvdPvJUHCHA.1600@tkmsftngp12, Will Fleenor <will@k2e.com wrote: Is there a command that I can use to search for blank cells in a database in Excel? Thanks, Will will_nospam_@k2e.com |
| 919 | Cells can only have formulas or values entered. Each entry replaces the previous entry. So no, you can't store a formula and value in the same cell at the same time. You can, however, use VBA and events to reconstruct the formula. Say you have the formula "=A1+A2" in cell A3. Then this event macro allows the user to make an overriding entry. Put this in the worksheet code module (right-click on the worksheet tab, choose View Code, paste the code in the window that opens, then click the XL icon on the toolbar to return to XL): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(0, 0) = "A3" Then _ If IsEmpty(.Value) Then _ .Formula = "=A1 + A2" End With End Sub In article <1115801c21df3$26e7d060$36ef2ecf@tkmsftngxa12, landon <pugsly8422@hotmail.com wrote: i have put in formulas, but when you put in a number it erases the formula, so im wondering if there is a way to save the formula to the cell, so that even after you erase/delete the number you put in the cell, the formula will still be there. any help is great appreciated. thanx |
| 935 | Hi, I would like to define a new function in VBA and then use it in a spreadsheet. Could someone please tell me the procedure since all I get is a #NAME? when I reference the function I have created. Thanks. |
| 941 | Post this to the excel.programming ng and include a copy of the function you wrote. I'm guessing the function never gets assigned to what you want, but who knows..... ken -----Original Message----- Hi, I would like to define a new function in VBA and then use it in a spreadsheet. Could someone please tell me the procedure since all I get is a #NAME? when I reference the function I have created. Thanks. . |
| 949 | You can't do this with a simple formula. You'll need to write some VBA to do the job. Put the following function in a standard code module (not ThisWorkbook or one of the Sheet modules). '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function SheetNames(Optional Index As Long = 0) As Variant Dim Arr() As String Dim Ndx As Long With Application.Caller.Worksheet.Parent.Worksheets If Index = 0 Then ReDim Arr(1 To Application.Caller.Cells.Count) For Ndx = 1 To Application.Min(.Count, UBound(Arr)) Arr(Ndx) = .Item(Ndx).Name Next Ndx If Application.Caller.Rows.Count 1 Then SheetNames = Application.Transpose(Arr) Else SheetNames = Arr End If Else If (Index < 0) Or (Index .Count) Then SheetNames = CVErr(xlErrValue) Else SheetNames = .Item(Index).Name End If End If End With End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' You can call this function directly from a worksheet cell as either a simple formula or as an array formula. For example, if you enter =SheetNames(3) it will return the name of the 3rd worksheet. To return a list of all the sheets in the workbook, first select the range of cells (all in a single row or a single column), type =SheetNames(0) or =SheetNames() and press Ctrl+Shift+Enter rather than just Enter. The enters the formulas as what is called an "array formula" and the sheet names will be listed in those cells. If you array-enter the formula in to more cells than there are worksheets, the first N cells will have the sheet names, and the remaining cells will be blank. If you array-enter the formula into fewer cells that there are worksheets, then only the first N sheets will be listed. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 970 | One way to get there. Open your workbook in Excel. hit alt-F11 (this gets you to the VBE) hit ctrl-F4 (this displays the project explorer--no harm if it's already shown) You should see something that looks like VBAProject (Yourworkbookfilenamehere.xls) There's either a box with a + or - directly in front of this. If it's a +, then click on it to fan out its subordinates You should see an item named "microsoft Excel Objects" (expand this one if necessary) You should see an object for each worksheet and one named ThisWorkbook. Doubleclick on that one and you're there. (Code window should open on right hand side.) ========= Alternative way. Make the workbook less than fullscreen inside excel. (Window|Arrange|Tiled will work) Right click on that workbook's title bar and select view code. You're there. Good luck, Rob Pearce wrote: General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 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 |
| 974 | This sounds contradictory to me, you don't want the today function because it changes with the computer's date but you don't want to use ctrl+; because it doesn't change with the computer's date? Sub InsertDate() Range("A1").Value = Date End Sub will insert today's date in A1 which will be the same as doing ctrl+; -- Regards, Peo Sjoblom <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? |
| 979 | What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1009 | Something like this maybe? Private Sub Workbook_NewSheet(ByVal Sh As Object) Range("A1").Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" End Sub Jon dflannery wrote: What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1025 | Or maybe this? This will enter the date and the sheetname when a new worksheet has been created. Private Sub Workbook_NewSheet(ByVal Sh As Object) Sheet1.Activate ' a summary sheet of the PO's Range("A1").End(xlDown).Offset(1, 0).Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" ActiveCell.Offset(0, 1).Value = Sh.Name End Sub Jon Crash wrote: Something like this maybe? Private Sub Workbook_NewSheet(ByVal Sh As Object) Range("A1").Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" End Sub Jon dflannery wrote: What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1053 | Right you may be, I tried the =IF(ISERR(.... and got what I need. Thanks for the quick help. If I could just remember all these arbitrary and inconsistent VBA functions I'd be happier. Oh well, this .Net will put us all out of business in a few years anyway. Tom -----Original Message----- Here, Tom =IF(ISNA(VLOOKUP(A1,Sheet2! $A$1:$B$13,2,FALSE)),"0",VLOOKUP(A1,Sheet2!$A$1:$ B$13,2,FALSE)) HTH "TomT" <t@t.com wrote in message news:1388e01c21ec8$b77f9740$39ef2ecf@TKMSFTNGXA08... This does not seem to work the way I want =IF(VLOOKUP(A1,Sheet2!$A$1:$B$13,2,FALSE) ="#N/A",0,VLOOKUP (A1,Sheet2!$A$1:C1,2,FALSE)) If the lookup fails, I get the "#N/A" instead of the zero. If it works I get the correct value. What's going on/what's the fix? Thanks to all in advance. . |
| 1069 | Thanks to you both, works perfectly, now all I have to do is understand it. I'd better read up on Countif. Thanks! Neal. "" <DavidH@OzGrid.com wrote in message news:9uYS8.17$tW6.14652@vicpull1.telstra.net... Hi Neal No need for VBA, Conditional Formatting will do it nicely see here: /Excel/Formulas.htm And here to take this a step further: /download/default.htm Download "FormsControls.zip" -- FREE EXCEL NEWSLETTER /News/2home.htm "Neal" <nblackie@clear.net.nz wrote in message news:O5e93upHCHA.2480@tkmsftngp11... | I am making a spreadsheet for our Radio Control flying club. Each member | has some transmitters and each has a frequency assigned. If someone is | flying and another person turns on their transmitter with the same | frequency, the one in the air spirals down out of control and crashes, and | there's a lot of yelling etc. | | I have a simple table, names down the left, frequencies owned across the | top. I want to be able to enter them in (such as 40.85, and if that number | already exists, they BOTH should be highlighted in red. That way, if anyone | looks at the sheet, they can spot who they potentially clash with. | | How do I do that? I guess I am going to need some sort of Macro that | updates whenever a cells is updated? | | Regards, Neal Blackie. | | |
| 1070 | I've defined a name in a worksheet. It doesn't refer to any cell. Let's say the name is "switch". How do I use its value in VBA code? I tried: If switch=1 then ... but I got an error. |
| 1073 | Try using the Evaluate function or enclose the name in square brackets, e.g., [switch] HTH, -- Earl K. Takasaki Please reply to the group. "Joe" <joe.williams@verizon.com wrote in message news:1361401c21ee2$57101870$9be62ecf@tkmsftngxa03... I've defined a name in a worksheet. It doesn't refer to any cell. Let's say the name is "switch". How do I use its value in VBA code? I tried: If switch=1 then ... but I got an error. |
| 1076 | Joe Try this syntax Result = Mid(ActiveWorkbook.Names("switch").Value,2) ' To get rid of the equation sign If Result = 1 Then....... -- Best regards Leo Heuser MVP Excel "Joe" <joe.williams@verizon.com skrev i en meddelelse news:1361401c21ee2$57101870$9be62ecf@tkmsftngxa03... I've defined a name in a worksheet. It doesn't refer to any cell. Let's say the name is "switch". How do I use its value in VBA code? I tried: If switch=1 then ... but I got an error. |
| 1107 | Can code in one VBA module create code in another VBA module, as opposed to having a * human * type it in? We want to create a macro in one workbook which replaces the code in a module in another open workbook. |
| 1113 | Take a look at Chip Pearson's web page: /excel/vbe.htm Peter Stallone wrote: Can code in one VBA module create code in another VBA module, as opposed to having a * human * type it in? We want to create a macro in one workbook which replaces the code in a module in another open workbook. -- Dave Peterson ec35720@msn.com |
| 1128 | Andie, If you do a search on 'excel tutorial' you will find some ideas. / Or here is a list I have gathered. /Pages/Excel/homepage.html /view/cobrand/beginners/i/19 /excel/pivots.htm (on pivot tables) /dmcritchie/excel/excel.htm (go to the 'Excel Lessons & Tutorials' section) http://205.236.230.101/xl/ and some on VBA programming /trio/tut/excel/index.html 6.net/ /toc.htm /OzGrid.html /support/excel/content/vba101/default.asp /support/excel/content/Automation/automation.asp Andie <andiecorbin@hotmail.com wrote in message news:139ea01c2204b$f9fba5d0$b1e62ecf@tkmsftngxa04... After doing an unsuccessful search, do you know where I can get an Excel tutorial? Regards, AC |
| 1138 | There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1142 | Dave's routine is vulnerable to lock-up any time Amount is = the number of single digits in the range Bottom to Top and Top-Bottom is =10. Of course, it's unusual for any lotto to have more than 9 picks, so this would happen rarely in Lotto picking. However the routine below generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) numbers: Public Function Rands(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 Rands = Rands & " " & iArr(i) Next i Rands = Trim(Rands) End Function In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann <sandymann@tiscali.co.uk wrote: Dave, I am not trying to be a smart-ass here - I just trying to learn good programming techniques through these NG's - and I know that your function is just a bit of fun but it seems, to me at least, to have a fatal flaw when the number of random numbers approaches the total of numbers available. For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. *Amount*)' Excel seem to freeze. I assume that the reason is that it ends up going through an endless loop. I think what is happening is that when 12 is selected then when either 1 or 2 is selected, the InStr function will find then in the number 12 ans so will reject them. (i.e. Running the function numerous times I noticed that if 12 is selected early then neither 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the list.) It must be that the odds are that the numbers 16 through 20 have already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr function will find that number and so will continue the loop without end because it still requires another number. At least when I added a counting variable to see how many times the loop had executed I got a message box at 20,000,000 (I didn't have the patients to wait any longer.) Of course I could be wrong about the above but I would value your comments to further my knowledge of Excel & VBA Sandy "" <DavidH@OzGrid.com wrote in message news:imUS8.40$0O6.53272@vicpull1.telstra.net... Hi Wayne I have a fun little Function that you could use: Click the link below for full easy instructions if are unsure of how to use in the Worksheet. /VBA/RandomNumbers.htm Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iNum As String Dim strNum As String Dim i As Integer Application.Volatile iNum = Int((Top - Bottom + 1) * Rnd + Bottom) For i = 1 To Amount strNum = Trim(strNum & " " & iNum) Do Until InStr(1, strNum, iNum) = 0 iNum = Int((Top - Bottom + 1) * Rnd + Bottom) Loop Next i RandLotto = strNum End Function -- FREE EXCEL NEWSLETTER /News/2home.htm "Wayne Robinson" <waynerobinson@bigpond.com wrote in message news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | I am trying to use the rand function to generate several random numbers. | That's fine. But how do I make sure that no two numbers are ever the same??? | | Any help would be greatly appreciated! | | |
| 1147 | Hi J.E. Just for fun, and to add to your excellent idea. I believe that one needs different techniques based on different situations. If one needed to pick 999 out of 1,000 numbers, then doing a search on previous numbers would take a long time. A loop would take a long time as it tried to pick a number that was not already used. Loading the 1,000 numbers, and shuffling would probably be the best way. On the other hand, if you had to pick 100 numbers out of 1,000,000, then building a large array, and then sorting that array, would not be the best idea either. I like to use the following general technique. It handles the problem of a large pool to choose from. (not building a large array to shuffle). It is also pretty fast for "Lotto" size problems. Although it has a programming loop :( ,most of the "hard" looping is done by Excel as it checks previous entries. As a side note...I don't believe a collection can be used as a 1 dimensional array when finished. (example: Join / Split). That is why the last "for-loop." I just hate that last loop! <vbg ..and I have tried everything! I removed some code, and removed error checking... (16 numbers between 1 & 20) Debug.Print PickNumbers(16, 1, 20) 8,13,15,4,2,5,20,14,19,6,16,12,3,18,10,1 Function PickNumbers(N As Long, Low As Long, High As Long) As String '// By: Dana DeLouis Dim grp As New Collection Dim v As Variant Dim j As Long Dim s As String On Error Resume Next Do While grp.Count < N s = CStr(Int(Rnd * (High - Low + 1)) + Low) grp.Add s, s Loop '// Basically done. Do what you want here... ReDim v(1 To grp.Count) For j = 1 To grp.Count: v(j) = grp(j): Next PickNumbers = Join(v, ",") End Function -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:300620021308012025%jemcgimpsey@mvps.org... Dave's routine is vulnerable to lock-up any time Amount is = the number of single digits in the range Bottom to Top and Top-Bottom is =10. Of course, it's unusual for any lotto to have more than 9 picks, so this would happen rarely in Lotto picking. However the routine below generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) numbers: Public Function Rands(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 Rands = Rands & " " & iArr(i) Next i Rands = Trim(Rands) End Function In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann <sandymann@tiscali.co.uk wrote: Dave, I am not trying to be a smart-ass here - I just trying to learn good programming techniques through these NG's - and I know that your function is just a bit of fun but it seems, to me at least, to have a fatal flaw when the number of random numbers approaches the total of numbers available. For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. *Amount*)' Excel seem to freeze. I assume that the reason is that it ends up going through an endless loop. I think what is happening is that when 12 is selected then when either 1 or 2 is selected, the InStr function will find then in the number 12 ans so will reject them. (i.e. Running the function numerous times I noticed that if 12 is selected early then neither 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the list.) It must be that the odds are that the numbers 16 through 20 have already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr function will find that number and so will continue the loop without end because it still requires another number. At least when I added a counting variable to see how many times the loop had executed I got a message box at 20,000,000 (I didn't have the patients to wait any longer.) Of course I could be wrong about the above but I would value your comments to further my knowledge of Excel & VBA Sandy "" <DavidH@OzGrid.com wrote in message news:imUS8.40$0O6.53272@vicpull1.telstra.net... Hi Wayne I have a fun little Function that you could use: Click the link below for full easy instructions if are unsure of how to use in the Worksheet. /VBA/RandomNumbers.htm Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) Dim iNum As String Dim strNum As String Dim i As Integer Application.Volatile iNum = Int((Top - Bottom + 1) * Rnd + Bottom) For i = 1 To Amount strNum = Trim(strNum & " " & iNum) Do Until InStr(1, strNum, iNum) = 0 iNum = Int((Top - Bottom + 1) * Rnd + Bottom) Loop Next i RandLotto = strNum End Function -- FREE EXCEL NEWSLETTER /News/2home.htm "Wayne Robinson" <waynerobinson@bigpond.com wrote in message news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | I am trying to use the rand function to generate several random numbers. | That's fine. But how do I make sure that no two numbers are ever the same??? | | Any help would be greatly appreciated! | | |
| 1154 | Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1165 | Make it a single line if statement. If ActiveCell.Offset(69, 7).Value < 0 Then _ ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then _ ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End Sub Bob Vance <rjvance@ihug.co.nz wrote in message news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1176 | Hi Sandy I am glad you bought this up as I wrote the code about a year ago and it was for a user wanting non-repeating random numbers between 1 and 10. I hade forgotten about this :o) I will revisit this and see if I can loosen it up somewhat, starting with Jims suggestion. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Sandy Mann" <sandymann@tiscali.co.uk wrote in message news:uwNIHS7HCHA.2012@tkmsftngp13... | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1177 | Hi Jim You are right, the original Function was written to work on numbers between 1 and 10 and I hade forgotten that. I have updated this code on my site with a thank you to you for modifying it: /VBA/RandomNumbers.htm -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:300620021308012025%jemcgimpsey@mvps.org... | Dave's routine is vulnerable to lock-up any time Amount is = the | number of single digits in the range Bottom to Top and Top-Bottom is | =10. Of course, it's unusual for any lotto to have more than 9 picks, | so this would happen rarely in Lotto picking. However the routine below | generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) | numbers: | | Public Function Rands(Bottom As Integer, Top As Integer, _ | Amount As Integer) As String | Dim iArr As Variant | Dim i As Integer | Dim r As Integer | Dim temp As Integer | | Application.Volatile | | ReDim iArr(Bottom To Top) | For i = Bottom To Top | iArr(i) = i | Next i | For i = Top To Bottom + 1 Step -1 | r = Int(Rnd() * (i - Bottom + 1)) + Bottom | temp = iArr(r) | iArr(r) = iArr(i) | iArr(i) = temp | Next i | For i = Bottom To Bottom + Amount - 1 | Rands = Rands & " " & iArr(i) | Next i | Rands = Trim(Rands) | End Function | | | | In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann | <sandymann@tiscali.co.uk wrote: | | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1180 | Did you not get my macro to work Bob Works great on my machine. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1181 | try these urls /trio/tut/excel/index.html /downsamp.html -world.net/beginning/vbtutorial6/ /vbatutor.htm /compute/visualbasic/msubtutorialbeg "Andie" <andiecorbin@hotmail.com wrote in message news:139ea01c2204b$f9fba5d0$b1e62ecf@tkmsftngxa04... After doing an unsuccessful search, do you know where I can get an Excel tutorial? Regards, AC |
| 1183 | Hi Andie Just a note about the URL /OzGrid.html that is a very old one, the new address is -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Barbara wiseman" <b@nbpwiseman.fsnet.co.uk wrote in message news:OhVbDBFICHA.2312@tkmsftngp12... | Andie, | If you do a search on 'excel tutorial' you will find some ideas. | / | | Or here is a list I have gathered. | /Pages/Excel/homepage.html | /view/cobrand/beginners/i/19 | /excel/pivots.htm | (on pivot tables) | /dmcritchie/excel/excel.htm | (go to the 'Excel Lessons & Tutorials' section) | http://205.236.230.101/xl/ | | and some on VBA programming | /trio/tut/excel/index.html | 6.net/ | /toc.htm | /OzGrid.html | /support/excel/content/vba101/default.asp | /support/excel/content/Automation/automation.asp | | | Andie <andiecorbin@hotmail.com wrote in message | news:139ea01c2204b$f9fba5d0$b1e62ecf@tkmsftngxa04... | After doing an unsuccessful search, do you know where I | can get an Excel tutorial? | | Regards, | AC | | |
| 1186 | Hi, Easy Peasy question. Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" I wrote the following code. Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = Worksheets(2).ActiveCell.Offset(c, 0) Which when dedugged is ok but when runs states "Object doesn't support this property or method" C= "some vertical number" What would the correct syntax be to copy a value from one cell in a workbook sheet to another cell in a workbook sheet. William |
| 1188 | "William" <wapfu@xtra.co.nz wrote in message news:11d8001c220b3$49b8d540$36ef2ecf@tkmsftngxa12... | Hi, | Easy Peasy question. | Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" | I wrote the following code. | Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = | Worksheets(2).ActiveCell.Offset(c, 0) | | Which when dedugged is ok but when runs states "Object | doesn't support this property or method" | C= "some vertical number" | | What would the correct syntax be to copy a value from one | cell in a workbook sheet to another cell in a workbook | sheet. | | William |
| 1189 | Sorry I didn't get it to work it went back to g column when I was at ce column Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:OeVcc6KICHA.1060@tkmsftngp11... Did you not get my macro to work Bob Works great on my machine. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1190 | Hi William Try: Worksheets(7).Select ActiveCell.Copy Destination:= _ Worksheets(2).Range(ActiveCell.Address) You cannot specify ActiveCell after referencing a Worksheet. OR better yet, use the sheets CodeName (name not in brackets in the Project Explorer). So the code might look something like: Sheet7.Select ActiveCell.Copy Destination:= _ Sheet2.Range(ActiveCell.Address) The CodeName of a Sheet is not altered by changing its position or renaming it. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:11d8001c220b3$49b8d540$36ef2ecf@tkmsftngxa12... | Hi, | Easy Peasy question. | Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" | I wrote the following code. | Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = | Worksheets(2).ActiveCell.Offset(c, 0) | | Which when dedugged is ok but when runs states "Object | doesn't support this property or method" | C= "some vertical number" | | What would the correct syntax be to copy a value from one | cell in a workbook sheet to another cell in a workbook | sheet. | | William |
| 1191 | Mr Ogilvy, That worked Brilliantly Thanks for the Help Regards Bob Vance "Tom Ogilvy" <twogilvy@msn.com wrote in message news:u5mvE3IICHA.1772@tkmsftngp09... Make it a single line if statement. If ActiveCell.Offset(69, 7).Value < 0 Then _ ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then _ ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End Sub Regards, Tom Ogilvy Bob Vance <rjvance@ihug.co.nz wrote in message news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1192 | Hi We are receiving the following error message when certain users try to use excel 2002 "An error occurred initializing the VBA libraries (265)" All the computers are running Windows 2000 with Office XP professional connected to a Win2000 SBS. We first noticed the problem on 2 computers which were upgraded to Win2000 from Win98. On those computers the only user who doesn't get the message is the Administrator. The only other 2 computers experiencing the problem have always been Win2000. On those computers only one particular user get the problem on each machine. I have noticed that on the machines that have always been Win2000 each time a new user logs on office is installed however in the upgraded machines each user opens up office straight away without running through the initial setup. We recently upgraded our practice management software which may have caused the problem however the software was removed from 2 upgraded machines then the operating system was reinstalled. We have tried uninstalling office then reinstalling the operating system before reinstalling office. We also tried repairing the office installation Any assistance would be appreciated Thanks Ben |
| 1202 | Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1212 | Hi Ben, Try running Excel in Safe Mode and see if the error occurs. For this go to Start/Run and run the following command: Excel.exe /s Note that there is a space before the forward slash. If everything works fine in Safe Mode, then the problem could be due to an add-in Go to Tools/Add-ins and uncheck all the add-ins. Then start Excel without using the /s switch. If this solves the problem then enable the add-ins one-by-one, restarting Excel each time, to find the offending add-in. If the problem still exists then go to Tools/Options/General and make sure that the 'Alternate Startup file location' is blank. If this doesn't work, move all the files out of C:\...\Office\XLStart. This could also be caused by a bad xlb file. Search for files "*.xlb" and move them to another folder.. ~~~~~~~~~~~~~~~ Beth Melton Microsoft Office MVP Please post replies/further questions to the newsgroup so that all may benefit. Personal requests for assistance can not be acknowledged. "Ben Owen" <thebigo@nospam.iinet.net.au wrote in message news:3d1fdfdc$0$23244$5a62ac22@freenews.iinet.net.au... Hi We are receiving the following error message when certain users try to use excel 2002 "An error occurred initializing the VBA libraries (265)" All the computers are running Windows 2000 with Office XP professional connected to a Win2000 SBS. We first noticed the problem on 2 computers which were upgraded to Win2000 from Win98. On those computers the only user who doesn't get the message is the Administrator. The only other 2 computers experiencing the problem have always been Win2000. On those computers only one particular user get the problem on each machine. I have noticed that on the machines that have always been Win2000 each time a new user logs on office is installed however in the upgraded machines each user opens up office straight away without running through the initial setup. We recently upgraded our practice management software which may have caused the problem however the software was removed from 2 upgraded machines then the operating system was reinstalled. We have tried uninstalling office then reinstalling the operating system before reinstalling office. We also tried repairing the office installation Any assistance would be appreciated Thanks Ben |
| 1221 | To use a sheet-level name, you include the sheet name as part if the name and part of the reference: Name is 'Air Quality Detail'!LastRecord and the reference is 'Air Quality Detail'!$A$2:$C$10 Be careful with relative references. They are relative to the cell that's active at the time you write the formula. If the cell won't change, use absolute, as I did above. You perhaps could use a macro to update and or create the names. On Mon, 1 Jul 2002 09:17:27 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1244 | Hi Annette Just change the TextBox CellLink property to any cell then spell check the cell. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:umTexsUICHA.2052@tkmsftngp08... | Annette | | This is not possible, but you could almost certainly drop it into a sheet | with VBA code, spell check that and then advise user if incorrect | | -- | HTH | Nick Hodge | Southampton, England | Microsoft MVP - Excel | nick_hodge@btinternet.com | | Excel XP\WinXP | "Annette Balboa" <abalboa@whopper.com wrote in message | news:124d801c22144$85e1b690$a4e62ecf@tkmsftngxa06... | I want to run spell check to text that is inside of a | textbox, is there any way to do this?? | I have a spreadsheet setup with instructions in a cell and | then I provide a textbox to be completed with the answer. | Several user have asked how they could run a spell check | to what they have written. I have tried and have not been | able to do so. | | |
| 1248 | Interesting to know - Mac VBA is still in the version 5's, so doesn't have a native Join (I wrote one myself for compatibility, but it's understandably slower than the built-in function and the loop). Wonder why ReDim Preserve is slower - I'd think it would just be a matter of changing a pointer or two. In article <eYR5WSWICHA.1124@tkmsftngp10, Dana DeLouis <ng_only@hotmail.com wrote: Just a little side note. I thought I could improve your speed slightly by eliminating the last loop. For i = Bottom To Bottom + Amount - 1 Rands = Rands & " " & iArr(i) Next i Rands = Trim(Rands) by using something like this... ReDim Preserve iArr(Bottom To Bottom + Amount - 1) Rands = Join(iArr, Space(1)) However, this is actually slower. The timing of the ReDim statement is slower than I initially thought. :0 There are so many interesting timing questions. Another one would be the Filter function, instead of ReDim. The number of loops is less, since you only want to mark 43-49 as being deleted.(for the 42 / 49 problem.). Unfortunately, this too is slower. :( For i = Bottom + Amount To Top iArr(i) = Chr(255) Next iArr = Filter(iArr, Chr(255), False) Rands = Join(iArr, Space(1)) -- Dana <snip |
| 1249 | Hi, Can't say I'm not thinking. When I get a match I try to take that value and vlookup it up in the correct sheet. I then take the value one column accross and store it. I then look up the corresponding value in the other sheet locate the correct offset cell and store it. I then take the value from the first sheet and post it to the second, and carry on the routine. But Alas and alac it doesn't happen that way. where have I gone wrong. I get Object required. I'm not a VBA person. Sub test() 'table in worksheet(7) is vertical 'table in worksheet(2) is horizontal Dim rng1 As Range, rng2 As Range Dim cell As Range Dim res As Variant Dim str As Variant Dim str1 As Variant Dim str2 As Variant Dim str3 As Variant Rem Set rActCell = ActiveCell Sheet2.Visible = True Set rng1 = Sheet2.Range("D3:AD3") Sheet8.Visible = True Set rng2 = Sheet8.Range("A1:A16") For Each cell In rng1 res = "" res = Application.Match(cell.Value, rng2, 0) If Not IsError(res) Then ' they match, do something Select Case Sheet8.Cells(2, 3) ' Gets the month value to set the offset "c" 'vertic ally from the active cell in 'worksh eet(2). Case 1 To 3 c = Sheet8.Cells(2, 3) + 10 Case 4 To 12 c = Sheet8.Cells(2, 3) - 2 Case Else 'what ever End Select MsgBox cell.Value & " equals " & rng2(res).Value, vbOKOnly cell.Value = z 'set object from Match function rng2(res).Value = y 'set object from MsgBox function x = "" ' store the value t = "" ' store the value str = Application.VLookup(y, rng2, x) 'Lookup the value in sht8 str1 = str.Columnoffset1 'Find the numeric value to the right str2 = Application.HLookup(z, rng1, t) 'lookup the value in sht2 str3 = str2.Rowoffset(c) 'make the cell active str1.Copy Destination:=str3 'copy the value from sht8 to sht2 Rem Sheet8.Range(rActCell.Address).Offset(0, 1).Copy Destination:=Sheet2.Range(rActCell.Address).Offset (c, 0) Else ' they don't match End If ' continue the search Next End Sub |
| 1258 | Hi Ted You can use a sheets Index number or CodeName instead of it's Tab name. In regards to the Relative Recording the macro will be relative to you Activecell or selection when you start recording, so you can start with Absolute, select a specific cell then switch to Relative. Quite frankly though Ted, if you are having problems with recording macros, automating Pivot Tables might become a nightmare for you. They are really jumping in the VBA deep end. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ted Chee" <tchee@jamcracker.com wrote in message news:1202a01c2216f$7342f690$a5e62ecf@tkmsftngxa07... | I want to record a macro to make pivot tables from .csv | files, then send this macro to people who have probably | never seen a pivot table so they will have an excellent | way to analyze data without knowing much about Excel. I'm | just using the "Record new macro" and "Stop recording" | feature. There are 2 problems I'm having. | | 1. The macro only works on spreadsheets with the same name. | | 2. The macro uses an absolute range for the pivot table. | I tried clicking on the Relative Reference icon when | making the macro, but the macro still uses absolute values. |
| 1269 | This isn't a sql server example, but the ideas is the same and so might be of use. This gets data to and from Excel and Access using SQL: /examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: /examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * creating a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. You can also download the demonstration file called "excelsql.xls". Regards Andy Wiggins FCCA www.BygSoftware.com Home of "Byg Tools for VBA" and "The Excel Auditor" "savana" <savanaland2000@yahoo.com wrote in message news:<13e6c01c220b2$2c1b0130$3aef2ecf@TKMSFTNGXA09... Could someone give me an insight as to how to implement moving data from excel to odbc sql server dbase and to move data from sql server dbase to excel? thanks |
| 1290 | Hi Steven You can do this without GroupBoxes but you would need to use the OptionButton from the Control ToolBox (ActiveX Controls) these have a GroupName Property that you can set. However, they would add overhead (If lots of them)and require some VBA knowledge. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Steven McDonald" <a@a.com wrote in message news:1223501c221b5$5da46fd0$37ef2ecf@TKMSFTNGXA13... | Thanks Dave, | | This is what I was doing, but your having confirmed that I | was doing it correctly, I went back and checked and it | seems that the problem was that my group boxes were too | small, and therefore not quite containing the option | buttons. It would be useful if you could simply group the | buttons, rather than having to use the box, but clearly | this isn't possible. | | Steve | | -----Original Message----- | Hi Steve | | Place then in a GroupBox, then just link one in the group | to any cell and | the others in the same GroupBox will automatically link | to the same cell. | Each group of OptionButtons will then work independently | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Steven McDonald" <a@a.com wrote in message | news:1271b01c221ac$8716dff0$a4e62ecf@tkmsftngxa06... | | I'm using sets of option buttons from the forms menu to | | control the value in cells. Generally, I have three | | grouped buttons to set the corresponding cell value to | 1, | | 2 or 3 (simple enough). My problem is that some of the | | sets of buttons seem to get "stuck together", i.e., I | get | | values from 1-6 in one cell instead of 1-3 in two | separate | | cells. | | | | Does anyone know who to "separate" groups of buttons | like | | this? I have tried changing the linked cell, but it | seems | | to change the link for both of the "stuck" groups, not | | just the selected one. It probably doesn't help that I'm | | creating new groups of buttons by copying/pasting old | | ones, but I have a lot of groups to create and it would | be | | very laborious otherwise. | | | | Thanks in advance, | | | | Steve | | | . | |
| 1303 | The only ways that I know how to duplicate this is to either select the range (columns A:D) first, then do your data enter or to use a little VBA. Right click on the worksheet tab and select view code. Paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("a:d")) Is Nothing Then 'don't do anything Else If Target.Column = 4 Then Target.Offset(1, -3).Select Else Target.Offset(0, 1).Select End If End If End Sub Mike Lavallee wrote: Which feature allows you to do this? (using the tab or arrow keys) The block of cells was NOT selected in advance. A1 A2 A3 A4 (auto-return) B1 B2 B3 B4 (auto-return) Etc. It behaved undoubtedly as it was supposed to. It went to the predetermined column then dropped down a row for the next set of data. Was the data originally entered through a data form or some such??? I'd like to be able to use the auto carriage return feature, if it is a feature and not a quirk. This worked in a worksheet which was protected, and didn't work when the sheet was unprotected. Regards, Puzzled -- Dave Peterson ec35720@msn.com |
| 1332 | I would be grateful if someone could tell me the VBA command to open/display a MS Word document while in Excel. I could create a hyperlink to the Word file and then write a command to press ENTER on the hyperlink, but there must be a cleverer way of doing it. Any help would be appreciated. Thanks, AP |
| 1333 | AP The following code uses early binding, so requires a reference to be set up in advance to the Word x Object Library (In the VBE, ToolsReferences) Sub openWordDoc() Dim wdApp As Word.Application Dim wdDoc As Word.Document Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") wdApp.Visible = True End Sub -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "AP" <APhang@whispertech.co.nz wrote in message news:0MwU8.512$JN4.94385@news02.tsnz.net... I would be grateful if someone could tell me the VBA command to open/display a MS Word document while in Excel. I could create a hyperlink to the Word file and then write a command to press ENTER on the hyperlink, but there must be a cleverer way of doing it. Any help would be appreciated. Thanks, AP |
| 1399 | Glad you liked it. In the past, I used the upper region of the array to save the random values. This time, I switched to the lower portion simply so that I could use REDIM to truncate the array and then assign it to the function. The time it took to concatenate the output in your code was a surprise as was the time it took to access all the elements of the collection (in Dana's code). I wonder how XL/VBA implements a collection. A linked list, perhaps? -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <020720021245349034%jemcgimpsey@mvps.org, J.E. McGimpsey <jemcgimpsey@mvps.org wrote Thanks, Tushar! In article <MPG.178ba7a1b7d9b10898a684@msnews.microsoft.com, Tushar Mehta <ng_poster@bigfoot.com wrote: There are two reasons your method is slower, J.E. First, it 'generates' too many random numbers. Second, is the output concatenation loop. To optimize the code for 'minimal' generation, replace the main For loop with For i = Top To Top - Amount + 1 Step -1 and the output loop with For i = Top To Top - Amount + 1 Step -1 I had rejected this approach for some reason that escapes me now. Thanks to you, it is glaringly obvious that it is preferred. <slap to forehead! Also, it is not clear why the output is provided as a string. It would seem that an array would be more useful to the caller. I thought it was the OP's specification, but looking back I see it was introduced by D. Hawley. Curious, though OP never said how he wanted results returned. In fact, for larger sample sizes (100,000 out of 1,000,000), both Dana's code and your code take so long in the output phase that I have never let either run to completion! :( I did 100 out of 1,000,000. Since I randomized the whole array, I don't expect it would take much longer to do 100,000 (that is, if there weren't a 32K limit on string size). I did brew a pot of coffee and read a section of the paper, however. Your function is now firmly ensconced in my library. |
| 1402 | Hi all Is there a add-inn that you can use to fax a sheet away. And the number is hardcoded on the sheet or in VBA. I can change the printer to fax with Application.ActivePrinter = "hp officejet k series fax on Ne00:" But I still have to say wich number and to how. Regards Ron |
| 1453 | Thanks, Nick. I'm just a beginner and a lot of terms are strange to me. Hope you'll be able to shed some light on this one. The GetObject help file gives the following example: ----------------------------- Dim CADObject As Object Set CADObject = GetObject("C:\CAD\SCHEMA.CAD") When this code is executed, the application associated with the specified pathname is started and the object in the specified file is activated. ----------------------------- I've adapted the example to display a word file, as follows: Dim word_document As Object Set word_document = GetObject("C:\test.doc") Although no errors occur, it doesn't quite bring up the word document. Basically nothing at all happens! Would appreciate any help. Cheers, AP "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:uBaTxzlICHA.2408@tkmsftngp13... AP The following code uses early binding, so requires a reference to be set up in advance to the Word x Object Library (In the VBE, ToolsReferences) Sub openWordDoc() Dim wdApp As Word.Application Dim wdDoc As Word.Document Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") wdApp.Visible = True End Sub -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "AP" <APhang@whispertech.co.nz wrote in message news:0MwU8.512$JN4.94385@news02.tsnz.net... I would be grateful if someone could tell me the VBA command to open/display a MS Word document while in Excel. I could create a hyperlink to the Word file and then write a command to press ENTER on the hyperlink, but there must be a cleverer way of doing it. Any help would be appreciated. Thanks, AP |
| 1510 | Hi Tony You have misunderstood me. I meant for you to use the same type of Dynamic Range in the List Source of the Validation. The example does use Comboboxes, but as no VBA is needed (al via single dynamic range) it can very easily be used in many situations. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message news:OuOXQa6ICHA.2280@tkmsftngp12... | Hi Dave, | | Thanks for taking the time to get back to me. Unfortunately on my | spreadsheet of Project Tasks I will be allowing the users to add more rows | as required and therefore I would need to dynamically add combo boxes/delete | combo boxes as rows are added and deleted. I have done this before and it is | all a bit messy and sometimes things go wrong. As this spreadsheet will be | distributed to hundreds of remote users I would rather go for the more | simple solution of Validation lists if I can get them to work. | | I appreciate you taking the time to reply and I will have a good look at the | solutions you pointed me to just in case. | | Regards | Tony | | "" <DavidH@OzGrid.com wrote in message | news:BWYU8.8$lY.21766@vicpull1.telstra.net... | Hi Tony | | | I have a working example here that should help: | /download/default.htm It's under "ChangingCombo2.zip" | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message | news:ugoshexICHA.2464@tkmsftngp12... | | Hi Folks, | | | | I need some help with implementing data validation lists. | | I want to have a validation list for column B that is based upon the | value | | the user selects in column A and a validation list in Column C that | depends | | on the choices made in columns A and B. | | | | | | On a sheet named Projects users enter the details of people working on | | Projects | | The sheet looks like this | | | | Department Project Person | | Accounts Payroll System John Smith | | Accounts Payroll System Helen Brown | | Accounts Debt Recovery Allan Welling | | Despatch New Loading Bay Mike Lang | | Despatch New Loading Bay Sharon Stone | | HR Leave System John Smith | | HR Leave System Mike Lang | | HR Training Jodi Rich | | | | (It is possible that two departments may have a project of the same | name) | | | | | | The values entered in the Department Column are from a predefined list. | | The values entered in the Project and Person columns are user defined. | (i.e. | | absolutely anything) | | | | | | My "Project Tasks" Sheet looks like this | | | | Department Project Person | | Task | | Accounts Payroll System John Smith | | Scoping | | Accounts Payroll System John Smith | | Planning | | Accounts Payroll System John Smith | | Purchase | | Accounts Payroll System Helen Brown | | Installation | | | | | | | | I want the values entered in the Department, Project and Person columns | to | | be picked from a validation list. | | If the user selects Accounts in the Department column, the validation | list | | for Project Column should consist of only projects entered on the | "Projects" | | sheet against the Accounts department. | | Likewise the validation list for the Person column should only consist | of | | people listed in the "Projects" sheet for the nominated | department/project. | | | | | | How do I do this? | | (Just stressing again I do not know in advance the values the users will | be | | entering as Projects and Persons on the "Projects" sheet.) | | | | | | TIA | | Tony | | | | | | | | |
| 1534 | Thanks, Scott. Though I am familiar with macro / VBA, I will definitly give it a try. Vittal -- ============================================ Tata Ryerson Limited, Tata Centre, 43 Chowringhee Road, Kolkata - 700 071 India Phone: 288-7087 Fax: (33) 288-1247, 2713 "Scott Collier" <scott.jo@bigpond.com wrote in message news:VHUU8.27349$Hj3.84774@newsfeeds.bigpond.com... There may be a way simpler method, but one way would be to use a macro. Private Sub Workbook_BeforePrint(Cancel As Boolean) myDate = Format(Date, "dd-mmm-yy") For Each Sheeet In ThisWorkbook.Sheets Sheeet.PageSetup.LeftFooter = myDate Next Sheeet End Sub This will print today's date in the left footer. Please Note : The left footer of every sheet in your workbook is modified. I'm sorry, I don't know of a simpler method, but that doesn't mean there isn't one. Scott "Vittal" <ryerson@giascl01.vsnl.net.in wrote in message news:uWPDwaxICHA.1168@tkmsftngp13... Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal |
| 1572 | UDF's are user defined functions. Excel supports built in functions (=sum(), =average(), =left()), and it also supports procedures/macros that can do a lot of repetitive stuff. (deleting rows that have column A = 17.5). These types of procedures are called macros. But it also supports user written functions. (That's what this is.) Hit alt-f11 to get to the VBE (visual basic editor/environment). Hit ctrl-R to see the Project explorer (usually along the left hand side) locate your project. It'll usually look like VBAProject (Yourfilenamehere.xls). There'll be a box with a +/- in it in front of it. This works just like windows explorer--expand and contract the things that are underneath it. Expand everything associated with it (just for curiosity's sake) You'll see: Microsoft Excel Objects sheet1 (sheet1) sheet2 (sheet2) ... sheetx (sheetx) ThisWorkbook Each one of these can contain code that act on just that thing. You want to make sure that any input that the user does in Column A of sheet1 is upper case. You can add event code that makes anything they type upper case. (It'll be almost invisible to the user!) You want to add some footers to the printed output? You can add it to the ThisWorkbook module. Chip Pearson has some nice notes at: /excel/events.htm that describe this kind of stuff in detail. ------- If you right click anywhere within that project, you'll get a popup with Insert... as an option. Click on that and then click on Module. (there are other things there, too.) Now paste that code in for the =previoussheet() stuff. Now hit alt-f11 to swap back to excel. One the second sheet (or the first if you want to see an error), type: =previoussheet("a1") I should have added to put something in A1 of the previoussheet! Pretty neat, huh? You use it just like a built-in formula, too. ---------- Why use UDFs at all. Well, if you've ever spent time in the worksheet.functions newgroup, there are a lot of questions about "how do I do this?" Some of the answers are longer than the Gettysburg address! I don't know how those people figure it out. I know that most of them are beyond me. But some could be rewritten into VBA and made understandable by me. They may be long and complex, but at least each part makes sense <vbg. Also, there are somethings that just can't be done (well, by me) using formulas. You want the color of the font in a cell? Chip Pearson has code that allows you to return a number that represents that color using a UDF. /excel/colors.htm Reprinted here without the express written consent of Chip or major league baseball: Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange.Font.ColorIndex Else CellColorIndex = InRange.Interior.ColorIndex End If End Function Go to Chip's site and read all the caveats about this if you're going to use it. ========= And to read more about macros and code in general, you might want to head over to 's site: /dmcritchie/excel/getstarted.htm Ben wrote: Hi Dave, I actually have the same question as Lennart, but unlike Lennart, I don't know what a UDF is. Is it possible for me to simply cut and paste the code that you wrote below into my workbook, and if so, where exactly do I paste it. I'm new to this, if you can't tell :) Thanks in advance for your help. -Ben -----Original Message----- You could use a UDF like this: Option Explicit Function PreviousSheet(myAddress As String) As Variant Application.Volatile PreviousSheet = Application.Caller.Parent.Previous.Range(myAddress) .Value End Function There is no error checking. You may want to add some checks to see if the previous sheet actually exists, is visible, etc. You'd use it in your worksheet like: =PreviousSheet("a1") (I didn't want to use a range (like: =PreviousSheet(A1), because I wanted to be able to use this formula in cell A1. And putting the cell in the formula resulted in a circular reference.) Lennart wrote: Is it possible to link a cell on one worksheet to a cell on the previous one, without using the name of that sheet? so if I have 'sheet1' and 'sheet2', I don't want to use something for cell A5 on 'sheet2' like: ='sheet1'!A5 what I'm looking for is something like: =Previous_Sheet!A5 so that it doesn't matter what the name of that sheet is. BTW by Previous sheet, I mean the sheet to the left of the one I'm using at that moment. can anyone tell me how this is done? thanks in advance, Lennart -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 1584 | If you are happy with VBA, this code snippet should do it for you On Error Resume Next 'stops errors if none With Columns("A").SpecialCells(xlCellTypeBlanks) If .Count < 3 Then .EntireRow.Delete On Error GoTo 0 End With HTH -- Bob Phillips ... looking out across Poole Harbour to the Purbecks "jvoortman" <jvoortman@canada.com wrote in message news:134a801c224a5$d3df0f50$a5e62ecf@tkmsftngxa07... I would like to know if there is a way to remove only blank cells in a column when there is only 1 or 2, but not to remove blanks if there are more. The attached link will show you a sample of what it looks like now. I want to move the address up right under the company name (so removing 2 cells would be the trick) and I need to remove that 1 blank cell in the middle of the company info also. I need to keep the multiple blanks between the different companies (and their data)so just doing a remove blanks wouldn't do the trick.. any ideas |
| 1612 | Hi Excel Users and Experts, Windows XP, Excel 2002 I am using Dana's macro and I get a compile error, "User-defined type not defined" and the code line "Dim CB as New DataObject" is highlighted. I checked under the VBA Tools References and cannot find the Microsoft Forms 2.0 object library which I am guessing is the problem. There are several MS ActiveX libraries, one of which is 2.0 but no luck on Forms. The macro worked fine on a previous computer and version of Excel, however cannot get past this compile error. Sub GoogleExcelHelp() '================================================== ' By: Dana DeLouis ' Looks for a words that are copied to ' the clipboard in the Google Newgroups... ' limited to the "Excel" groups. ' As always, feedback is welcomed... '================================================== ' You must have a reference set in your VBA project ' to the Microsoft Forms 2.0 object library. '================================================== Dim LookFor As String Dim CB As New DataObject ' This should be a string, and not a link as ' it is displayed in this newsgroup. On Error Resume Next Const GoogleExcel As String = _ "/groups?as_q=##&as_ugroup=*excel*&lr=lang_en&nu m=100&hl=en" CB.GetFromClipboard LookFor = CB.GetText() ' Now, clean it up With Application LookFor = Replace(LookFor, vbCrLf, Space(1)) LookFor = .Clean(LookFor) LookFor = .Trim(LookFor) End With ' As a guess, adjust to a format for Google LookFor = Replace(LookFor, Space(1), "+") ActiveWorkbook.FollowHyperlink Address:=Replace(GoogleExcel, "##", LookFor), NewWindow:=True End Sub Thanks, Howard |
| 1614 | Insert a userform in your project and it will create a reference to it. You can then delete the userform I would think. L. Howard Kittle <lhkittle@attbi.com wrote in message news:jYIV8.105515$Uu2.18431@sccrnsc03... Hi Excel Users and Experts, Windows XP, Excel 2002 I am using Dana's macro and I get a compile error, "User-defined type not defined" and the code line "Dim CB as New DataObject" is highlighted. I checked under the VBA Tools References and cannot find the Microsoft Forms 2.0 object library which I am guessing is the problem. There are several MS ActiveX libraries, one of which is 2.0 but no luck on Forms. The macro worked fine on a previous computer and version of Excel, however cannot get past this compile error. Sub GoogleExcelHelp() '================================================== ' By: Dana DeLouis ' Looks for a words that are copied to ' the clipboard in the Google Newgroups... ' limited to the "Excel" groups. ' As always, feedback is welcomed... '================================================== ' You must have a reference set in your VBA project ' to the Microsoft Forms 2.0 object library. '================================================== Dim LookFor As String Dim CB As New DataObject ' This should be a string, and not a link as ' it is displayed in this newsgroup. On Error Resume Next Const GoogleExcel As String = _ "/groups?as_q=##&as_ugroup=*excel*&lr=lang_en&nu m=100&hl=en" CB.GetFromClipboard LookFor = CB.GetText() ' Now, clean it up With Application LookFor = Replace(LookFor, vbCrLf, Space(1)) LookFor = .Clean(LookFor) LookFor = .Trim(LookFor) End With ' As a guess, adjust to a format for Google LookFor = Replace(LookFor, Space(1), "+") ActiveWorkbook.FollowHyperlink Address:=Replace(GoogleExcel, "##", LookFor), NewWindow:=True End Sub Thanks, Howard |
| 1615 | Thanks, Tom. That did the trick and it worked fine. Thanks again, Regards, Howard "L. Howard Kittle" <lhkittle@attbi.com wrote in message news:jYIV8.105515$Uu2.18431@sccrnsc03... Hi Excel Users and Experts, Windows XP, Excel 2002 I am using Dana's macro and I get a compile error, "User-defined type not defined" and the code line "Dim CB as New DataObject" is highlighted. I checked under the VBA Tools References and cannot find the Microsoft Forms 2.0 object library which I am guessing is the problem. There are several MS ActiveX libraries, one of which is 2.0 but no luck on Forms. The macro worked fine on a previous computer and version of Excel, however cannot get past this compile error. Sub GoogleExcelHelp() '================================================== ' By: Dana DeLouis ' Looks for a words that are copied to ' the clipboard in the Google Newgroups... ' limited to the "Excel" groups. ' As always, feedback is welcomed... '================================================== ' You must have a reference set in your VBA project ' to the Microsoft Forms 2.0 object library. '================================================== Dim LookFor As String Dim CB As New DataObject ' This should be a string, and not a link as ' it is displayed in this newsgroup. On Error Resume Next Const GoogleExcel As String = _ "/groups?as_q=##&as_ugroup=*excel*&lr=lang_en&nu m=100&hl=en" CB.GetFromClipboard LookFor = CB.GetText() ' Now, clean it up With Application LookFor = Replace(LookFor, vbCrLf, Space(1)) LookFor = .Clean(LookFor) LookFor = .Trim(LookFor) End With ' As a guess, adjust to a format for Google LookFor = Replace(LookFor, Space(1), "+") ActiveWorkbook.FollowHyperlink Address:=Replace(GoogleExcel, "##", LookFor), NewWindow:=True End Sub Thanks, Howard |
| 1645 | Are you planning on using VBA or are you asking if you can do it without VBA? Mark Williams <markmidi@musicscore.freeserve.co.uk wrote in message news:ag931i$8fg$1@newsg3.svr.pol.co.uk... Hi everyone, Before I go ahead and figure out how to do this I just wanted your ideas on whether its possible and a good way to go about it: What I have is an Access database containing Items for sale, descriptions, Price, etc... What I'd like to do in Excel is set up an Invoice where I can set up combo-boxes to select the item (from the database) that the customer wants to purchase. Excel will read the database and list all the Items - allow me to select the item then excel will automatically put in the price thats listed in the database against that item. I hope I have explained that well... Any suggestions ? Mark |
| 1649 | Hi Mark, Create a ComboBox in your sheet from the design-toolbar. Set the property "ColumnCount" to that number your Access-table contains data. Create the following code for the ComboBox: Private Sub ComboBox1_GotFocus() Dim db As Database Dim rs As Recordset Dim RecCount As Long Dim i As Long Set db = DAO.Workspaces(0).OpenDatabase("C:\Test.mdb") Set rs = db.OpenRecordset("SELECT YourTablename.* FROM YourTablename", dbOpenDynaset) rs.MoveNext RecCount = rs.RecordCount ComboBox1.Column() = rs.GetRows(RecCount) End Sub Private Sub ComboBox1_Change() [A1] = ComboBox1.Column(0) [A2] = ComboBox1.Column(1) [A3] = ComboBox1.Column(2) [A4] = ComboBox1.Column(3) [A5] = ComboBox1.Column(4) End Sub Suppose, the table contains 5 columns of data, the ComboBox will display as many columns as you define for "ColumnCount". After selecting (clicking) on one of the items, the Change-Code transfers the data from all columns to the cells in column A (in my example there are 5 columns in the Access-table). HTH, Matthias www.excel-center.com www.dr-rochholz.de "Tom Ogilvy" <twogilvy@msn.com schrieb im Newsbeitrag news:u5S##vcJCHA.1724@tkmsftngp08... Are you planning on using VBA or are you asking if you can do it without VBA? Regards, Tom Ogilvy Mark Williams <markmidi@musicscore.freeserve.co.uk wrote in message news:ag931i$8fg$1@newsg3.svr.pol.co.uk... Hi everyone, Before I go ahead and figure out how to do this I just wanted your ideas on whether its possible and a good way to go about it: What I have is an Access database containing Items for sale, descriptions, Price, etc... What I'd like to do in Excel is set up an Invoice where I can set up combo-boxes to select the item (from the database) that the customer wants to purchase. Excel will read the database and list all the Items - allow me to select the item then excel will automatically put in the price thats listed in the database against that item. I hope I have explained that well... Any suggestions ? Mark |
| 1673 | You can store stuff like that in the registry. If you use VBA, you can use the GetSettings and PutSettings functions to store and retrieve that type of info. Look them up in the Help for the entire syntax. HTH, -- Earl K. Takasaki Please reply to the group. "Snow" <fc@tijd.com wrote in message news:3d28ac37$0$333$ba620e4c@news.skynet.be... Hi, I'm busy designing an excel-worksheet that will be used by multiple users. Each person will have to bring in his personal data (name, tel, etc). Now my question is, how can one make that, once that person has filled in his data, those data or cells are being saved (external in a local excel-file on his computer) and called for or updated when he opens the excel- worksheet again. And if he changes his data, those should automatically be updated too. That way, the user will only have to fill in his data once (and still have the ability to adjust it in the worksheet itself). I was thinking of a macro, but I am not experienced in VB. Can anyone help? Thx in advance, Snow |
| 1676 | Nathan, Do you have a "Debug" button the on error message that comes up? If so, repeat the process, click Debug and copy the entire procedure an post in the body of your message (NOT as an attachment). If you do not have a "Debug" button, then the problems lies in a password-protected add-in that is loaded when you start Excel (or perhaps just some workbook that resides in the XLStart folder). Go to the Tools menu, Add-Ins, and see what is checked. Repeat the following until there are no add-ins checked: Uncheck one. Close and Restart Excel If Error Occurs Then Repeat Else That Add-In Is The Problem End IF If you can get to the VBA code of the offending Add-In, you (or we) can fix it. If you cannot get to the VBA code, then you must either (or both) 1) not use that add-in, 2) get the author to fix it. It isn't an "Excel" problem per se, but rather a problem in code written by someone else that Excel is having problems with. If the above methods don't work, repeat the same process but instead of "uninstalling" add-ins, move one-by-one files out of your XLStart folder (typically something like C:\Program Files\Microsoft Office\Office\XLStart ). When you move the offending file, Excel won't open it (Excel is design to open every file in XLStart when it starts up), you'll find the culprit. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Nathan Gutman" <ngutman@cshore.com wrote in message news:3d28c04e.31676567@news.cshore.com... Usin Excel97 under win95. Out of nowhere, when I invoke Excel it comes up with a blank worksheet Book1 but immediately displays a message about Runtime error "13" Type mismatch. This started showing up recently and I have no idea what is causing it. This happens before I do anything to this worksheet. I have to click on "End" to get rid of it. Where is this coming from and how do I fix that? Thanks, |
| 1691 | I've a excel file with fews worksheets & marco, how can I use VBA code to restrict causal users that they can only opening it by browsing on my PC but cannot copy and use on others' PC within the network? (can path checking works?) Thanks |
| 1693 | Dave, Thank you very much. You solution works very nicely. Regards Tony "" <DavidH@OzGrid.com wrote in message news:XH6V8.15$G11.29347@vicpull1.telstra.net... Hi Tony You have misunderstood me. I meant for you to use the same type of Dynamic Range in the List Source of the Validation. The example does use Comboboxes, but as no VBA is needed (al via single dynamic range) it can very easily be used in many situations. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message news:OuOXQa6ICHA.2280@tkmsftngp12... | Hi Dave, | | Thanks for taking the time to get back to me. Unfortunately on my | spreadsheet of Project Tasks I will be allowing the users to add more rows | as required and therefore I would need to dynamically add combo boxes/delete | combo boxes as rows are added and deleted. I have done this before and it is | all a bit messy and sometimes things go wrong. As this spreadsheet will be | distributed to hundreds of remote users I would rather go for the more | simple solution of Validation lists if I can get them to work. | | I appreciate you taking the time to reply and I will have a good look at the | solutions you pointed me to just in case. | | Regards | Tony | | "" <DavidH@OzGrid.com wrote in message | news:BWYU8.8$lY.21766@vicpull1.telstra.net... | Hi Tony | | | I have a working example here that should help: | /download/default.htm It's under "ChangingCombo2.zip" | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message | news:ugoshexICHA.2464@tkmsftngp12... | | Hi Folks, | | | | I need some help with implementing data validation lists. | | I want to have a validation list for column B that is based upon the | value | | the user selects in column A and a validation list in Column C that | depends | | on the choices made in columns A and B. | | | | | | On a sheet named Projects users enter the details of people working on | | Projects | | The sheet looks like this | | | | Department Project Person | | Accounts Payroll System John Smith | | Accounts Payroll System Helen Brown | | Accounts Debt Recovery Allan Welling | | Despatch New Loading Bay Mike Lang | | Despatch New Loading Bay Sharon Stone | | HR Leave System John Smith | | HR Leave System Mike Lang | | HR Training Jodi Rich | | | | (It is possible that two departments may have a project of the same | name) | | | | | | The values entered in the Department Column are from a predefined list. | | The values entered in the Project and Person columns are user defined. | (i.e. | | absolutely anything) | | | | | | My "Project Tasks" Sheet looks like this | | | | Department Project Person | | Task | | Accounts Payroll System John Smith | | Scoping | | Accounts Payroll System John Smith | | Planning | | Accounts Payroll System John Smith | | Purchase | | Accounts Payroll System Helen Brown | | Installation | | | | | | | | I want the values entered in the Department, Project and Person columns | to | | be picked from a validation list. | | If the user selects Accounts in the Department column, the validation | list | | for Project Column should consist of only projects entered on the | "Projects" | | sheet against the Accounts department. | | Likewise the validation list for the Person column should only consist | of | | people listed in the "Projects" sheet for the nominated | department/project. | | | | | | How do I do this? | | (Just stressing again I do not know in advance the values the users will | be | | entering as Projects and Persons on the "Projects" sheet.) | | | | | | TIA | | Tony | | | | | | | | |
| 1695 | Sure you can search for the string -------- and read a value one cell below: Sub FindHyphens() Dim strValue As String strValue = Cells.Find(What:="--------", After:=Range("A1"), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Offset(1, 0).Value End Sub ...or you can use a more sophisticated method Tom Ogilvy pointed me yesterday. With stuff found on this site, /support/excel/content/fileio/fileio.asp, you don't need to read & write the whole file; just read row by row until you find the hyphened row and then store the following row in a variable and exit the loop. Requires some VBA skills, but is very powerul if you're capable of some programming (a good moment to start :). HTH Jouni "Rob Pearce" <rob.pearce@consignia.com wrote in message news:15f3d01c226ae$5860f770$b1e62ecf@tkmsftngxa04... I have used Excel to import 700 lines of text into a sheet. The only data that I require is the row that can be found in between ---- lines, sample below -------- CPRP202 -------- The above is located in column A. Is there a way to do it? Thanks, Rob. |
| 1700 | Notice that these two: ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False must be on one row or concatenated by a space and an underscore ( _) in the end of the first line as shown below. Newsgroups wrap VBA code sometimes badly. ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", _ Link:=False, DisplayAsIcon:=False Jouni "Jouni" <asd.asd@asd.asd wrote in message news:rrlW8.8598$ws6.177185@news2.nokia.com... Assuming you've got the picture copied in the clipboard, you could use the following code (press any worksheet tab on a workbook, select View Code, paste the code there and run by pressing F5): Sub PastePicAndScaleTheCell() ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False ' Destination: cell C4 With ActiveSheet.Shapes(1) .Top = Range("1:3").Height .Left = Range("A:B").Width .Height = Range("C4").Height .Width = Range("C4").Width End With End Sub To my surprise, Excel scales the pic 1:1, meaning width/height ratio is fixed. Maybe someone else can give a workaround for this if you need to scale it exactly to the size of a cell. HTH Jouni "Mark A. Bystry" <mbystry@ziggity.com wrote in message news:OWjy4#qJCHA.2508@tkmsftngp08... is it possible to insert an image file (jpeg, bmp, gif) into a cell and have the image autoscale down to the cell size? |
| 1712 | Is it possible to execute sql code in oracle from Excel with VBA? If so, what's the code? Below is my atempt: With ActiveSheet.QueryTables.Add (Connection:= "ODBC;DSN=orcl;UID=rare;PWD=rare;DBQ=rms; DBA=W;APA=T;PFC=1;TLO=0;", _ Destination:=Range("A1")) .CommandText = "desc temprare1;" .Refresh |
| 1720 | Try modifying this code for your use Dim sqlstring, connstring, Count, i As Integer 'Update the Part Group box with the appropriate group for the selected P/N Sheets("Query2").Activate sqlstring = "SELECT DISTINCT PART_GRP.F_NAME" _ & " FROM iqs.dbo.PART_GRP PART_GRP, iqs.dbo.PART_DAT PART_DAT" _ & " WHERE PART_GRP.F_PTGP = PART_DAT.F_PTGP AND ((PART_DAT.F_NAME='" & UserForm1.ComboBox1 & "'))" connstring = "ODBC;DSN=spc at ipt-sql;UID=Greg;APP=Microsoft® Query;WSID=GREG2;DATABASE=iqs;Trusted_Connection=Yes" With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring) .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells '.RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False '.Refresh End With HTH, Greg "Calle" <calleo@hotmail.com wrote in message news:1740d01c226bc$4611bd30$35ef2ecf@TKMSFTNGXA11... Is it possible to execute sql code in oracle from Excel with VBA? If so, what's the code? Below is my atempt: With ActiveSheet.QueryTables.Add (Connection:= "ODBC;DSN=orcl;UID=rare;PWD=rare;DBQ=rms; DBA=W;APA=T;PFC=1;TLO=0;", _ Destination:=Range("A1")) .CommandText = "desc temprare1;" .Refresh |
| 1733 | I've tested the code using first VBA's object.CopyPicture method for a chart and second by opening a jpeg in Paint (Window's defauls pic viewer), pressing Alt+A & Alt+C and pasting it then with the code I've presented earlier. Both works. I don't know exactly what's wrong there with you computer. You could try the following: 1) copy any picture in the clipboard 2) turn Excel's Macro Recorder on (Tools | Macro | Record New Macro...) 3) select Edit | Paste Special... 4) put thumbs up and wish that there's something listed that resembles a picture format... If so, select it and click Ok. If not, shut down the computer and go have fun elsewhere :-) 5) stop Macro Recorder (click the little blue square) 6) go to Tools | Macro | Macros... and search for the code you just recorded. Probably Activesheet.PasteSpecial Format:=(something here)... is different from what I posted due to your computer's settings. It'd be fun to know, whether that worked out. Post back if you've got further questions; however, Europe's going to bed now and I won't be back until tomorrow morning. Perhaps there's someone else who can help you if needed. Take care, Jouni Finland "Mark A. Bystry" <mbystry@ziggity.com wrote in message news:#t8Dm#rJCHA.2688@tkmsftngp11... ahh! i must be retarded or something. i cannot get it to work. it errors out on the activesheet line. i've tried several things and still cannot get it to work. your code is exactly what i am looking for though... |
| 1742 | Greg, you are a hero thank you very much!!! Calle -----Original Message----- Try modifying this code for your use Dim sqlstring, connstring, Count, i As Integer 'Update the Part Group box with the appropriate group=20 for the selected P/N Sheets("Query2").Activate sqlstring =3D "SELECT DISTINCT PART_GRP.F_NAME" _ & " FROM iqs.dbo.PART_GRP PART_GRP,=20 iqs.dbo.PART_DAT PART_DAT" _ & " WHERE PART_GRP.F_PTGP =3D PART_DAT.F_PTGP=20 AND ((PART_DAT.F_NAME=3D'" & UserForm1.ComboBox1 & "'))" connstring =3D "ODBC;DSN=3Dspc at ipt- sql;UID=3DGreg;APP=3DMicrosoft=AE Query;WSID=3DGREG2;DATABASE=3Diqs;Trusted_Connection=3DYes" With ActiveSheet.QueryTables.Add (Connection:=3Dconnstring, Destination:=3DRange("B1"), Sql:=3Dsqlstring) .FieldNames =3D False .RowNumbers =3D False .FillAdjacentFormulas =3D False .PreserveFormatting =3D True .RefreshOnFileOpen =3D False .BackgroundQuery =3D True .RefreshStyle =3D xlOverwriteCells '.RefreshStyle =3D xlInsertDeleteCells .SavePassword =3D True .SaveData =3D True .AdjustColumnWidth =3D True .RefreshPeriod =3D 0 .PreserveColumnInfo =3D True .Refresh BackgroundQuery:=3DFalse '.Refresh End With HTH, Greg "Calle" <calleo@hotmail.com wrote in message news:1740d01c226bc$4611bd30$35ef2ecf@TKMSFTNGXA11... Is it possible to execute sql code in oracle from Excel with VBA? If so, what's the code? Below is my atempt: With ActiveSheet.QueryTables.Add (Connection:=3D "ODBC;DSN=3Dorcl;UID=3Drare;PWD=3Drare;DBQ=3Drms; DBA=3DW;APA=3DT;PFC=3D1;TLO=3D0;", _ Destination:=3DRange("A1")) .CommandText =3D "desc temprare1;" .Refresh . |
| 1784 | Here's a VBA procedure that should do the job. Sub ForEach() Dim a As Variant, b As Variant, c As Variant Dim aa As Variant, bb As Variant, cc As Variant Dim Row As Long a = Array(1, 2, 3) b = Array(10, 20) c = Array(5, 50, 500) Row = 1 For Each aa In a For Each bb In b For Each cc In c Cells(Row, 1) = aa Cells(Row, 2) = bb Cells(Row, 3) = cc Row = Row + 1 Next cc Next bb Next aa End Sub It write the data to the active worksheet, beginning in row 1. The a, b, and c arrays can have any number of values. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Bob Bobb Robertson" <MartinAJ@Copper.net wrote in message news:eC1dJItJCHA.2568@tkmsftngp09... Is there an Excel command, function, or macro that mimic's REFLEX's (Borland's numeric database product circa 1990) "For Each ... " Function. That is, given several, say four fields, the "For Each" function would generate records For each A=1,2,3 For each B=10,20 For each C=5,50,500 would result in 3x2x3=18 records as follows A B C 1 10 5 2 10 5 3 10 5 1 20 5 2 20 5 3 20 5 1 10 50 2 10 50 3 10 50 1 20 50 2 20 50 3 20 50 1 10 500 2 10 500 3 10 500 1 20 500 2 20 500 3 20 500 Then you could define a "D" field that is a function of A, B, and C -- FrankJakob@Yahoo.com |
| 1827 | no problem open the workbook and a new one rightclick on a sheetab and choose select all sheets select move or copy and choose in "to book" the new workbook. select copy!! also then OK now all sheets ar in the new workbook hit alt f11 in the VBA editor you can drag and drop the modules and userforms to the new workbook. only the code that is under thisworkbook you must copy in the thisworkbook of the new file. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1491901c22784$c6a69f10$2ae2c90a@hosting.microsoft.com... Thanks. I thought about that. But I have a lot of macros and forms and range names that could become a litte messy. -----Original Message----- the only way I heve solved it to copy the sheets and modules to a new workbook in Excel 97 and save that one. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. . |
| 1842 | Thanks, David I was thiking more in terms of doing it thru VBA. Any suggestions? Terry On Tue, 9 Jul 2002 17:21:10 -0400, "" <dmcritchie@msn.com wrote: Hi Terry, I think the ability to cut or copy and paste from one office application to another has done away with the ability to export or save as Rich Text Format. But, I think, you can copy to the clipboard and paste to WordPad Start, Run WordPad and then change the extension to .rtf if not already with .rtf Let me know if that worked for your application as you've made me curious. I noticed it did not include the interior coloring of the cells when I just tested. The only reason I ever worked with .rtf was to create an RTF file from Word to be run through a converter to HTML before anything was included in Excel. Depending on your system I think they were named RTF2HTML or RTFTOHTML. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Terry Freedman" <terry@terryfreedman.com wrote in message news:3d2b42d6.4460293@auth.news.easynet.co.uk... Hi Can someone tell me how to get Excel to export a worksheet to RTF format please? Thanks Terry == Terry Freedman Technology Tips for Writers mailto:wkh-subscribe@topica.com == Terry Freedman Technology Tips for Writers mailto:wkh-subscribe@topica.com |
| 1852 | Maybe this will help you get started with activex stuff: Sub testme() Dim oleObj As OLEObject For Each oleObj In ActiveSheet.OLEObjects If TypeOf oleObj.Object Is MSForms.Combobox Then MsgBox "Combobox: " & oleObj.Name ElseIf TypeOf oleObj.Object Is MSForms.OptionButton Then MsgBox "optionbutton: " & oleObj.Name End If Next End Sub I've picked up what I know about typeof in the streets (er, in the newsgroups). I _think_ that there was a discussion lamenting the lack of documentation in help for "typeof" and I _think_ that there is an example that's buried in another section. (I kind of remember under the "if" section of help.) Hey, I found it (xl2002): Tip Select Case may be more useful when evaluating a single expression that has several possible actions. However, the TypeOf objectname Is objecttype clause can't be used with the Select Case statement. Note TypeOf cannot be used with hard data types such as Long, Integer, and so forth other than Object. But that wasn't very useful, was it??? ========= Don't forget for the questions you know have been asked/answered before. Rob Miller wrote: Dave, Good stuff! Also, good suggestion concerning a variant in Sub2. Having a little trouble using TypeOf with ActiveX controls - still messing around with it... If you have another minute...do you know of any documentation conerning TypeOf? I cannot bring it up in my Excel VBA Help. Thanks again Rob -----Original Message----- How about this: Option Explicit Sub Sub1() Call Sub2(Worksheets(1)) Call Sub2(ThisWorkbook) Call Sub2(Range("a1:a3")) End Sub Sub Sub2(GenObj As Object) If TypeOf GenObj Is Workbook Then MsgBox "workbook" ElseIf TypeOf GenObj Is Worksheet Then MsgBox "worksheet" ElseIf TypeOf GenObj Is Range Then MsgBox "range" Else MsgBox "what the heck was that?" End If End Sub And it looks like you could make sub2: Sub Sub2(GenObj As Variant) Then you could pass it things that aren't objects and not have compile errors. (Not sure if that matters to you, though.) Call Sub2("asdf") Wouldn't produce an error. Rob Miller wrote: All, I want to pass a parameter as a generic object type, then determine what type of object it is and branch accordingly. Sub Sub1 Call Sub2 (GenericObj) End Sub Sub Sub2 (GenObj as Object) If this type of object then.... If that type of object then... If the other type of object then... else... End Sub I'm using ADO to populate a recordset. Now I want the option to output to a default spot on an Excel sheet, a specific range on a sheet, a textbox, etc... Thanks.... Rob Miller -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 1866 | Dave, Well - you nailed it. Thanks! This is just what I need... Rob -----Original Message----- Maybe this will help you get started with activex stuff: Sub testme() Dim oleObj As OLEObject For Each oleObj In ActiveSheet.OLEObjects If TypeOf oleObj.Object Is MSForms.Combobox Then MsgBox "Combobox: " & oleObj.Name ElseIf TypeOf oleObj.Object Is MSForms.OptionButton Then MsgBox "optionbutton: " & oleObj.Name End If Next End Sub I've picked up what I know about typeof in the streets (er, in the newsgroups). I _think_ that there was a discussion lamenting the lack of documentation in help for "typeof" and I _think_ that there is an example that's buried in another section. (I kind of remember under the "if" section of help.) Hey, I found it (xl2002): Tip Select Case may be more useful when evaluating a single expression that has several possible actions. However, the TypeOf objectname Is objecttype clause can't be used with the Select Case statement. Note TypeOf cannot be used with hard data types such as Long, Integer, and so forth other than Object. But that wasn't very useful, was it??? ========= Don't forget for the questions you know have been asked/answered before. Rob Miller wrote: Dave, Good stuff! Also, good suggestion concerning a variant in Sub2. Having a little trouble using TypeOf with ActiveX controls - still messing around with it... If you have another minute...do you know of any documentation conerning TypeOf? I cannot bring it up in my Excel VBA Help. Thanks again Rob -----Original Message----- How about this: Option Explicit Sub Sub1() Call Sub2(Worksheets(1)) Call Sub2(ThisWorkbook) Call Sub2(Range("a1:a3")) End Sub Sub Sub2(GenObj As Object) If TypeOf GenObj Is Workbook Then MsgBox "workbook" ElseIf TypeOf GenObj Is Worksheet Then MsgBox "worksheet" ElseIf TypeOf GenObj Is Range Then MsgBox "range" Else MsgBox "what the heck was that?" End If End Sub And it looks like you could make sub2: Sub Sub2(GenObj As Variant) Then you could pass it things that aren't objects and not have compile errors. (Not sure if that matters to you, though.) Call Sub2("asdf") Wouldn't produce an error. Rob Miller wrote: All, I want to pass a parameter as a generic object type, then determine what type of object it is and branch accordingly. Sub Sub1 Call Sub2 (GenericObj) End Sub Sub Sub2 (GenObj as Object) If this type of object then.... If that type of object then... If the other type of object then... else... End Sub I'm using ADO to populate a recordset. Now I want the option to output to a default spot on an Excel sheet, a specific range on a sheet, a textbox, etc... Thanks.... Rob Miller -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com . |
| 1869 | Thanks everyone. This is what I ended up doing. I had to tweak the VBA codes because many of the form objects (combo boxes, list boxes etc. were created with different names). Everyting is fine now. But I think this is a bug (in Excel 97). As soon as the file is re-saved to 97 format, that annoying message should completely disappear. Also, I think Excel XP should have Excel 97 as one of the formats (not the 97-2000,5.0/95 combo format) listed in the save as type. -----Original Message----- no problem open the workbook and a new one rightclick on a sheetab and choose select all sheets select move or copy and choose in "to book" the new workbook. select copy!! also then OK now all sheets ar in the new workbook hit alt f11 in the VBA editor you can drag and drop the modules and userforms to the new workbook. only the code that is under thisworkbook you must copy in the thisworkbook of the new file. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1491901c22784$c6a69f10 $2ae2c90a@hosting.microsoft.com... Thanks. I thought about that. But I have a lot of macros and forms and range names that could become a litte messy. -----Original Message----- the only way I heve solved it to copy the sheets and modules to a new workbook in Excel 97 and save that one. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. . . |
| 1892 | On Tue, 9 Jul 2002 23:48:15 -0600, "JR" <jryan@iname.com wrote: Both those will work good. Would you happen to know the syntax for the same thing with Word files? After setting a reference in the VB6 project to the Word object library as well as the Excel one... Private Sub cmd2_Click() Dim wd_App As New Word.Application wd_App.Visible = True wd_App.Documents.Open _ FileName:="H:\Word\Tests_Development\TestDoc1.doc", _ ReadOnly:=True Set wd_App = Nothing End Sub The nice thing about VB6/VBA; the syntax is remarkably consistent. 8^ "Hank Scorpio" <ApolloXVIII@hates.spam wrote in message news:3d2bc229.1237943@news.ozemail.com.au... On Tue, 9 Jul 2002 21:54:29 -0600, "JR" <jryan@iname.com wrote: Is there a command line option that I can include so that I can open any file in Read Only mode? I need to launch Excel and/or Word from a VB6 program and need to make sure the files open as Read Only even though they are not saved that way on the server. Thanks! For a command line option when starting Excel, just use /r as one of the startup switches. See the Help topic "Use startup switches when you start Microsoft Excel". Alternatively, something like this should also work (and would probably be a better option from VB6) assuming that you have your library references set up correctly (I've left out any error handling for simplicity's sake): Private Sub cmd1_Click() Dim xl_App As New Excel.Application xl_App.Visible = True xl_App.Workbooks.Open _ FileName:="H:\EXCEL\Tests_Development\TestBook1.xls", _ ReadOnly:=True Set xl_App = Nothing End Sub --------------------------------------------------------- Hank Scorpio apolloXVIII@ozemail.com.au Change XVIII to 18 for real address. --------------------------------------------------------- Hank Scorpio apolloXVIII@ozemail.com.au Change XVIII to 18 for real address. |
| 1950 | In a worksheet named "TIPS" there is a range "L10:O36" (also named as "tipsdata"). Each cell in the range is linked to other cells in the sheet and therefore displays "0" if there is no data in the linked cells. I'd like to copy each row, "L10:O10" thru "L36:O36", only if they are not displaying "0", and paste those rows that are not "0" to another range "F9:I61". I do have some VBA knowledge and I beleive I can manage to work it out if someone can write the code for me. It was to complicated for me to handle. Thank you very much for your time and help. -- I.Ugraz |
| 1951 | I don't know if this posted from Google groups... I know VBA/Vb but in Excel it's a whole different world. Can someone tell me how to do a few things? I have a function, say: Function xTEST(d as integer) as integer If d = 0 then xTEST = 1 else xTEST = 2 end if End function Now if I want d to = a cell and I want the result in a different cell how do I do it? When I enter =xTEST(B2) in a cell I get a "#NAME?" in there.?? I can't seem to figure out how to do this and it seems simple enough?? Thanks for a thump on the head. |
| 1959 | Your function worked ok for me. Do you have it in the same workbook that holds B2? Did you put the code in a general module? If it's in a different workbook, you can alter your function to: =book1.xls!xtest(B1) if your code is behind a worksheet/thisworkbook, then move it to a general module. Todd P wrote: I don't know if this posted from Google groups... I know VBA/Vb but in Excel it's a whole different world. Can someone tell me how to do a few things? I have a function, say: Function xTEST(d as integer) as integer If d = 0 then xTEST = 1 else xTEST = 2 end if End function Now if I want d to = a cell and I want the result in a different cell how do I do it? When I enter =xTEST(B2) in a cell I get a "#NAME?" in there.?? I can't seem to figure out how to do this and it seems simple enough?? Thanks for a thump on the head. -- Dave Peterson ec35720@msn.com |
| 1964 | I know VBA/Vb but in Excel it's a whole different world. Can someone tell me how to do a few things? I have a function, say: Function xTEST(d as integer) as integer If d = 0 then xTEST = 1 else xTEST = 2 end if End function Now if I want d to = a cell and I want the result in a different cell how do I do it? When I enter =xTEST(B2) in a cell I get a "#NAME?" in there…?? I can't seem to figure out how to do this and it seems simple enough?? Thanks for a thump on the head… |
| 1969 | Dirk Assuming your data is in column A. Place this formula in B1 and copy down. ="*" & A1 & "*" Using VBA it can be done quickly. Copy/Paste the following to a General Module in your workbook. Select the range to add to then run the Macro. Sub AddAsters() Dim cell As Range For Each cell In Selection cell.Value = "*" & cell.Value & "*" Next End Sub HTH Gord Dibben Excel MVP - XL97 SR2 On Wed, 10 Jul 2002 17:34:10 -0700, "Dirk Lebsack" <dlebsack@fulcrum.net wrote: I have a column with different sets of characters in each cell. I want to add a common character, an asterisk, to the beginning and end of each cells contents. Is there a quick way to do this? |
| 1970 | See one reply to your first posting. Todd Pasterski wrote: I know VBA/Vb but in Excel it's a whole different world. Can someone tell me how to do a few things? I have a function, say: Function xTEST(d as integer) as integer If d = 0 then xTEST = 1 else xTEST = 2 end if End function Now if I want d to = a cell and I want the result in a different cell how do I do it? When I enter =xTEST(B2) in a cell I get a "#NAME?" in there…?? I can't seem to figure out how to do this and it seems simple enough?? Thanks for a thump on the head… -- Dave Peterson ec35720@msn.com |
| 1972 | 1) make sure that your function is defined in the same workbook and the one you are calling it from. 2) make sure that your function is defined in a regular code module HTH, -- Earl K. Takasaki Please reply to the group. "Todd Pasterski" <pasterto@hotmail.com wrote in message news:67ed13d1.0207101423.597010f6@posting.google.com... I know VBA/Vb but in Excel it's a whole different world. Can someone tell me how to do a few things? I have a function, say: Function xTEST(d as integer) as integer If d = 0 then xTEST = 1 else xTEST = 2 end if End function Now if I want d to = a cell and I want the result in a different cell how do I do it? When I enter =xTEST(B2) in a cell I get a "#NAME?" in there.?? I can't seem to figure out how to do this and it seems simple enough?? Thanks for a thump on the head. |
| 1981 | Hi All How do I appropriately reference the solver add in in VBA. I have tried following the KB advice on this subject but could not locate the solver add-in library reference. Thanks Matt |
| 1986 | The foll. works in XL XP and should be the same in earlier versions. Look up 'Solver' (w/o the quotes) in XL VBE help. Click on any of the Solver functions. The 2nd para will tell you how to establish a reference to solver.xla. The file is in the Office\Library\Solver folder. -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <1745801c22889$d9ac1800$9be62ecf@tkmsftngxa03, Matt <mstedman@idx.com.au wrote Hi All How do I appropriately reference the solver add in in VBA. I have tried following the KB advice on this subject but could not locate the solver add-in library reference. Thanks Matt |
| 1988 | Hi Ben, You don't need to link, but add an equivalent statement on VBA sub. For link Sheet2!B5 to Sheet1!A5 do this: Private Sub Worksheet_Activate() ThisWorkbook.Sheets(2).Range("B5").Value = _ ThisWorkbook.Sheets(1).Range("A5").Value End Sub About break line, this was made by email, but VBA statement break line is done with a space followed by an underscore ( _) like above. HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Ben" <ben@rageco.com escreveu na mensagem news:147d501c22760$353ed890$2ae2c90a@hosting.microsoft.com... Hi Orlando, Thanks for your help. I tried inserting the code you wrote below, but I couldn't get it to work. First it gave me a syntax error, so I messed around with the code and put everything between "Private Sub Worksheet_Activate()" and "End Sub" on one line (I don't know VB at all, but someone told me that putting it all on one line could fix it (?). Anyways, that fixed the syntax error, but it still didn't seem to accomplish what I was trying to do. I then linked Sheet2!B5 to Sheet1!A5, and it still appeared on Sheet 2 as "=Sheet1!A5", not as the actual data from A5 on Sheet1. Any suggestions? Thanks again for your help. -Ben -----Original Message----- Hi Ben, - Open your workbook - Right click Sheet2 on tab sheet - Click on View Code command - Insert the code below: Private Sub Worksheet_Activate() ThisWorkbook.Sheets(2).Range("A1").Value = ThisWorkbook.Sheets(1).Range("A1").Value End Sub HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Ben" <ben@rageco.com escreveu na mensagem news:13c1801c226ad$2be8f400$9ee62ecf@tkmsftn gxa05... Hi All, I have a workbook in which I am trying to have data from one sheet (Sheet 1) be linked to various fields on several other sheets (Sheets 2-4). This in itself is easy enough to do, but I want to be able to edit the data directly in Sheets 2-4. As I currently have it set up, the data in a given cell in Sheet 2, which is referring to data from cell A1 in Sheet 1, will be a formula such as follows: =Sheet1!A1. How can I import the data from Sheet 1 into Sheet 2, such that it appears as editable data in Sheet 2, and not as a formula? Thanks in advance for any advice. -Ben . |
| 2046 | AP This method uses late binding, where you do not need to set a reference in advance. It is also slower. What you are doing is setting a reference to the object, which in this case is a word document, not a word application. To see what you have opened, you will need to make the application visible after you have opened the document in memory, e.g. word_document.Parent.Visible = True -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "AP" <APhang@whispertech.co.nz wrote in message news:ZkMU8.650$JN4.171550@news02.tsnz.net... Thanks, Nick. I'm just a beginner and a lot of terms are strange to me. Hope you'll be able to shed some light on this one. The GetObject help file gives the following example: ----------------------------- Dim CADObject As Object Set CADObject = GetObject("C:\CAD\SCHEMA.CAD") When this code is executed, the application associated with the specified pathname is started and the object in the specified file is activated. ----------------------------- I've adapted the example to display a word file, as follows: Dim word_document As Object Set word_document = GetObject("C:\test.doc") Although no errors occur, it doesn't quite bring up the word document. Basically nothing at all happens! Would appreciate any help. Cheers, AP "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:uBaTxzlICHA.2408@tkmsftngp13... AP The following code uses early binding, so requires a reference to be set up in advance to the Word x Object Library (In the VBE, ToolsReferences) Sub openWordDoc() Dim wdApp As Word.Application Dim wdDoc As Word.Document Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") wdApp.Visible = True End Sub -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "AP" <APhang@whispertech.co.nz wrote in message news:0MwU8.512$JN4.94385@news02.tsnz.net... I would be grateful if someone could tell me the VBA command to open/display a MS Word document while in Excel. I could create a hyperlink to the Word file and then write a command to press ENTER on the hyperlink, but there must be a cleverer way of doing it. Any help would be appreciated. Thanks, AP |
| 2055 | Joe In vba ActiveCell.Rows("1:1").EntireRow.Select in normal spreadsheet just click on the row number. Alan -----Original Message----- This one is very basic, but I can not remember how to do it... If I have a cell selected as the result of a search (causing it to be a different cell each time), and I want to select the whole row in which the cell lies, what command do I use??? Thanks in advance, Joe . |
| 2061 | My toolbar file in Excel 97 and Windows 98SE is: C:\WINDOWS\UserName8.xlb My toolbar file in Excel 2000 and Windows 98SE is: C:\WINDOWS\Application Data\Microsoft\Excel\Excel.xlb This is UN-tested: Find the Excel.xlb file on the destination machine. Rename the Excel.xlb file on the destination machine to Excel.bak (or something else that isn't used) Copy the xxxx8.xlb file from the source machine. Paste the xxxx8.xlb file in the same location as the Excel.bak Rename the xxxx8.xlb to Excel.xlb "Tom S" <Sinskit@usa.redcross.org wrote in message news:173d301c2290b$47e4c7f0$b1e62ecf@tkmsftngxa04... How do I convert an Excel 97 file with vba code and custom menus to excel 2002. The MS Knowledge Base web site says custom toolbars will not be migrated. Then to open the *8.xlb file to view the toolbars in 2002. Problem is my computer with Windows 2000 on it does not have a *8.xlb file to open. |
| 2067 | Just ActiveCell.EntireRow.Select will do it. Alan Beban Alan-SouthAust wrote: Joe In vba ActiveCell.Rows("1:1").EntireRow.Select in normal spreadsheet just click on the row number. Alan -----Original Message----- This one is very basic, but I can not remember how to do it... If I have a cell selected as the result of a search (causing it to be a different cell each time), and I want to select the whole row in which the cell lies, what command do I use??? Thanks in advance, Joe . |
| 2069 | Thanks Alan I had adapted it from a macro where I wanted the next three rows. Alan -----Original Message----- Just ActiveCell.EntireRow.Select will do it. Alan Beban Alan-SouthAust wrote: Joe In vba ActiveCell.Rows("1:1").EntireRow.Select |
| 2076 | Now that you've got this running the way you want, you should stick around more. If I stuck around everywhere I wanted to, I'd be, well, *very* sticky. And probably never sleep. <-(It's approaching that point anyway.) But I'll be baaaaack. Excel is much easier (and more fun) to work with than Word <gdr. Believe it or not, I often reach the point -- usually deep in the softest, moistest part of VBA, like in a userform, where the otherwise dark purple line between Word and Excel actually blurs. (Sometimes I'll even forget which program birthed the macro I'm working on.) It's a nice, er, escape. ;) One follow-up: Why would I have been told (early in my VBA career and I think even in this very newsgroup) to use an .XLS with the IsAddIn property changed, rather than an .XLA? Is there any easily explained advantage to the former? Thanks again, Mark Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word Userform demystification ritual begins at: /~mtangard/userforms.html "Life is nothing if you're not obsessed." --John Waters Dave Peterson wrote: Bernie did devine what I meant about auto_open macro in a general module. When you do File|SaveAs, there's an option near the bottom that allows you to specify Addin (in the "save as type" box). You'll probably have to point to your favorite directory again, though. But my point was that you didn't have to call it .xls. You could have its extension .xla (which sounds more normal to me). But it sounds like it'll do what you want. (I have a bad habbit of picking on details instead of the seeing big picture. The reason that's it's a bad habbit is sometimes I make detailed mistakes (module named auto_open for example)!) Now that you've got this running the way you want, you should stick around more. Excel is much easier (and more fun) to work with than Word <gdr. Mark Tangard <mtangard@speakeasy.net wrote in message news:<3D2CF0FC.3C2C975A@speakeasy.net... #2. ThisDocument should be ThisWorkbook Oops, that's what I meant. Sorry, Word-itis. #4. I'm not sure why you want to make it an addin (hidden??) I don't want another workbook visible. #6. If you made it an addin, why not use .xla? Well, because this is the only way I know to make XL add-ins. ;) You can also accomplish the same thing by putting your code in a general module named Auto_open. A *module* (not macro) named Auto_Open? In what workbook? Again, I don't want a macro to run *every* time I open Excel. Also, the code *isn't* associated with a specific workbook, so I can't build the desktop shortcut to open a given workbook & then run the macro. (I know how to do that, but it wouldn't help.) For example, several times a day I want Excel to open and then run an Application.FileSearch procedure to find the file in a given folder whose name is alphabetically last, then open it. I don't want this to happen each time I open Excel, just when I want that file -- and that file may change unpredictably. Am I missing something obvious? (You should spend more time in here. Things like this get mentioned pretty often <bg.) That's why I've spent so much time searching for the answer on Google. I know from roaming the Word groups that things that are mentioned often can usually be located there and that the question-answerers are usually tired of hearing the same queries over and over. -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters Mark Tangard wrote: After years of wishing for an Excel equivalent to Word's "/m" startup switch -- to have Excel automatically run a macro upon startup but only when you want it to, e.g., to make a desktop shortcut for that action, not to have the macro run whenever Excel opens -- I think I've stumbled upon the answer, and while it works fine, it seems waaaaaay too simple. Excel gurus, please tell me if something's wrong with this, or if I'm in one of those damn parallel universes again: 1. Create a new workbook 2. Place the macro you want to run in the workbook's ThisDocument module. 3. Name the macro Workbook_Open. 4. Change ThisWorkbook's IsAddIn property to True 5. Save it, but put it somewhere other than the XLStart folder. 6. Make a desktop shortcut to open the file as if it were a regular wkbk, e.g., Excel C:\path\MySneakyAddIn.xls It works. But I've never seen it mentioned. Is it just really bad form? -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters -- Dave Peterson ec35720@msn.com |
| 2092 | This file might be a help: /examples/num2wrds.xls It's in the "Accountants" section on page: /examples/examples.htm It contains two methods to convert numbers to words and two cheque writing routines. The code is open and commented. Regards Andy Wiggins FCCA www.BygSoftware.com Home of "Byg Tools for VBA" and "The Excel Auditor" "Lisa LaRue" <llarue@walkuplawoffice.com wrote in message news:<17c7d01c2292f$075f6810$3aef2ecf@TKMSFTNGXA09... Does anyone know how to convert a regular number to text for purposes of check writing? For instance, I would like $150.85 to appear as "One Hundred Fifty and 85/100 Dollars". Thanks for your help. |
| 2104 | How come when I open your Excel file the =SpellNumber() works but doesn't work in my version of Excel ? Phil. Andy Wiggins wrote: This file might be a help: /examples/num2wrds.xls It's in the "Accountants" section on page: /examples/examples.htm It contains two methods to convert numbers to words and two cheque writing routines. The code is open and commented. Regards Andy Wiggins FCCA www.BygSoftware.com Home of "Byg Tools for VBA" and "The Excel Auditor" "Lisa LaRue" <llarue@walkuplawoffice.com wrote in message news:<17c7d01c2292f$075f6810$3aef2ecf@TKMSFTNGXA09... Does anyone know how to convert a regular number to text for purposes of check writing? For instance, I would like $150.85 to appear as "One Hundred Fifty and 85/100 Dollars". Thanks for your help. |
| 2106 | Hi, You can use the "DataAdd" function available in VB. Create the following function in a module in your VBA Project for your Excel file: Public Function GetBirthDate(strInterval As String, dblAge As Double, dteDeathDate As Variant) As String If Not IsDate(dteDeathDate) Then Exit Function GetBirthDate = Format(DateAdd("d", 1, DateAdd (strInterval, dblAge - 1, dteDeathDate)), "dd mmm yyyy") GetBirthDate = GetBirthDate & " - " & Format(DateAdd (strInterval, dblAge, dteDeathDate), "dd mmm yyyy") End Function In a cell type in =GetBirthDate("yyyy", -50, A1), where A1 is the reference to the cell with the Death Date Example where A1 = 14.07.2002 (A1 can be formatted to any date format, just make sure its a date format). Return value is "15 Jul 1951 - 14 Jul 1952" To create a module press Alt+F11 to show the Visual Basic window. Press CTRL+R to show the Project Window (if not already showing) and select your VBAProject(your file name). Right click on the VBA Project and from the popup menu select Insert | Module. Paste in the function above and away you go. If you need to know more about the "DataAdd" function highlight the word and press F1, you see what additional Intervals you can use. Best regards KM -----Original Message----- Thanks for the suggestion Helene. Your formula works well and is far superior to my version with text/decimal numbers but is still too simple. The formula provided by Alan SA is perfect for the job if only it could deal with dates in the 1700-1800's as it outputs exactly what is required and doesn't allow incorrect entries such as 32nd of March. Thanks though "Helene Lovenheim" <heleneglov@mindspring.com wrote in message news:B953EE81.E63A%heleneglov@mindspring.com... | Looks like my solution doesn't work for dates before 1900 either, | but if you enter the date as text (as in your example 14 Jul 2002) | and instead use the formula | | =LEFT(A1,LEN(A1)-4)&RIGHT(A1,4)-50 | | where again the date is entered in cell A1 it looks like it works | | | "Alan-SouthAust" <aenpkenn@ihug.com.au wrote in message | | | To get first date DATE(YEAR(A1)-B1-1,MONTH(A1),DAY (A1)+1) | | | and second date | | | DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1)) | | | | | | where your date is in call a1 and number of years in b1 | | | | | | put them together like this. | | | | | | =TEXT(DATE(YEAR(A1)-B1-1,MONTH(A1),DAY(A1) +1),"dd/mm/yyyy") | | | &" to "&TEXT(DATE(YEAR(A1)-B1,MONTH(A1),DAY | | | (A1)),"dd/mm/yyyy") | | | | | | Alan | | | -----Original Message----- | | | Hi All | | | | | | Anyone know how to subtract dates in excel to detail a | | | possible date range | | | i.e. | | | Death Date - Age = Possible Birth Dates | | | 14 July 2002 - 50 years = 15 July 1951 - 14 July 1952 | | | | | | Marriage Date - Age = Possible Birth Dates | | | 11 Nov 2001 - 25 = 12 Nov 1965 - 11 Nov 1966 . |
| 2107 | Hi Rob, Yes I have. I renamed normal.dot, took care of vbe6 file and have tons of memory. Any other ideas? Unfortunately, no. You might try posting in public.office.developer.vba, in the hopes that someone there might have an idea. There are more "dev" types there, and since your problem seems broader than just Word, it's worth a try. But I get the feeling you have the same feeling I do: you may need to reformat and reinstall Windows. Something appears to have been royally mixed up. There is one other thing, if this is not Office XP (doesn't sound like it). There are sets of instructions in the KB for completely uninstalling all the bits-and-pieces of Office 97 or 2000. Doing a complete "clean-up" like this might just remove "the bad thingy", so that a reinstall would work. Cindy Meister INTER-Solutions, Switzerland /cindymeister /word /MSOfficeForum This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
| 2109 | Hi all. Can any one help me in the right way please. In VBA, I know I need to use the Filesystemobject. How can I do a little script that will do the following : In a directory called e:\logs\accounts\ For each subfolder in directory above For each file that start with 102********.csv Open the file and save it as bla.xls in e:\logs\save\ Next Next Any help will be much appreciated. Thanks hugo.b@derivs.com Hugo |
| 2225 | Hi Andonny, In ThisWorkBook: Private Sub Workbook_BeforePrint(Cancel As Boolean) You can find the above from within ThisWorkBook where you will see two dropdowns the lefthand one has (General) or Workbook choose Workbook the righthand dropdown includes BeforePrint. --for others seeking information-- More information on Event Macros in /dmcritchie/excel/event.htm More information on pathname in footers in /dmcritchie/excel/pathname.htm Excel XP users can place full pathname into the footer without a macro. (In VBA this is entered as &Z) &[Path]&[File] inserts the path and file name. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Andonny" <wje@multiline.com.au wrote in message news:#2xtS3xKCHA.388@tkmsftngp09... Hi, I would like to achieve that whenever I print an excel sheet it has the path in the footer. I know about the code placed into ThisWorkbook like Sub UpdateFooter() ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName End Sub Is it possible to have something in personal.xls or somewhere that I don't have to write the code into every workbook. Thanks for your help Andonny |
| 2234 | Hi Andonny, See these articles in the Microsoft Knowledge Base: Q153090 How To Pass a Visual Basic Array to an Excel Worksheet /default.aspx?scid=kb;en-us;Q153090 Q177991 XL: Limitations of Passing Arrays to Excel Using Automation /default.aspx?scid=kb;en-us;Q177991 Q146406 XL: How to Retrieve a Table from Access into Excel Using DAO /default.aspx?scid=kb;en-us;Q146406 Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic /default.aspx?scid=kb;en-us;Q247412 Q75376 XL: Maximum Array Size in Microsoft Excel /default.aspx?scid=kb;en-us;Q75376 Q166342 XL97: Maximum Array Size in Microsoft Excel 97 /default.aspx?scid=kb;en-us;Q166342 Q153307 HOWTO: Call Microsoft Excel Macros that Take Parameters /default.aspx?scid=kb;en-us;Q153307 Q163435 VBA: Programming Resources /default.aspx?scid=kb;en-us;Q75376 HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Andonny" <wje@multiline.com.au escreveu na mensagem news:eYJ0IAwKCHA.1988@tkmsftngp08... Hi, I am looking for a site where I could learn about arrays when used in functions and also used in visual balsic routines. Specially when setting ranges. I am aware of the pitfalls when using arrays but I still would like to learn more about them. Thanks for your help Andonny |
| 2237 | Dear All, I have a list of customers information (Name, Company, Telephone,....) in an excel file. Name Company Telephone A AA 777 B BB 444 C CC 888 I want to read these values (Using VBA) into my Access table which has the same fields. My Question: As I have to loop in my code to access these values, how can I know I have reached the end of a row in my sheet, therefore I have to add a new record in my table in the database? In Other words, how can I write a loop that reads these cells correctly? Thank you very much :) |
| 2239 | Yass I don't know exactly what you are doing but this might help you get there. A statement like: Set RangeToCopy = Range(Range("A1"), Range("IV1").End(xlToLeft).Address) will set the range from your starting cell to the last occupied cell in that row. You would put this statement into a loop procedure like this: Sub CopyAllTheRows Dim RangeToCopy As Range Dim i As Range Set RangeToCopy = Range(Range("A1"), Range("IV1").End(xlToLeft).Address) For Each i In RangeToCopy 'Your code here Next i End Sub You would have to add code to loop from row to row. This code loops just through the cells in that row. HTH. Come back if you need more help with this. Otto "Yass" <gol_e_yass@yahoo.com wrote in message news:ags566$p8b5@news-dxb.emirates.net.ae... Dear All, I have a list of customers information (Name, Company, Telephone,....) in an excel file. Name Company Telephone A AA 777 B BB 444 C CC 888 I want to read these values (Using VBA) into my Access table which has the same fields. My Question: As I have to loop in my code to access these values, how can I know I have reached the end of a row in my sheet, therefore I have to add a new record in my table in the database? In Other words, how can I write a loop that reads these cells correctly? Thank you very much :) |
| 2268 | My inventory is set in one workbook, with recipes in another. My main problem is when I use VBA to set links from my ingredients to my inventory prices the location is not updated when I sort my inventory (categorizing new inventory items, as I also use my inventory sheets for order guides as well as price quotes from my purveyors). This is only the case when I set the links using a macro. -----Original Message----- Is this all in the same workbook, or separate files? How is the inventory database set up? How is the information linked? What are you sorting, and what results do you want after you sort? forrest lyman wrote: when i sort my excel data bases with links I can not get my links to update as well. I often have to add items to my inventory and it makes my inventory dificult to read to add all new items to the end -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 2272 | What formula are you inserting with the macro? It should be identical to what you'd insert manually. forrest lyman wrote: My inventory is set in one workbook, with recipes in another. My main problem is when I use VBA to set links from my ingredients to my inventory prices the location is not updated when I sort my inventory (categorizing new inventory items, as I also use my inventory sheets for order guides as well as price quotes from my purveyors). This is only the case when I set the links using a macro. -----Original Message----- Is this all in the same workbook, or separate files? How is the inventory database set up? How is the information linked? What are you sorting, and what results do you want after you sort? forrest lyman wrote: when i sort my excel data bases with links I can not get my links to update as well. I often have to add items to my inventory and it makes my inventory dificult to read to add all new items to the end -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2328 | I use xl2002 and it crashed excel and disconnected me from my dialup at the same time! gf wrote: I can open many webpages in excel, but some, like this one: /sports/hockey/stats0102/last28s.htm crash it every time. I end up with an Access violation (5) when I look at the Dr. Watson log and I'm just curious if this occurs to anyone else. Office 2000 Windows 2000 The reason I ask is I was using a webquery to access the data into a spreadsheet. I can save the page as text, import it and parse it into columns, but I thought I'd write a little VBA routine to do all that for me. I've done this with other pages many times, so it seemed like a piece of cake. Alas, this page seems quite resistant. -- Dave Peterson ec35720@msn.com |
| 2353 | VLOOKUP will work, but you'll need to have the formula in a different column than the one into which you type your week number. If you need to have the contents of the Week number column actually change, you'll need a VBA routine. If it is not acceptable to have a separate column for the Week description, let me know. --ron On Sun, 14 Jul 2002 21:43:52 -0700, "Steve" <swrtw2001@yahoo.com wrote: Hi Everyone, Can someone direct me in the right direction, Here's what I'm looking for I have a table which looks like the following a1) Week Count ---------------- a2) 2 100 a3) 3 90 I have a table which associates numbers 1-53 with specific weeks of the year ie... d1) Number Week ------------------------------------------ d2) 1 January 1st to January 5th d3) 2 January 6th to January 12th d4) 3 January 13th to January 19th etc... all the way to 53 and the end of the year... I want to be able to write a function such as if a2=2 I want it to show "January 6th to January 12th" and if a2=3 I want it to show "January 13th to January 19th" etc... I tried searching through the archives but was unable to find someone with I similar situation. The closest I found was using the vlookup function, but i wasn't able to get it to work properly. If someone can assist me in resovling this I would greatly appreciate it. Thank you!!! --ron |
| 2366 | Please see my answer to your original posting for a VBA solution excluding dates and times. -- Best regards Leo Heuser MVP Excel "Terri Beth" <Terri01@directvinternet.com skrev i en meddelelse news:18a7d01c22c68$dbe739a0$9ae62ecf@tkmsftngxa02... How can I average a row of cells that contain numbers and text but only get the average of the cells that are number (excluding the text cells). I recently asked this question except my previous question said I had numbers, text, and dates. I was told I couldn't do this with dates included because they are actually numbers. So how can I do it with just numbers and text in a row? Help! |
| 2367 | Terri This UDF (User Defined Function) will do the job. 1. From the worksheet enter the VBA editor with <Alt<F11 2. Choose Insert Module 3. Copy and paste the below code to the righthand window. 4. Return to the sheet with <Alt<F11 and save the workbook. 5. Enter this expression (as an example) in a cell: =ave(c2:m2) Function Ave(CheckRange As Range) As Double 'Leo Heuser, 16-7-2002 'Works like the worksheet function AVERAGE() 'but excludes dates and times. Dim AverageData() Dim Cell As Range ReDim AverageData(0) For Each Cell In CheckRange.Cells If Not IsDate(Cell.Text) Then AverageData(UBound(AverageData)) = Cell.Value ReDim Preserve AverageData(UBound(AverageData) + 1) End If Next Cell Ave = Application.WorksheetFunction.Average(AverageData) End Function -- Best regards Leo Heuser MVP Excel "Terri Beth" <Terri01@directvinternet.com skrev i en meddelelse news:15f0001c22ba4$8e1866f0$9ee62ecf@tkmsftngxa05... How can I average a row of cells that contain numbers, text, and dates but only get the average of the cells that are number (excluding the text cells and date cells)? This is a tough one for me and I've struggled with it for a days. |
| 2453 | Leo, I added the code to my worksheet and it works great. Thank you very much! Terri Beth -----Original Message----- Terri This UDF (User Defined Function) will do the job. 1. From the worksheet enter the VBA editor with <Alt<F11 2. Choose Insert Module 3. Copy and paste the below code to the righthand window. 4. Return to the sheet with <Alt<F11 and save the workbook. 5. Enter this expression (as an example) in a cell: =ave(c2:m2) Function Ave(CheckRange As Range) As Double 'Leo Heuser, 16-7-2002 'Works like the worksheet function AVERAGE() 'but excludes dates and times. Dim AverageData() Dim Cell As Range ReDim AverageData(0) For Each Cell In CheckRange.Cells If Not IsDate(Cell.Text) Then AverageData(UBound(AverageData)) = Cell.Value ReDim Preserve AverageData(UBound (AverageData) + 1) End If Next Cell Ave = Application.WorksheetFunction.Average (AverageData) End Function -- Best regards Leo Heuser MVP Excel "Terri Beth" <Terri01@directvinternet.com skrev i en meddelelse news:15f0001c22ba4$8e1866f0$9ee62ecf@tkmsftngxa05... How can I average a row of cells that contain numbers, text, and dates but only get the average of the cells that are number (excluding the text cells and date cells)? This is a tough one for me and I've struggled with it for a days. . |
| 2461 | James Start here for the basics. In no particular order. /support/excel/content/vba101/default.asp /dmcritchie/excel/getstarted.htm HTH Gord Dibben Excel MVP - XL97 SR2 On 16 Jul 2002 19:01:37 -0700, james31k@excite.com (James Tigert) wrote: Does anyone know of any good places online to get a good start on VB in Excel? I know that VB is the answer to some of the needs I have in Excel, but even if someone types out the code for me, I have no idea what I am supposed to do with it, or where to put it. I do not want to waste others' time with my ignorance. If it comes to it, I will buy a book. First, though, I thought I would ask here if there are any places for help,assistance for beginners,or even tutorials online. Thanks much, James |
| 2471 | I have a workbook which contains various pivot tables, all of which are based upon the same range of data. When I created the tables, I always chose to "base it on an existing report" when prompted, rather than create a seperate report, which promised to make smaller memory and file sizes as a result. However, the file size of the workbook seems much larger than I'd expect when I save it with data + pivot tables. (1.4meg with data and 1 PT; 6.7meg with data + 5 PTs; all based on the same initial PT) I tried adding some VBA code to cycle through the PTs in the workbook and set the "SaveData" property to False, then refresh prior to save - not much help there. Any other ideas on reducing the file size, while keeping the PT layouts intact? Thanks |
| 2500 | Use a macro. The following is straight(ish) from VBA help for the FindNext method: With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.interior.colorindex=33 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Post again if you're not familiar with the VB editor. HTH Roger |
| 2512 | I got a worksheet with a pivottable embedded by using a olap cube, how can I share the file so that the pivottable can works on other PC? need any vba connection script? Thanks!!! |
| 2513 | I got a worksheet with a pivottable embedded by using a olap cube, how can I share the file so that the pivottable can works on other PC? need any vba connection script? Thanks!!! |
| 2526 | I currently have an application that I wrote in Microsoft Excel VBA and would like to port it to other machines in my business. However, it seems as though I need Excel loaded on the machine that I am porting the app to, or it will not run. Should this be the case? Is there an Excel runtime available sort of like the Access runtime - which allows the production of true standalone applications? Steve Squires steve@summitworld.com steven.squires@roche.com Thanks in advance! |
| 2531 | I think Yup was giving a worksheet solution (with =Text(value,"0.0")) The VBA equivalent of Text is Format. and you could use it like: Dim temp As Double Dim AtP As Double Dim myVal As Double 'don't know what these are: temp = 18.3 AtP = 3033.9 myVal = 0.00129 * (AtP / 101.325) * (273.15 / (273.15 + temp)) Me.TextBox4.Value = Format(myVal, "#,##0.000000") phil wrote: Hi, i tried:- TextBox4.Text(Value, "0.0") = 0.00129 * (AtP / 101.325) * (273.15 / (273.15 + temp)) but i get 'compile error - can't assign to read-only property'.? It's the value in the text box on a user form that i wantto round, rather than in cell. thanks phil -----Original Message----- I don't know exactly what the problem is, but you could use 'TEXT(value,"0.0") Which means that the actual value that is entered, is displayed with one decimal. "0.000" would give 3 decimals and so on. If it is in a cell, just simply use 'Format Cells', number, n decimals. If this is not what you're looking for, be more specific. Regards "Phil" <pperry@acsoft.co.uk wrote in message news:168fa01c22ce7$5f3c4c90$9ee62ecf@tkmsftngxa05... Hi, I'm taking one number input by user on user form, doing some calculation, then putting the result in another uneditable textbox on the userform. The numbers are very small, but all i want to do is round the result so it fits in the box! I'm sure it must be blindingly obvious, but i'm not seeing it...tried maxlength, etc, no luck. thanks phil . -- Dave Peterson ec35720@msn.com |
| 2534 | Hi Steve, No, unfortunately there isn't any kind of a run-time that would allow you to deploy Excel applications on computers that don't have Excel installed. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Steven Squires (summitworld)" <steve@summitworld.com wrote in message news:OVRgsadLCHA.1396@cpimsnntpa03... I currently have an application that I wrote in Microsoft Excel VBA and would like to port it to other machines in my business. However, it seems as though I need Excel loaded on the machine that I am porting the app to, or it will not run. Should this be the case? Is there an Excel runtime available sort of like the Access runtime - which allows the production of true standalone applications? Steve Squires steve@summitworld.com steven.squires@roche.com Thanks in advance! |
| 2541 | I don't have a suggestion for reducing the size, but I just tried this experiment in xl2002. 14 columns x 1300 rows of random numbers. 3 pivot tables with about 4-6 fields in them. When I saved the workbook with the pt's based on an existing report, I got 441K. When I did the same file but created a separate report, the file was saved at 850K. I think that pt's eat a lot of space/memory. If you're really concerned about filesize (I wouldn't be...), maybe you could build the pivot tables when you open the workbook and delete them before you close it. Seems like a lot of work for not much benefit. Personal opinion follows: With the possible exception of when excel overshoots the last used cell in a worksheet and makes the saved file larger, I pretty much accept the fact that if excel needs ??KB (or MBs), then it needs them. If you have to email it to someone, you could zip it first. If you have to put it on a floppy disk and it still won't fit, zipping (at least winzip) allows you to span floppies. Charles Sergeant wrote: I have a workbook which contains various pivot tables, all of which are based upon the same range of data. When I created the tables, I always chose to "base it on an existing report" when prompted, rather than create a seperate report, which promised to make smaller memory and file sizes as a result. However, the file size of the workbook seems much larger than I'd expect when I save it with data + pivot tables. (1.4meg with data and 1 PT; 6.7meg with data + 5 PTs; all based on the same initial PT) I tried adding some VBA code to cycle through the PTs in the workbook and set the "SaveData" property to False, then refresh prior to save - not much help there. Any other ideas on reducing the file size, while keeping the PT layouts intact? Thanks -- Dave Peterson ec35720@msn.com |
| 2560 | Hi Charles, Hi Dave The most significant way of reducing the file size I have come across is to enter the following code into the THisWorkbook module. (To perform the task manually, select any cell on the pivot table, right click, select "Table Options" and in the bottom part of the dialog box, remove the check mark from "Save data with table layout". If all pivot tables are genuinely linked (and that does not necessarily mean they have been all been created from the same source data) then you do not need to loop through all pivot tables - run the code on one pivot table and they will all be updated. Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Accounts").Activate Dim pt As PivotTable Dim ws As Worksheet For Each ws In Worksheets With ws If .PivotTables.Count 0 Then For Each pt In .PivotTables pt.PivotCache.Refresh pt.SaveData = False Next pt End If End With Next ws End Sub Private Sub Workbook_Open() Dim pt As PivotTable Dim ws As Worksheet For Each ws In Worksheets With ws If .PivotTables.Count 0 Then For Each pt In .PivotTables pt.PivotCache.Refresh pt.SaveData = True Next pt End If End With Next ws end sub Regards William willwest22@yahoo.com "Dave Peterson" <ec35720@msn.com wrote in message news:3D35E8A5.B3F5A2CF@msn.com... | I don't have a suggestion for reducing the size, but I just tried this | experiment in xl2002. | | 14 columns x 1300 rows of random numbers. | | 3 pivot tables with about 4-6 fields in them. | | When I saved the workbook with the pt's based on an existing report, I got | 441K. When I did the same file but created a separate report, the file was | saved at 850K. | | I think that pt's eat a lot of space/memory. | | If you're really concerned about filesize (I wouldn't be...), maybe you could | build the pivot tables when you open the workbook and delete them before you | close it. Seems like a lot of work for not much benefit. | | Personal opinion follows: | | With the possible exception of when excel overshoots the last used cell in a | worksheet and makes the saved file larger, I pretty much accept the fact that if | excel needs ??KB (or MBs), then it needs them. | | If you have to email it to someone, you could zip it first. If you have to put | it on a floppy disk and it still won't fit, zipping (at least winzip) allows you | to span floppies. | | | | Charles Sergeant wrote: | | I have a workbook which contains various pivot tables, all of which are | based upon the same range of data. When I created the tables, I always | chose to "base it on an existing report" when prompted, rather than create a | seperate report, which promised to make smaller memory and file sizes as a | result. | | However, the file size of the workbook seems much larger than I'd expect | when I save it with data + pivot tables. (1.4meg with data and 1 PT; 6.7meg | with data + 5 PTs; all based on the same initial PT) | | I tried adding some VBA code to cycle through the PTs in the workbook and | set the "SaveData" property to False, then refresh prior to save - not much | help there. | | Any other ideas on reducing the file size, while keeping the PT layouts | intact? Thanks | | -- | | Dave Peterson | ec35720@msn.com |
| 2561 | You can do something like this in VBA: ActiveSheet.EnableOutlining = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True But it has to be reset each time the workbook opens. (So put it in auto_open or workbook_open) Excel won't remember it between sessions. Tim McPhillips wrote: I need help with some forecasting templates I am creating at work. I have worksheets that have three levels of outlines in the rows (to expand and contract detail). I would like to prevent the user from overwriting certain cells by locking cells and protecting the sheet. However, if I protect the sheet the outlines become inoperable. My company is on the cutting edge of technology with Excel 97 and I don't know if this problem has been addressed in later versions of Excel. Any suggested work-arounds? Any help would be appreciated. Tim McPhillips -- Dave Peterson ec35720@msn.com |
| 2566 | Maybe you could just assign the rangename while you're in your code: Option Explicit Sub testme() Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "c").End(xlUp).Row .Range("a1:A" & LastRow).Name = "myStuff1" End With MsgBox Range("mystuff1").Address End Sub This example uses the last used cell in column C to determine a range in column A. But you could modify it to use the same column. You could also use this format: dim rng as range with activesheet set rng = .range("a1",.cells(.rows.count,"A").end(xlup)) end with rng.name = "mystuff2" The end() stuff is pretty neat. It's like using the End key in a worksheet to go to the next set of cells. In this case, (.cells(.rows.count,"A").end(xlup)), it starts at A65536 (65536 = rows.count) and goes up until it finds something to make it stop (formula/value). Keith R wrote: My apologies, since I can't view postings except via google, and I'm not set up at work to post via google, I can't link this to my previous message, but on a related thread... 1) Is it possible to put an array formula (ctrl-shft-enter) inside a named range? Not the whole named range as an array formula, just part of it (see below). I've been trying with no luck, and my web search hasn't turned up a solution. 2) If an array formula can be used inside a named range, how can I generate that formula programatically, using VBA? for example, I could use the named range: =OFFSET("A" & {=MAX(IF(ISBLANK(3:3)=FALSE,COLUMN(3:3),0))},1,1,1,1) where the key component: =MAX(IF(ISBLANK(3:3)=FALSE,COLUMN(3:3),0)) should be an array formula to return the last used cell in the row. Then, once that works (assuming there is a way to make it work), I'd want to create the named range via VBA, something like- ActiveWorkbook.Names.Add Name:=MyStuff, RefersTo:="OFFSET({=MAX(IF(ISBLANK(3:3)=FALSE,COLUMN(3:3),0))},1,1,1,1)" (I actually want to create a bunch of similar ranges via VBA instead of keying each named range manually) If this isn't possible, please see my other post, asking for a way within a named range to identify the last used cell in a row- this was the only way I could figure out how to do it. Using XL97 on Win95 Thanks, Keith -- Dave Peterson ec35720@msn.com |
| 2570 | I tried it with my 441k test workbook and the size went down to 258K. I went into table options and turned it back on by hand and did another save and it went back up to 440K. I wonder why Charles said that it had only a little affect when he did it. It seemed to work very nicely for me! William wrote: Hi Charles, Hi Dave The most significant way of reducing the file size I have come across is to enter the following code into the THisWorkbook module. (To perform the task manually, select any cell on the pivot table, right click, select "Table Options" and in the bottom part of the dialog box, remove the check mark from "Save data with table layout". If all pivot tables are genuinely linked (and that does not necessarily mean they have been all been created from the same source data) then you do not need to loop through all pivot tables - run the code on one pivot table and they will all be updated. Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("Accounts").Activate Dim pt As PivotTable Dim ws As Worksheet For Each ws In Worksheets With ws If .PivotTables.Count 0 Then For Each pt In .PivotTables pt.PivotCache.Refresh pt.SaveData = False Next pt End If End With Next ws End Sub Private Sub Workbook_Open() Dim pt As PivotTable Dim ws As Worksheet For Each ws In Worksheets With ws If .PivotTables.Count 0 Then For Each pt In .PivotTables pt.PivotCache.Refresh pt.SaveData = True Next pt End If End With Next ws end sub Regards William willwest22@yahoo.com "Dave Peterson" <ec35720@msn.com wrote in message news:3D35E8A5.B3F5A2CF@msn.com... | I don't have a suggestion for reducing the size, but I just tried this | experiment in xl2002. | | 14 columns x 1300 rows of random numbers. | | 3 pivot tables with about 4-6 fields in them. | | When I saved the workbook with the pt's based on an existing report, I got | 441K. When I did the same file but created a separate report, the file was | saved at 850K. | | I think that pt's eat a lot of space/memory. | | If you're really concerned about filesize (I wouldn't be...), maybe you could | build the pivot tables when you open the workbook and delete them before you | close it. Seems like a lot of work for not much benefit. | | Personal opinion follows: | | With the possible exception of when excel overshoots the last used cell in a | worksheet and makes the saved file larger, I pretty much accept the fact that if | excel needs ??KB (or MBs), then it needs them. | | If you have to email it to someone, you could zip it first. If you have to put | it on a floppy disk and it still won't fit, zipping (at least winzip) allows you | to span floppies. | | | | Charles Sergeant wrote: | | I have a workbook which contains various pivot tables, all of which are | based upon the same range of data. When I created the tables, I always | chose to "base it on an existing report" when prompted, rather than create a | seperate report, which promised to make smaller memory and file sizes as a | result. | | However, the file size of the workbook seems much larger than I'd expect | when I save it with data + pivot tables. (1.4meg with data and 1 PT; 6.7meg | with data + 5 PTs; all based on the same initial PT) | | I tried adding some VBA code to cycle through the PTs in the workbook and | set the "SaveData" property to False, then refresh prior to save - not much | help there. | | Any other ideas on reducing the file size, while keeping the PT layouts | intact? Thanks | | -- | | Dave Peterson | ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 2589 | I've tried opening AutoCAD (.dwg) files from within Excel using the following macro: Dim drawing As Object Set drawing = GetObject("C:\piston.dwg") drawing.Parent.Visible = True Although the VBA help file contains a similar example, this macro does not work, and gives a error saying: "File name or class name not found during Automation operation". Any help will be greatly appreciated. Thanks, AP |
| 2603 | Hello, How can I use the Excel function VLOOKUP within my VBA project? It doesn't recognizes it... rs.Fields("Planned_week") = VLOOKUP(the appropriate parameters) |
| 2611 | Hi, You need to add the Microsoft Excel V# Object Library You can then reference all Excel functions using the following command if you don't set up a global application object Excel.Application.WorksheetFunction.VLOOKUP(the appropriate parameters... Best regards KM -----Original Message----- Hello, How can I use the Excel function VLOOKUP within my VBA project? It doesn't recognizes it... rs.Fields("Planned_week") = VLOOKUP(the appropriate parameters) . |
| 2631 | Hi, In VBA module I would like to iterate through the select sheets of an active workbook. Is there a way to determine is a sheet has been selected and is a part of a group. If so how is it done. Eg something like this but the Selected item is not a vaild Worksheet property. Dim xls As Worksheet For Each xls In ActiveWorkbook.Sheets If xls.Selected Then xls.Activate Debug.Print xls.Name End If Next xls Best regards |
| 2714 | This file might be a help: /examples/num2wrds.xls It's in the "Accountants" section on page: /examples/examples.htm It contains two methods to convert numbers to words and two cheque writing routines. The code is open and commented. Regards Andy Wiggins FCCA www.BygSoftware.com Home of "Byg Tools for VBA" and "The Excel Auditor" "Hervinder" <hervinder.sigh@ctvc.co.uk wrote in message news:<1765001c22f11$73a0e930$a5e62ecf@tkmsftngxa07... Is there a function within Excel that will convert a number into a words. For example: convert: 132 into One Hundred and Thirty Two . |
| 2738 | Hi Are you sure that the .dwg file is correctly associated with your AutoCad programme via My Computer/View/Options/File Types ? Regards BrianB ------------------------------------------------------------------ "AP" <APhang@whispertech.co.nz wrote in message news:<i7rZ8.7246$JN4.1221125@news02.tsnz.net... I've tried opening AutoCAD (.dwg) files from within Excel using the following macro: Dim drawing As Object Set drawing = GetObject("C:\piston.dwg") drawing.Parent.Visible = True Although the VBA help file contains a similar example, this macro does not work, and gives a error saying: "File name or class name not found during Automation operation". Any help will be greatly appreciated. Thanks, AP |
| 2749 | Keshava How about Sub SumSelection() MsgBox Application.Sum(Selection) End Sub -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "Keshava Prashanth" <keshav@analytica-india.com wrote in message news:17a9101c22fce$6ad3a960$a5e62ecf@tkmsftngxa07... Hi, I should display the sum of all cells for the column where the cell is highlighted. Using VBA how can I achieve this. Please help me. Regards, Keshav. |
| 2750 | you can use the sum on the right site of the Statusbar also this give the sum of the selection also Regards Ron "Keshava Prashanth" <keshav@analytica-india.com schreef in bericht news:17a9101c22fce$6ad3a960$a5e62ecf@tkmsftngxa07... Hi, I should display the sum of all cells for the column where the cell is highlighted. Using VBA how can I achieve this. Please help me. Regards, Keshav. |
| 2760 | Keshava I don't know what you mean. I think you mean you want to sum all the cells in the column of the active cell. Is that right? Post back and give us an example. Tell us what cell you selected (active cell) and exactly what you want to happen when you do so. HTH Otto "Keshava Prashanth" <keshav@analytica-india.com wrote in message news:17a9101c22fce$6ad3a960$a5e62ecf@tkmsftngxa07... Hi, I should display the sum of all cells for the column where the cell is highlighted. Using VBA how can I achieve this. Please help me. Regards, Keshav. |
| 2767 | Keshava IF "highlighted" means "colored", you can use this UDF to Sum them. In a cell enter =SumColor(cellref,range) where cellref is a cell with the color you want and range is the range of cells to SUM. Function SumColor(rColor As Range, rSumRange As Range) 'Sums cells based on a specified fill color. Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End Function HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 20 Jul 2002 02:18:32 -0700, "Keshava Prashanth" <keshav@analytica-india.com wrote: Hi, I should display the sum of all cells for the column where the cell is highlighted. Using VBA how can I achieve this. Please help me. Regards, Keshav. |
| 2779 | Based on the use of X2K === How do I in VBA code do the following: I want to put the contents of Sheet1's B37 in Sheet2's B9 That's all, when I click Command Btn 1 Thank you, -z |
| 2785 | If contents mean value only, then Worksheets("sheet2").Range("b9").Value = _ Worksheets("sheet1").Range("b37").Value If you meant all the attributes (font color/fill color/border, formula...): Worksheets("sheet1").Range("b37").Copy _ Destination:=Worksheets("sheet2").Range("b9") You might want to read about getting started with macros at 's web page: /dmcritchie/excel/getstarted.htm He's got a lot of tips you might like. -z wrote: Based on the use of X2K === How do I in VBA code do the following: I want to put the contents of Sheet1's B37 in Sheet2's B9 That's all, when I click Command Btn 1 Thank you, -z -- Dave Peterson ec35720@msn.com |
| 2792 | Thank you, and I most certainly read your reference -z "Dave Peterson" <ec35720@msn.com wrote in message news:3D39F5DF.68CD9EB5@msn.com... If contents mean value only, then Worksheets("sheet2").Range("b9").Value = _ Worksheets("sheet1").Range("b37").Value If you meant all the attributes (font color/fill color/border, formula...): Worksheets("sheet1").Range("b37").Copy _ Destination:=Worksheets("sheet2").Range("b9") You might want to read about getting started with macros at David McRitchie's web page: /dmcritchie/excel/getstarted.htm He's got a lot of tips you might like. -z wrote: Based on the use of X2K === How do I in VBA code do the following: I want to put the contents of Sheet1's B37 in Sheet2's B9 That's all, when I click Command Btn 1 Thank you, -z -- Dave Peterson ec35720@msn.com |
| 2794 | Only if you are willing to use a programmatic (VBA) solution. Post back and I'm sure someone, if not I, will help out. -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <18a8301c23064$e46d33e0$37ef2ecf@TKMSFTNGXA13, Terri Beth <Terri01 @directvinternet.com wrote Is there a way to put a formula (or something)in a sheet tab that will enable me to name the tabs based on list in one of my worksheets? Example: In a worksheet, A1=Mary, A2=Brett, A3=James. The sheet tab 1 will be named Mary, sheet tab 2 will be named Brett, and sheet tab 3 will be named James? Also, if I change the names on my list, then the sheet tab will auto update with the new entry. |
| 2795 | Thank you. It am very willing to use a VBA solution. I searched this site (and others) for this type of solution before I posted a new item. Again, thank you for helping me. -----Original Message----- Only if you are willing to use a programmatic (VBA) solution. Post back and I'm sure someone, if not I, will help out. -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <18a8301c23064$e46d33e0$37ef2ecf@TKMSFTNGXA13, Terri Beth <Terri01 @directvinternet.com wrote Is there a way to put a formula (or something)in a sheet tab that will enable me to name the tabs based on list in one of my worksheets? Example: In a worksheet, A1=Mary, A2=Brett, A3=James. The sheet tab 1 will be named Mary, sheet tab 2 will be named Brett, and sheet tab 3 will be named James? Also, if I change the names on my list, then the sheet tab will auto update with the new entry. . |