VBA and Pivot Table Query for Text values

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Conditional Formating Hi Every one, I have a sheet which currently uses Excel 2000 conditional formating to change the cell color based on a formula that comes from another sheet. So depending on the alphabet result that comes from the formula the cell's color changes for upto 3 results L-Yellow M-Orange H-Red But now we have a new requirement which says a new N-Green is to be added. I have tried the macro Worksheet_Change suggested by others for the worksheet but it only works when there an direct addition to cell and some key pressed. Can any one suggest another alternative, I am new to VBA so any help is appreciated. Tag: VBA and Pivot Table Query for Text values Tag: 981381
  - 2
    - Converting Excel Equation into VBA I currently have a spreadsheet that starts up with a series of userforms. Within these userforms an employee will select certain criteria to base equations on. I've built all of the dialog boxes and everything works great. The issue I'm having now is that when the employee hits the final command button I want an equation to populate. The current example is very simple and only has one requirement. I originally wrote the equation in excel to first demonstrate that using several SUMIFS we could accomplish what we needed. I was then told that there could be many different criteria and I would prefer to have excel only calculate one equation instead of many if they only needed one at a time. Below is the current excel equation. =((SUMIFS('recovery idea.xlsm'!PAID2003,'recovery idea.xlsm'!DAYS2003,">="&$A2,'recovery idea.xlsm'!DAYS2003,"<="&$B2,'recovery idea.xlsm'!DTEOFPAY2003,"<="&'recovery idea.xlsm'!MAXDATE))+(SUMIFS('recovery idea.xlsm'!PAID2007,'recovery idea.xlsm'!DAYS2007,">"&$A2,'recovery idea.xlsm'!DAYS2007,"<="&$B2,'recovery idea.xlsm'!DTEOFPAY2007,"<="&MAXDATE)))/(SUMIF(REFERREDDTE,"<"&MAXDATE-RecoveryRates!$A2,BALREFERRED)) A2 currently equals 0 and B2 equals 30. A3 would equal 31 and B3 60... so on and so forth. The first two SUMIFS gather amounts that meet the criteria (in this case any payment made prior to the MAXDATE, the max date is being pulled from the last dialog box). The third SUMIF divides the total by the amount that was referred. Basically showing the amount collected over the total possible. Below is the current code for when the command button is hit. Private Sub CommandButton2_Click() Sheet4.Cells(2, "H") = TextBox1.Value Unload AllLegalNone End Sub Thanks! Tag: VBA and Pivot Table Query for Text values Tag: 981380
  - 3
    - Is there a test for an empty string that returns a boolean? Hi I'm passing the ElevCptn parameter as Optional: Private Sub SetOptBut(FontBack As Boolean, PlnCptn As String, Optional ElevCptn As String) I want to test to see if the parameter has been included & Enable a button if it is, & Disable it if is isn't, so I'd like it to return a boolean. Something like this: CboElevation.Enabled = IsEmpty(ElevCptn) Except that doesn't work because "" isn't an empty string. Any help is appreciated Ta Dave F. Tag: VBA and Pivot Table Query for Text values Tag: 981375
  - 4
    - Macro - How do i....? I have a worksheet that starts out like this with 5 columns: A B C D E 1000 0 14630.17 Bank of Amer Disbursement Account 2200 0 250.64 401K Pension Due 6220 5192.31 0 Staff Veterinary Salaries 0 6223 1179.74 0 Veterinary Assistant Wages 157.81 6224 908.5 0 Receptionist Wages 79 6225 4843.19 0 Veterinary Technician Wages 410.98 6227 1346.15 0 Practice Manager Wages 0 6245 262 0 Grooming Payroll Expenses 0 6290 1380 0 Payroll Taxes 6290 0 3525.19 Payroll Taxes-ee 6290 3525.19 0 Payroll Taxes-ee 6315 0 14.4 Disability Insurance 6330 0 342 Medical/Dental Insurance 6355 125.32 0 Retirement Plan Contributions The first part of the marco I have created creates a new column before A and takes the amount in row 1column C and pastes it to all rows that hold information then deletes first row. Looks like this: A B C D E F 14630.17 2200 0 250.64 401K Pension Due 14630.17 6220 5192.31 0 Staff Veterinary Salaries 0 14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81 14630.17 6224 908.5 0 Receptionist Wages 79 14630.17 6225 4843.19 0 Veterinary TechWages 410.98 14630.17 6227 1346.15 0 Practice Manager Wages 0 14630.17 6245 262 0 Grooming Payroll Expenses 0 14630.17 6290 1380 0 Payroll Taxes 14630.17 6290 0 3525.19 Payroll Taxes-ee 14630.17 6290 3525.19 0 Payroll Taxes-ee 14630.17 6315 0 14.4 Disability Insurance 14630.17 6330 0 342 Medical/Dental Insurance 14630.17 6355 125.32 0 Retirement Plan Contributions The next part I am unsure of. What I need to do is take the amounts in column F and move them to the bottom of column C. I also need to eliminate spaces. The catch is that this document may never be exactly the same so it cannot be cell specific. Any suggestions are greatly appreciated. This is my first time working with macros. If you need more info please let me know. Thanks, -- Malissa Tag: VBA and Pivot Table Query for Text values Tag: 981370
  - 5
    - =sum doesn't work properly I'm confused! I've been entering =sum formulas without a problem. Suddenly, today, all I get is a "0" total instead of the sum of the column. For instance, =sum(d36:d43) should equal 17, but it shows a 0. The cells are formatted as "general". What happened? Tag: VBA and Pivot Table Query for Text values Tag: 981366
  - 6
    - Function Call to check Workbooks.Open HI there... I written a Sub that opens Excel files from a network drive and imports them into an Excel sheet one at a time in an append fashion inside a loop. So, I have create a different Sub previously that export Excel files to their respective network folders, and then employees work on it, and then we needed an automatic way to import them all in for analysis. I have a Log from from the previous export process so records the locations/file names of all the excel files that were exported, so I use that to import them back in. My Sub works very well, but I wanted to include a way to check when the File is being opened for different supprise conditions that can arise, including errors. I am not sure how Excel behaves in opening a file if someone else has it open and if working on it. or IF the file is missing for some reason from the expected location where it should be or whatever other conditions there exists that could become problematic, basically I want the script to cycle through all the files and handle errors or problems gracefully so that the VBA don't error out. Anyways, I am posting my entire VBA script that I did, which works, but I wanted to add some handling, and I am not sure if a Function Call would be appropriate here, have little experience with Function Calls. Also, I consider my skills like beginner to maybe a little intermediate, so if anyone has suggestions on making the script better, let me know. But basically it reads the locations and file names from a Log sheet, so addresses would be something like this: Location the Store folders were written to: c:\Documents and Settings\LANID\Desktop\test_store_import\Store_1_.xls c:\Documents and Settings\LANID\Desktop\test_store_import\Store_2_.xls Location the Store folders were written to, but in the real script they will point to network drives, instead of my local computer for testing..... The script also strips the store number value from the file name, so that the right sheet for copy can be selected, which in this example would be a sheet named 1, which represents store number 1, thanks a lot! code below ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++ Sub Import_From_Log() Dim wsLogName, wsOpenName As String Dim wsNew, wsLog As Worksheet ' New sheet that will contain all the imported Excel sheets, appended in one sheet Dim wsLogRange As Range Dim FileToOpen, get_store_number As String Dim lastrow, lastrow2, lastrow3, real As Long Dim RangeCell As Range Dim Switch As Worksheet wsLogName = ActiveWorkbook.Name Set wsLog = ActiveSheet Application.ScreenUpdating = False With wsLog Sheets("Log").Activate lastrow = .Range("A" & .Rows.Count).End(xlUp).Row ' last row is stored in variable RangeFinder Set wsLogRange = Range("A2:A" & lastrow) End With Set wsNew = Sheets.Add ' Add the new workbook that will contain the updated lists wsNew.Name = "File_Import_Appended" For Each Value2 In wsLogRange FileToOpen = Value2 With wsLog Workbooks.Open FileName:=FileToOpen ' works up to here wsOpenName = ActiveWorkbook.Name ' record the name of Excel file just opened into a variable for later usage... get_store_number = Left(Mid(wsOpenName, 7), 1) ' gets the value for the sheet number to select and copy from, ignore all else ActiveWorkbook.Sheets(get_store_number).Activate ' make sure to only select the store sheets to copy from Range("A1").Select ' start by placing cursor into cell A1 Selection.End(xlToRight).Select ' find last column Selection.End(xlDown).Select ' next - find last row Set RangeCell = ActiveCell ' record the right side of the range and assign to RangeCell Range("A1:" & RangeCell.Address()).Select ' select the range Selection.Copy ' now copy it Windows(wsLogName).Activate ' switch back to the Append sheet ActiveWorkbook.Sheets("File_Import_Appended").Activate ' make sure we are activating the append sheet With wsNew lastrow2 = .Range("A" & .Rows.Count).End(xlUp).Row End With If lastrow2 = 1 Then ' if sheet is blank that mean nothing was pasted, so start here for the first paste ActiveSheet.Paste Else real = lastrow2 + 1 ' if the sheet was already posted, we want all other pastes to follow this rule here Range("A" & real).Select ActiveSheet.Paste End If Application.Windows(wsOpenName).Activate Application.CutCopyMode = False ' clear the clipboard, otherwise a window will popup asking if clipboard contents should be saved ActiveWorkbook.Close SaveChanges:=False ' close the Import Excel Workbook End With Next Value2 With wsNew Cells.EntireColumn.AutoFit End With Application.ScreenUpdating = True End Sub Tag: VBA and Pivot Table Query for Text values Tag: 981362
  - 7
    - Calculating Cells What causes Calculating Cell 100% and locking up of the entire MS-Excel program? Tag: VBA and Pivot Table Query for Text values Tag: 981360
  - 8
    - Open to today's date Hello I have a workbook with a sheet for each week of the year. The dates are in the same row in each sheet. I would like to be able to have the workbook open to the sheet that contains today's date. Thanks for your help! Tag: VBA and Pivot Table Query for Text values Tag: 981355
  - 9
    - need help tweaking Debra Dalgleish macro greetings, i pulled the following code from ms. dalgleish's data validation file and would like the macro to stop once it reaches at certain row, if the last number is continually overwritten that's fine. i've tried if statements based on "target.row + 1" being less than a numerical value, say 10, but that doesn't seem to work (if someone could clue me into why that'd be great. any help appreciated, and props to ms. dalgleish and her fab, i.e. incredibly useful, website =D http://www.contextures.com/index.html Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 2, 3, 4, 5, 6 If Target.Offset(1, 0).Value = "" Then lRow = Target.Row + 1 Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub Tag: VBA and Pivot Table Query for Text values Tag: 981353
  - 10
    - Checkboxes and Arrays When working with Check Boxes, is it not possible to use arrays to keep track of them, as with worksheets? SET UP Dim all_boxes As Variant ' setting up ranges Set range_create = Worksheets("INTRO").Range("F7") What I am trying to do: ' create array listing the names of every possible sheet (there are MANY more sheets than listed here) all_boxes = Array("create_111", "create_112", "create_113") ' select all other check boxes by making them true For offset_var = 0 To 2 (there are normally 27 check boxes) Worksheets("INTRO").Array(all_boxes(offset_var)).Value = True Next offset_var What works: Worksheets("INTRO").create_111 = True Worksheets("INTRO").create_112 = True Worksheets("INTRO").create_113 = True ' etc... this takes up a LOT of space, and I always like trying to make more efficient code, if possible Problem: "Object doesn't support this property or method" Attempts to fix it: - I have tried putting the names in the array without "" (quotation marks) - I have tried removing the Array Worksheets("INTRO").(all_boxes(offset_var)).Value - I have tried without the Value Worksheets("INTRO").(all_boxes(offset_var)) - I have tried using Shapes with and without Value Worksheets("INTRO").Shapes(all_boxes(offset_var)) Worksheets("INTRO").Shapes(all_boxes(offset_var)).Value It is problem something extremely simple... Thanks for any help! Tag: VBA and Pivot Table Query for Text values Tag: 981346
  - 11
    - Formula for selecting highlighted cells only Is there a formula I can use to capture only highlighted cells in a row? Tag: VBA and Pivot Table Query for Text values Tag: 981344
  - 12
    - currency formatting of cells with code Hi, below is the code I have so users from different countries can enter their currency symbol. e.g $ or £ or ? or "Ksh" (Kenya Shillings), Bht (Thai Baht), etc. By them entering their currency symbol (or letter code) into cell M2 (Cells(2, 13)), the Change event fires and it formats a Range (OtherCurrencyCells), with this format. It works quite well, but when I try to enter certain letters into M2, nothing happens. For about 1/3 of letters it will fire, but won't change the format. E.g. It accepts all the currency symbols I've tried so far, but when I try to enter "ksh", or "bht", it won't change. But it will change for "kqr" and some other single and multiletter combinations. I have no idea the reason or the pattern. It will change for "k", but if I enter "ksh", it only takes the "k" as the currency format. But if I put any of these unacceptable ones inside "quotes", it takes them all. So I guess that could be my solution, to have users enter letter codes for their currency format, e.g. Ksh, Bht, in double quotes. But now I'm just plain curious as to why it won't accept certain of these letters. Any ideas? Thanks, Harold Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target On Error GoTo ErrHandler: If Intersect(t, Range("A1:z115")) Is Nothing Then Exit Sub Range("OtherCurrencyCells").NumberFormat = Cells(2, 13).Value & "* #,##0" Exit Sub ErrHandler: End Sub Tag: VBA and Pivot Table Query for Text values Tag: 981343
  - 13
    - Record Macro Window. Hi, I cannot see the small window which pop's up when we select record macro from the option tools>Macro> Can any one help tell me to to get it back so that i can record the macro with or without refrence mode. Regards Heera Tag: VBA and Pivot Table Query for Text values Tag: 981339
  - 14
    - worksheet menu bar Running 2003, xp pro sp 2. User cannot access the Options item on the Tools submenu in the Worksheet Menu Bar. At least one spreadsheet is open. Options is not greyed out. When anyone else logs onto her machine, it works perfectly. I logged on as Administrator and ran Repair via add/change programs - didn't help her. The registry contains the following in HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common\Toolbars\Settings Value 7 Name: Microsoft Excel AWDropdownHidden Type: REG_DWORD Data: 0x1 Anyone know what this is? Should I remove it? She swears she has not deleted or changed anything and that it worked fine yesterday. Any ideas about what may have happened? Thanks - Mike Tag: VBA and Pivot Table Query for Text values Tag: 981337
  - 15
    - Any sample to connect a remoted database through Excel? Does anyone have any sample to connect a remoted database through Excel? I need to make a connection with a database in China, and I locate in HK. Could anyone give me any suggestions on what I need to retrieve any data from a database? What I can think of is URL, username, password, ... Does anyone have any suggestions? Thanks in advance for any suggestions Eric Tag: VBA and Pivot Table Query for Text values Tag: 981323
  - 16
    - VBA formula too long? Hi, I am creating a utilisation tool in Excel which contains data on Sickness, Holidays and Other Absence. I have created the formula below in Excel to account for how much Other Absence has been taken year to date. I have then transferred this into VBA and changed the format to R1C1 and adapted the ranges so that they are defined by two variables (j - start of year, based on what today's date is & k - end of year, based on what today's date is). See code below. In reformatting however the formula is pushed onto a new line of code and the usual "_" does not seem to read as me wanting the code to continue on the next line. Is it possible to have a formula that is simply too long? I am getting more confident in Excel and VBA now and can pretty much do everything I want to, but I am equally confident that there is a better way of doing everything that I do :) I am not especially fond of the formula / code below so please feel free to scratch it if that provides a more simple solution. (I haven't finished reformatting after I encountered the problem.) EXCEL FORMULA =(SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi")))) REFORMATTED VBA CODE ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 & "]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_ <B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))" Thanks in advance, JohnP Tag: VBA and Pivot Table Query for Text values Tag: 981321
  - 17
    - Getting folder size information into cell??? Hello, I can't seem to figure out how to get folder size information into an excel spreadsheet cell. I have the filer/server location information in one cell and I'd like to use the information within that cell to extract the folder/directory size and populate another cell. I can't seem to figure out how to go about this? Any suggestions and/or solutions (if any) would be greatly appreciated. Please bear in mind, I'm a noob with this excel programming. ;-) Thank you. Tag: VBA and Pivot Table Query for Text values Tag: 981320
  - 18
    - Merge muliple sheets in multiple workbook below each other. how do i Merge muliple sheets in multiple workbook (in same folder) below each other. However each respective sheet should go to a new sheet. Note all sheets in individual workbook are named same. Please help me. all responses appreciated. Thanks Tag: VBA and Pivot Table Query for Text values Tag: 981319
  - 19
    - workbook / worksheet change event Is it possible to use the act of navigating to a different worksheet as an event in VBA programming? For instance, if I had a workbook with multiple worksheets, and the primary sheet that is displayed when the workbook is opened is called "setup", I want to make sure that you cannot navigate to or view any other worksheets until all necessary changes are made to the setup worksheet. Tag: VBA and Pivot Table Query for Text values Tag: 981298
  - 20
    - Pulling data from 3000 files Hi All, I'm going crazy try to copy and paste data from 3000 files into 1 file. Is there a way to automatically open a file, copy columns 1 -5 and then append it to the file where I want it all placed? Thanks in advance... Tag: VBA and Pivot Table Query for Text values Tag: 981297
  - 21
    - My code enters break mode but there are no breakpoints For some odd reason my code is acting like there are breakpoints in a handful of locations. I tried exporting the module, deleting the old excel file, creating a new excel file, importing the module, and running it. Same thing happened. Then I copied the code into Word, deleted the Excel file, re-created it, and pasted the code into a new module. Same problem. Any ideas??? I'm using Excel 2007. Tag: VBA and Pivot Table Query for Text values Tag: 981287
  - 22
    - Macro to show/hide rows I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! Tag: VBA and Pivot Table Query for Text values Tag: 981276
  - 23
    - Variable Input I have to use, what I thought was, a simple variable input, but it's turning out to be not so simple. Basically I loop through all sheets in a book, as long as the sheets <> "Sheet1". Then do some basic formatting and some simple math. Everything works fine except for this part: Chng = Range("F2") and this part: c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" I have names in column F of each sheet, which are all the same on each sheet, but different names are on different sheets. All I need to do is figure out the value in F2 on each sheet and use that in my IF function. I thought I could just assign the value in F2 to the variable Chng and then just use that variable in my IF function. Excel has other thoughts and it is telling me that I can't do that. Why? All code below: Sub Math() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet Dim myA As Range Dim lastYRow As Long Dim sumRng As Range Dim Tot As Double Dim Chng As Variant For Each sh In Worksheets If LCase(sh.Name) <> LCase("Sheet1") Then sh.Activate With sh .Rows(1).Font.Bold = True Chng = Range("F2") lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Each c In .Range("F2:F" & lastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" End If Next c lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row Set sumRng = .Range("Y2:Y" & lastYRow) Tot = Application.WorksheetFunction.Sum(sumRng) 'MsgBox Tot .Cells(lastRow + 1, "Y").Value = Tot '.Rows(2).Delete End With End If Next sh End Sub Any help is appreciated. Regards, Ryan-- -- RyGuy Tag: VBA and Pivot Table Query for Text values Tag: 981275
  - 24
    - Macro Error Help!! Please I need help in resolving this error "Sub or function not defined". I tried using Ron's code http://www.rondebruin.nl/copy3.htm and i get error. Please help me in identifying what am doing wrong. all responses appreciated. Sub RDB_Module1() ***** This get highlighted Dim myFiles As Variant Dim myCountOfFiles As Long myCountOfFiles = Get_File_Names( _ ****this also gets highlighted I believe these two function are not properly called or referenced to. HELP Thanks Yossy Tag: VBA and Pivot Table Query for Text values Tag: 981273
  - 25
    - Count function? If Col A contains a text variable and column B contains a numerical variable associated with the text variable, what is the simplest formula I can put into a single cell that will count the number of occurrences of the numerical variables in Col B associated with the text variable in A? Tag: VBA and Pivot Table Query for Text values Tag: 981272
- Next
  - 1
    - Appending Contents Of Multiple Spreadsheets Into One Hello. I would like to open up a new spreadsheet and append rows 2 - EOF from six other spreadsheets into that new one, be prompted for the name to save it with, then save and quit. Would someone be kind enough to offer some VBA code to do this with? Thank you, Sheldon Potolsky Tag: VBA and Pivot Table Query for Text values Tag: 981259
  - 2
    - What's the API call to kill the KB buffer? I don't want keystrokes typed in advance of my UserForm appearing to remain alive and execute. Anyone know the API call (which I assume this requires) to flush the keyboard buffer? Thanks much. *** Tag: VBA and Pivot Table Query for Text values Tag: 981247
  - 3
    - ListBox Displaying With Some Items Already Selected In 2007 I have an application that I have been using without problems since Office 2000. It has a list box, which is populated using the command: cyo_dialog.sel_box.RowSource = "product_list" It then goes on to ensure that all the items are deselected with the code: For a = 0 To (cyo_dialog.sel_box.ListCount - 1) cyo_dialog.sel_box.Selected(a) = False Next a cyo_dialog.sel_box.ListIndex = 0 Since I started using this on 2007, for some unknown reason two of the first three items come up selected (normally item 1 with either 2 or 3) , and are then not deselected by the code that follows. I have found 2007 to be very buggy and inconsistent in its running, but I can see no reason why this should now be happening. Thanks in advance. Tag: VBA and Pivot Table Query for Text values Tag: 981246
  - 4
    - Checking if a cell contains a string that is not case sensitive I am trying to count how many times a specific string "abc" forms part of a cell in a spreadsheet. For example abcgfd, abcdse, efgabc would be vaild and I would like to count these abdcef, bcadef, cbaeds would not be valid and I would not want to count these. Can anyone help point me in the right direction? Thanks. Tag: VBA and Pivot Table Query for Text values Tag: 981244
  - 5
    - TreeView's pos on UserForm changes by itself. Until I complete the excruciating process of finding out how to disable screen updating while pruning a TreeView (deleting & moving nodes), I hit on the idea of setting TreeView.Visible = False, then = True again when processing's done. When I do, the TV's re-shows in a DIFFERENT position on the UserForm than before. (It has shifted to what seems like the U.L.-most corner.) NOW what's going on? Thanks. *** Tag: VBA and Pivot Table Query for Text values Tag: 981243
  - 6
    - updating pivot table data via external xls file which is replaced hi, the problem with my code for updating the table is that it works on my computer but if i try it on another one the pivot table itself isn't updated but a new worksheet created with an empty pivot in it ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "U:\xyz\xyz_Shared\test\TestLines.xls!R4C1:R50000C38" The file from which the table should get it's data is called TestLines.xls Tag: VBA and Pivot Table Query for Text values Tag: 981240
  - 7
    - Numeric data Dear all Pleasae guide me to find out numeric data in cell where the cell contains both aplha & numeric value. Like i want to take out pincode from one single cell where in the location and pincode is mentioned in a single cell Eg ( Mumbai 400018 ) Serch Result shud b 400018 Thanxs in advance Tag: VBA and Pivot Table Query for Text values Tag: 981235
  - 8
    - Cell references and merged cells Is there an explanation somewhere of how to handle column references when there are merged cells in another row? For example: Private Sub TestOffset() 'Expect references to columns A, B, and C. 'Get references to columns AB(merged), C, and D, if there are merged cells in other rows Dim r As Long Dim rng As Excel.Range Range("A1").Activate Set rng = Range("A1") With rng Debug.Print .Address .Value = 1 .Offset(0, 1) = 2 .Offset(0, 2) = 3 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With Range("A20").Activate Set rng = Range("A20") Range("A20:B20").Merge With rng Debug.Print .Address .Value = 11 .Offset(0, 1) = 12 .Offset(0, 2) = 13 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With End Sub Tag: VBA and Pivot Table Query for Text values Tag: 981232
  - 9
    - Excel 2007 SP1 VBA Help I have created a WortArt object in Excel programatically using VBA and using the preset text effect style msoTextEffect3. Unfortunately, this creates text with the color red. Now I can right click on the word art and change the color of the text to whatever I want. But I cannot find a way to do this programmatically in VBA. Also, trying to use Macro recording is useless for this. It looks like macro recording is broken in Excel 2007. Any help grateful Wheeley Tag: VBA and Pivot Table Query for Text values Tag: 981230
  - 10
    - Help to speed up this macro I have the following macro that runsa a UDF to delete blank rows (every other row). The formula is inserted in cell E11 and copied down for for each line where Col B is not empty. It works just fine, however, there are only 100+ lines and it runs very slowly. Is there something Ican do to speed it up? Sub BudgetJobNo() ' Sheets("Budget").Select Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete Application.DisplayAlerts = False Application.ScreenUpdating = False JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND("" "",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")" Range("E11").Value = JobNo 'And then copy them down to the last line. Range("e11:e11").Select Selection.Copy CR = 11 Do While Cells(CR, 2) <> "" Range(Cells(CR, 5), Cells(CR, 5)).Select ActiveSheet.Paste CR = CR + 1 Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Jim Tag: VBA and Pivot Table Query for Text values Tag: 981229
  - 11
    - How do you collapse a TreeView? I cannot seem to find even basic properties in VBA's TreeView. There is Node.Collapse, which collapses the immediate children of a single node. Gee, Beaver, that's swell. How do you collapse the tree? At the risk of being obvious, I mean: every node. Thanks much. *** Tag: VBA and Pivot Table Query for Text values Tag: 981228
  - 12
    - WM_SETREDRAW doesn't work unless...what? RM Smissaert kindly replied to my recent Q of how to disable screen updating in a TreeView: Private Declare Function SendMessage Lib "user32" Alias _ "SendMessageA" (ByVal HWND As Long, ByVal wMsg _ As Long, ByVal wParam As Long, lParam As Any) As Long Private Const WM_SETREDRAW = &HB *** SendMessage TreeView1.HWND, WM_SETREDRAW, IIf(EnableTheBugger, 1, 0), 0 (The 1 and 0 in my IIf are reversed from his example, which I presume is correct, 'cause it seems to work for me.) But this call fails, UNLESS (I found empirically) I, like, make the call, then show a MsgBox (which interrupts processing and "seeds" the screen first...?) Sort of like having to turn off the pilot before turning off your stove. What additional API call must I make if any to make WM_SETREDRAW work? *** Better yet, how do you do this in a native VBA way? What I'm doing is cleaning & pruning the tree--deleting & moving hundreds or even thousands of nodes. Each operation of which has to wait for my ancient video card, which I inherited from Johannes Vermeer, to repaint (in slow-to-dry Old Master oils) my screen. Thanks much. *** Tag: VBA and Pivot Table Query for Text values Tag: 981227
  - 13
    - Confrimation of copy paste operation Hi, In my application (C#, Office 2007), i am attempting to copy a range of cells containing data to a range on another worksheet. Upon copying the range I then copy a chart into a specific area on the second worksheet. The chart is resized and set up to represent a datapoint per row of data so the chart. Occasionally (and i mean occasionally, i cannot replicate the problem 100% of the time) the chart does not get copied to the second worksheet. I do this a number of times for various different table ranges. Sometimes there will be a number of these charts copied to the same output worksheet and i have encountered this issue where by only some of the charts copy and others don't. I generally use the following code to do the copy paste _chObj = (ChartObject)_excelDataWS.ChartObjects("ChartName"); _chObj.Copy(); _excelPresentationWS.Paste(_presTopLeftRange), paramMissing); I have a couple of questions on this problem. 1) Is there a benefit to using the following copy command?: _chObj.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture); 2) Is there any way to confirm that a copy/paste operation has succeeded and if not then to retry it? I'm really having no joy in diagnosing why this is happening. I get no exceptions thrown when the charts do not appear. Any help is greatfully recieved. Thanks in advance, G Tag: VBA and Pivot Table Query for Text values Tag: 981226
  - 14
    - Capture paste from Office Clipboard Hi, A bit of background first I have an event macro which I want to prevent from running if the user paste some info from the office clipboard into a cell as it triggers my events. The below code works if the data is copied from one section of the spreadsheet to another. Is there anyway to capture Ctrl + V using select case?. Select Case Application.CutCopyMode Case Is = False MsgBox "Not in Cut or Copy mode" Case Is = xlCopy MsgBox "In Copy mode" Case Is = xlCut MsgBox "In Cut mode" End Select FYI I also looked at the on key event but didn't want to add a new module to the workbook if I can avoid it Application.OnKey "^v", "Macro1" If I had to add a module I guess Chip site would be the best start?? http://www.cpearson.com/excel/vbe.aspx Any suggestions of tips would be appreciated Regards VBA Noob Tag: VBA and Pivot Table Query for Text values Tag: 981221
  - 15
    - Modeless Form help please I'm using a modeless form with a spreadsheet. The reason for making it modeless is that I want to be able to use the spreadsheet in the background and refer to the form from time to time. However, When the form is displayed it disables the wheel on the mouse (which is quite a problem for me)... 1. Is there a way around this? 2. What else is different when a modeless form is displayed? Thanks M Tag: VBA and Pivot Table Query for Text values Tag: 981219
  - 16
    - Can i link various text in a cell to a picture I would like to link text in a cell to a picture so when i select or write text in a cell different pictures will appear next to the cell the text is in. Tag: VBA and Pivot Table Query for Text values Tag: 981217
  - 17
    - Thanks to Stephen Bullen for fixing VBA's miserable UserForm. With the qualification that I'm only using Win 2000 and this might've been fixed, and if I understand UserForm correctly: The seeming fact, which I just learned, that the highest-level visual control in VBA, UserForm, by design fails to implement even the most basic Windows features of being resizable, min- and maximizable (and I have yet to see a ScreenUpdate), has got to be the most pathetic, half- assed thing I've seen out of Redmond in a long time. UserForm is not a FORM. It has neither the appearance nor functionality of a form. It has little more than dialog functionality. I mean, when's the last time you've seen something on your Windows screen that's missing the Min/Max/Restore buttons, for God's sake? While they were at it, why didn't they omit caption functionality? Or force UserForm to be modal? (Thank God they didn't, but now I wonder: why not?) But can someone answer: what do users do who have screen resolutions different from the programmer's, when the box he designed doesn't fit their screen? As happened to me today when I ported my NonUserForm from one PC to another. I had to change in the form size IN DESIGN MODE. And, how in the world can all you programmers write an *application* in VBA? I REALLY hope I'm seriously missing something in my understanding here, I really do. It sure wouldn't be the first time. In fact, crow is one of the dishes I eat most gladly. *** In the meantime (and I can't believe it's taken me this long to discover it), I took Peter T's advice and went to Stephen Bullen's fabulous Excel site: www.oaltd.co.uk/Excel/Default.htm where there are all kinds of goodies. Not least of which is FormFun.xls, which includes all the necessary hooks out to Windows to fix the miserable, decrepit piece of crap that is UserForm. [Rant alert!] I have said this for decades: Microflot's Soviet approach to things will be its undoing. Because soon a company is going to come along with a computer on which you don't click a button called 'Start' to shut the goddamned system down. And by Soviet, I mean: Refusing to fix design flaws and their thousands of points of failure to implement their own--excellent-- Windows paradigms in EVERY user control, on EVERY screen, in EVERY context across their ENTIRE product spectrum. Yet, accomplishing that feat between even the most intimate siblings of the Office family is absolutely beyond this company. Instead, where do they spend their computing resources? Migrating us to operating systems requiring multiple cores and multi-gigabytes of storage, so we can have the thrill of executing a few MISERABLE machine instructions to do the things we did pretty well on our old 1981 IBM PCs--like balancing our checkbooks--but with 3D graphics, animation, and an electrical draw of a couple hundred more watts. If you have shares in MS, I say: maybe don't keep them for long. *** Tag: VBA and Pivot Table Query for Text values Tag: 981209
  - 18
    - Excel 2007 - How do I ... bring data into an excel 2007 worksheet that is stored in a sql database table? The idea is to parameterize the "where" clause in the sql select statement limiting the result set. There are lots of examples in the literature with regard to bringing in data stored in cubes --- but not so much from data in sql table. The basic issue is I want bring in descriptive information about say a contract (things like description, signing date, etc). Cubes which most (all) of the literature deals with are dealing with aggregations of amounts or "measures" and not so much about descriptions. As always all suggestions are welcome. Mark Tag: VBA and Pivot Table Query for Text values Tag: 981199
  - 19
    - UserForm ShowModal Problem I have a UserForm that will not Unload correctly for some reason when ShowModal = True. I get a Run-time Error '91': Object Variable or With block variable not set. But when I change ShowModal = False then everything Unloads properly. I have 8 other UserForms that have ShowModal = True and they all Unload just fine. Anybody know why this happens? Thanks in Advance, Ryan Tag: VBA and Pivot Table Query for Text values Tag: 981193
  - 20
    - code to extract data from a website runs of XP but not Vista I am working on code to extract data from a website. I am using CreateObject("InternetExplorer.Application") to start IE. The code is far from working perfectly but when I moved the code from an XP computer to a Vista computer I canâ??t get past the â??Do Until .readystate = 4â?? statement. IE opens visible and I get the following error message: â??The object invoked has disconnected from its clientsâ?? I did not get this error on the XP computer. The code is the following: Sub Tester() Dim objIE As Object Dim allTabs, t, x As Integer Dim r As Long, s As String, c As Long Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = False .Navigate "http://www.masters.org/en_US/scores/index.html" Do Until .readystate = 4 â??hangs on this statement on the Vista computer DoEvents Loop With .document Set allTabs = .getElementsByTagName("TABLE") For Each t In allTabs 'table with scores is always greater than 30 and only table that large on page If t.Rows.Length > 29 Then r = 2 For x = 1 To t.Rows.Length - 1 For c = 0 To 9 s = t.Rows(x).Cells(c).innerHTML ActiveWorkbook.Sheets("Info").Cells(r, c + 1).Value = s Next c r = r + 1 Next x End If Next t End With End With End Sub I have a feeling from looking up the error on the web that it may have something to do with the added security of Vista. Has anyone else run up against this problem? I see a lot of code samples here that use the same code to open IE that I do. -- russ Tag: VBA and Pivot Table Query for Text values Tag: 981192
  - 21
    - Automanipulating IE drop-down list in VBA/Excel I'm strictly a newbie in VB; I've been programming on the mainframe for decades but am only recently come to VBA, VBScript etc, so if you notice that I'm going about this all wrong, be sure and point me in a better direction. I have a spreadsheet that contains data from the web, and I'd like to make it smart enough to update itself when I tell it to. I've learned a little about the "Document" object model, and have gotten to the point where I can browse down through all the .Document.All items and figure out which ones I want VBA to manipulate. I can even fill in the values of web pages' text boxes and simulate a .Click on its buttons. But now I want to make a selection from a drop-down box. Let's say it has a list of states on it: I want my VBA program to pick out the correct state and convince the web page that I've chosen it. I've found the Option items that define the possible selections, and I see that if I find the correct one and change its Selected property to True, it shows up on the page correctly as though I selected it myself. But what the page doesn't do is proceed from that point as though I had clicked on the item - that is, it shows up in the drop-down selection box, but I can't get it to go forward from there. I've tried .Clicking on everything I can think of (might have missed one, though), but nothing seems to work. What am I supposed to do to get to the next step. If there's a FAQ on this, or another forum I should post to, just point me in the right direction. And if there's an easier way than using the Document object model - which wouldn't surprise me - feel free to point that out to me, too. Thanks. Tag: VBA and Pivot Table Query for Text values Tag: 981190
  - 22
    - Create a Macro with a List Hi Experts: > > I would like to build a macro with the List for multiple individuals that > will then enter the data from the List to the Summary Worksheet in the same > Workbook. This is the example of what I want to do.... > > List Sheet > > John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% > in Column D > > Put that data on the Summary Worksheet in Columns B, C, D & E > > I would also like to remove the % sign in Column D > > Can anyone help me with this? I would appreciate any assistance you can > provide. I'm working on a new reporting document that is using VLOOKUPs....a > lot of them every month....I think the Macro would be more efficient, but I > need your help to build on. -- jeannie v Tag: VBA and Pivot Table Query for Text values Tag: 981188
  - 23
    - I am not sure Hello; I am trying to help someone, for which I am not sure there exists a solution. He has a .csv file that he is opening in Excel. The values, somehow, import into the correct conglomeration of data. More in a minute. Column A is a number formatted as text. Column B is a series of numbers, some formatted as text. Those that are not formatted as text, are separated by commas (Huh!!) and hyphens. The object is to concatenate the two values together. Example: A | B | C | | 1 | 357 |Argentina | | 345 |32,34,39 |Paraguay | 40-45 | | | 33945 |27-30,41, | USA |43, 45 | The data should finally look like this for USA (etc.) A | B 3394527 |USA 3394528 |USA 3394529 |USA 3394530 |USA 3394541 |USA 3394543 |USA 3394545 |USA Which column the new values end up in does not matter. I know how to do this fairly rapidly with formula's and a big enough worksheet. Then copy and paste the values to where I want them. I am wondering if this is even possible. I am not a big VB fan. I like to avoid them if I can, but I wonder if this can even be handeled by VB. Column B on one of the cells looks like this as an example. 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584, 264724, 264729, 264772-264773 There is no regularity to the number of digits. There is no regularity to the hyphenation location. There is no regularity to the format of the numbers (which I guess all could be changed to numbers and back to text for concantenation) and there is no regularity to the quantity of values in the cell. Is there any hope for this? Any help would be appreciated. Thank You Frank Pytel PS. I see a lot of posts here that look really nice. I am on the Office Online site. I know these posts can be viewed and edited at other sites. Can I set this up in Outlook so that I can Post and search. Kind of like Outlook Express? Thanks Tag: VBA and Pivot Table Query for Text values Tag: 981185
  - 24
    - How to execute macro based on a users computer settings Hello experts, I'd like to find a method/script to only execute an excel macro based on a users existing file listing on their computer ( or it could be a pre-existing setting; ie. their IP or MAC address). Ideally I'd like to have a macro check for the existence of a certain file (or password phrase encoded in the file) on the directory of a users computer, if YES then execute macro, ELSE don't do anything. This is all done in the background right before the event that usually triggers the primary macro, which is an ftp command line script. My guess is creating a batch file that executes in a windows shell and outputs a value that could be read, but I'm not sure if that's the best way to achieve what I want. I want to avoid using a pop-up form and password inside the excel file - this is currently what I already have. Thank you for your help, -Doug Jr. Tag: VBA and Pivot Table Query for Text values Tag: 981182
  - 25
    - How best to break up code I hit the "procedure too large" error and I can't seem to get past it. I'm well aware of the reason and I can tell you know that I've effectively crammed all the IFs and LOOPs into it that I can, so let's assume that the code can't be condensed further. I've spent half-a-day reading through this group and Googling and can't seem to make it all come together. So my question is, how do I create this "well structured code" when I have SO MANY declarations which need to be used across the different procedures? I don't want to pass all of my declarations as arguments as I feel like that would take forever. There has got to me a more logical way that I am missing. And functions are supposed to be small aren't they? In reading the vba help, it seems that declaring the procedure or perhaps the individual variables using "static" would allow them to persist but they don't. Does it make a difference if I put everything in separate modules? From what I've read that isn't necessary. I read somewhere in this group that someone had a procedure that held all of their variables and they just called it in each procedure. How would this have been done? And if the variable is changed in the first procedure that calls it, then how can I get that value to persist to the next? I know the first question is going to be, let me see your code. But because this is more of a conceptual question, I'm leaving it out for now. I'll gladly provide some as needed. Many thanks for any assistance. Shelton Tag: VBA and Pivot Table Query for Text values Tag: 981179

I think I am having a problem with my code because if the value in a cell is
text instead of numeric, the symbol ' needs to be added to the code. In the
code below, where would I put the ' to switch the variable deptNUM from
numeric to text?

& "WHERE (AC_CLAIM.GL_BUS_UNIT_A_NBR=" & deptNUM & ")")

Thanks

Top