Help with match function, i need to tally

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
    - ADO help: Only get first 255 characters of field... Hi, can somebody help with this? I have an Access database, that has a table with a memo field in it. This table contains records longer than 255 characters. I am using ADO to get this info out of the database into a recordset, and then loop through the recordset to populate a spreadsheet. However, I only get the first 255 characters for the data that comes from the memo field. How do I get the entire value stored in the database? Somebody mentioned using "adLongVarWChar". But how do I do this? Please help, Thanks, Tag: Help with match function, i need to tally Tag: 981019
  - 2
    - distributing a spreadsheet with macro buttons How do you distribute an Excel 2003 spreadsheet application with buttons linked to macros? Do you actually have to relink all buttons to the macros for every new location? I can't believe that - I must be overlooking something. My spreadsheet has buttons which are linked to macros. When the spreadsheet is moved to a different location (on the same computer or a new computer), the following error occurs when a button is clicked: "A document with the name fubar.xls is already open. You cannot open two documents with the same name etc." I moved all macros into the Sheet1 Excel Object. That didn't help. I have tried using buttons from the control toolbox (which I read should correct this but doesn't), and from the forms toolbox, and the "custom menu item" from the macros list. Nothing works. When I put the spreadsheet on another machine, sometimes the buttons are there but I have to link them to the macros. Sometimes the buttons aren't there at all. I don't understand this and can't find anything to clarify it. Where are the toolbars stored? Why can't the buttons just look in the current workbook for the macros? Tag: Help with match function, i need to tally Tag: 981004
  - 3
    - Easy Worksheet Function help Where did i go wrong? =IF(F4='> 100',"1",)OR(F4='76 - 100',"2",)OR(F4='51 - 75',"3",)OR(F4='26 - 50',"4",)OR(F4='< 25',"5") Basicallyâ?¦ If F4 is equal â??> 100â?? then display a 1 or, if F4 is equal to â??76 â?? 100â?? then display a 2 or, if F4 is equal to â??51 â?? 75â?? then display a 3 Tag: Help with match function, i need to tally Tag: 981003
  - 4
    - UDF Function to calculate mean moving range (control charts) I was about to post a question as I was trying to create a function that would calculate the moving mean range which I would then be able to use to calculate the UCL and LCL for a series of data i.e Mean + 2.66*Moving mean range, without having to calculate all of the variances between each 2 sets of data....hope that makes sense. However, I have cracked it and wanted to post the Function in case anyone else wants it: It's pretty simple stuff as it is the first user defined function I've written so apologies if this is clumsy code.... Function MMR(CalledCells As range) As Variant Application.Volatile True Dim V1 As Variant Dim V2 As Variant Dim V3 As Variant Dim i As Integer Dim FirstRow As Long Dim LastRow As Long V1 = 0 FirstRow = CalledCells.Row LastRow = CalledCells.Row + CalledCells.Rows.Count - 1 For i = LastRow To FirstRow + 1 Step -1 V2 = Abs(Cells(i, CalledCells.Column).Value - Cells(i, CalledCells.Column).Offset(-1, 0).Value) V3 = V2 + V1 V1 = V3 Next i MMR = V1 / (CalledCells.Rows.Count - 1) End Function Tag: Help with match function, i need to tally Tag: 980999
  - 5
    - Trying to create Macro to delete duplicate Rows Hello, I am hoping someone can help me. I am not an experienced macro writer and I am having a problem on how to write a macro on deleteing duplicate rows. To give you an example of what i am trying to do is: I am trying to delete rows with duplicate information that is in column A,S,T,U,V,W, Y, and Z. But i want to leave at least one row with that info. For example if row 1, 2, and 3 have the same info in A, S, T, U, V, W, Y, and Z. I would want to leave row 1 and delete row 2 and 3. I am hoping someone can help. Thanks Tag: Help with match function, i need to tally Tag: 980988
  - 6
    - Optimising Code I have the following that is part of a larger piece of code that formats a worksheet. Currently, this part takes 15 minutes to run and I would like to know if I could have written this code in a better way: For Each c In r If c.Value = "Complete" Or c.Value = "Not Applicable" Then With Cells(c.Row, c.Column - 28) .ClearFormats .Interior.ColorIndex = 5 .Font.ColorIndex = 2 .Font.Name = "Verdana" .Font.Size = 8 .HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .NumberFormat = "dd/mm/yy" End With End If Next This is running on a area that is 203 rows x 17 columns, the number of rows varies. I cannot use conditional formatting as it already exists in the target cells and has to be removed and reformatted as above. Unless there is a better way, of course! Many thanks for any suggestions Louise Tag: Help with match function, i need to tally Tag: 980987
  - 7
    - Multiple checkbox selection I have an excel sheet with many Yes/No, Good/Abnormal checkboxes. The list is long. I want to have this checksheet have a feature to select all Yes, or Select all no, or select all good, select all abnormal, to avoid the operator having to check all if good/bad/ or the other. Any suggestions Tag: Help with match function, i need to tally Tag: 980984
  - 8
    - Excel2000: Avoiding loop in Worksheet_Change event Hi I have a Change Event for a worksheet, where 2 cells on this worksheet are checked, and some actions are taken, whenever some of them changes. By certain conditions, one of actions will be changing the entry in one of checked cells - which will fire the same Change event again, etc. Is there a way to 'switch the Change Event firing off' temporarily. I mean, that when the condition for changing this cell value is met, some code row has to switch executing of further Change events off, after that the change to cell is made, then further change events are turned on again, and then rest of code in current Change event is processed. Tag: Help with match function, i need to tally Tag: 980981
  - 9
    - Automation add-in - UDF to multi cells... Hi all, I have been doing some reading on this forum and could not find exactly what i was looking for so i decided to post. I have an automation add-in written in C#. I need a UDF function to populate multiple cells with data. (like a table of data, and the cell containing the UDF function would be the top left corner of the 'rendered' table). Is this possible? I have read that a UDF function can only return and populate a single cell. Well there must be some way to do it as I have seen other add-ins do this, and im trying to replicate the behaviour. Thanks! Dale *** Sent via Developersdex http://www.developersdex.com *** Tag: Help with match function, i need to tally Tag: 980977
  - 10
    - List of email addresses Hi I have a column with about 40 email addresses one below the other (i cell for each address). I would like to run a macro on the range so that all the addresses are combined in the one cell prefereably in the adjacent column. There is a semicolon at end of each address so I dont have to worry about the data ending up in one cell. Any help appreciated on this. Thanks, Aidan. Tag: Help with match function, i need to tally Tag: 980973
  - 11
    - Open several files and copy the contents I have a template that requires new data from 4 files. I want to open each in turn and copy the contents into specified sheets in the template. Each file is created by another program that always uses the same file name. The users add an extension to the name for each company they are working on to keep htem unique. I have the following code that opens the first file and copies the new data okay. Is there a way to modify this to use the one user input for each file and copy the data in turn to each worksheet? Sub OpenFile() Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String Dim sFilename As String Dim fExitDo As Boolean Dim sFileType As String Dim sFileOpen As String Dim sFileBudget As String Dim sFileJobList As String Dim sFileOrders As String Dim sFileLedger As String Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open WIP Data Files, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open New WIP Data Files " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then sFileBudget = "jobba1-" '--Job budget file to copy to sheet "Budget" sFileJobList = "jobbl1-" '--Job Job List file to copy to sheet WIP" sFileOrders = "salcustd1-" '--Job Customer Sales Orders file to copy to sheet "Orders" sFileLedger = "genjrld1-" '--Job General Ledger Detail file to copy to sheet "Ledger4900" MyPath = "S:\MYOB Data Files\WIPData\" ChDrive "S:\" ChDir MyPath '--users are to add a file name extension to the standard reports and save as .XLS files. It will be same for all 4 data files sFilename = InputBox("Please Provide ONLY the Name you saved the file as. EG: DEMO") FilesInPath = Dir(MyPath & "*.xl*") 'Check to see if files exist (Ron DeBruin) If sFilename = "" Then Exit Sub 'user hit cancel If FilesInPath = "" Then MsgBox "No files found-make sure you have saved your files in the correct location" Exit Sub End If End If sFileOpen = MyPath & sFileBudget & sFilename & ".xls" fExitDo = False Set wkbk = Workbooks.Open(Filename:=sFileOpen) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False Windows("WIP Template V1.xls").Activate Sheets("Budget").Select Cells.Select ActiveSheet.Paste wkbk.Close Savechanges = False Application.DisplayAlerts = True End Sub -- Jim Tag: Help with match function, i need to tally Tag: 980967
  - 12
    - One Workbook to Several Workbooks I have been using this Ron de Bruin code all day long! i've learned a lot today and now, before I turn in, I'm trying to get the code to do one more trick for me, but I can't figure out how to do it. Basically, the code below splits a large Workbook into several files, each with just one sheet. I would really like to split the large Workbook into several smaller files, and attach a summary sheet to each newly created file. The summary sheet is named 'Sheet1'. Is there some way to append a Sheet1 to each newly created file? Sub Copy_Every_Sheet_To_New_Workbook() 'Working in 97-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim DateString As String Dim FolderName As String With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With 'Copy every sheet from the workbook with this macro Set Sourcewb = ThisWorkbook 'Create new folder to save the new files in DateString = Format(Now, "yyyy-mm-dd") FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString MkDir FolderName 'Copy every visible sheet to a new workbook For Each sh In Sourcewb.Worksheets 'If the sheet is visible then copy it to a new workbook If sh.Visible = -1 Then sh.Copy 'Set Destwb to the new workbook Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 If Sourcewb.Name = .Name Then MsgBox "Your answer is NO in the security dialog" GoTo GoToNextSheet Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want If Destwb.Sheets(1).ProtectContents = False Then With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End If 'Save the new workbook and close it With Destwb .SaveAs FolderName _ & "\" & Destwb.Sheets(1).Name & FileExtStr, _ FileFormat:=FileFormatNum .Close False End With End If GoToNextSheet: Next sh MsgBox "You can find the files in " & FolderName With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub Regards, Ryan--- -- RyGuy Tag: Help with match function, i need to tally Tag: 980966
  - 13
    - How do I exit from a macro when a condition is not met In Excel 2003 I have a macro that checks if a cell is not zero. Range ("P124").Select If Selection.Value <> 0 Then Call Check End If Sub Check() Dim Msg, Style, Title, Response Msg = "This JV does not balance, please correct it before you continue." Style = vbOKCancel Title = "Out of Balance Response = MsgBox (Msg, Style, Title) End Sub I want to exit out of the macro routines when the user presses OK. Thanks Andy Tag: Help with match function, i need to tally Tag: 980963
  - 14
    - Userform coding problem I have a userform in which two values are input. What I want to do is on exit from the second text control (txtPurAmt) is clear the current data, set the focus in the date field and be ready to input another record. What is happening is that after I set the focus on the txt Date control, the code wants to run again. Here is what I have so far and have commented where the problem occurs. Any help is greatly appreciated. Thank you. Private Sub txtPurAmt_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strAgain As String, bYes As Boolean Range("A9").End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveCell.Value = Me.txtDate.Value ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.Value = CCur(Me.txtPurAmt.Text) strAgain = InputBox("Add another record?", "Add New Record?", "Yes") If strAgain = "Yes" Then ActiveCell.Offset(1, -1).Select Me.txtDate.Value = "" Me.txtPurAmt.Value = "" bYes = True Else Me.txtDate.Value = "" Me.txtPurAmt.Value = "" bYes = False End If If bYes = True Then '************ 'After the next line, the code starts all over again and the 'Exit Sub line never executes. Me.txtDate.SetFocus Exit Sub Else UserForm1.Hide Exit Sub End If End Sub Tag: Help with match function, i need to tally Tag: 980959
  - 15
    - Upgrade PivotTable drop-down selection box Thanks in advance for any help! I'd like to essentially improve upon the PivotTable drop-down selector for PivotFields. I use Pivots that have so many options to choose from that you end up having to click the box, unclick 'show all' and then dig for the items you want. I basically just want to be able to type in the one I want to choose, or type a portion of it and filter the available items. The ideal situation would be to completely override the current selection box with my own code. That may be impossible or beyond my ability. If it is possible, please give me a reference or point me in the right direction with the code. If that's not possible... I'm looking for design ideas. I haven't yet come up with a great idea on the implementation that will be easy to transfer to other people without having to spend much time on training them to use it. I want this to be applicable to PivotTables across the board (i.e. a modification to PivotTables in general, not just one particular spreadsheet). Thanks again for any help or ideas you can provide. Tag: Help with match function, i need to tally Tag: 980957
  - 16
    - Grab all filtered rows for some action In an autofiltered range, I currently select all the filtered rows for deletion manually. Is there a way to automate this manual selection of filtered rows bit once I select any cell within the filtered rows? Thanks Tag: Help with match function, i need to tally Tag: 980934
  - 17
    - Macro defining dynamic named ranges At times I need to change the source data used in some formulas. When this happens I would like to remove the source data sheet, called RawData, and import a new sheet that contains current data. I am trying to write a macro that would define named dynamic ranges on RawData. Iâ??m using this code I copied from some other post: ThisWorkbook.Names.Add Name:="dAge", RefersTo:="=OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1)", Visible:=True This line doesnâ??t leave any trace in the Name list; Insert>Name>Paste>Paste List does not show â??dAgeâ??, nor does a loop with â??Debug.Print crtName.Name, crtName.RefersTo, crtName.Visibleâ?? Even worse, this simpler one doesnâ??t work either ThisWorkbook.Names.Add Name:="dAge7", RefersTo:="=RawData!$H$1:$H$10", Visible:=True When I add the name with Insert>Name>Define this works: =OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1) I would appreciate any help with this syntax or other suggestions for updating/defining dynamic ranges in VB. Thank you. Tag: Help with match function, i need to tally Tag: 980931
  - 18
    - Drop-Down Arrow in AutoFilter Hi How can I programatically click the drop-down arrow in an autofilter? Geoff Tag: Help with match function, i need to tally Tag: 980929
  - 19
    - Running code. Hello, I have been using VB and C# in Visual Studio for a long time but never used it with Excel. I have an Excel sheet with 16 images. Each one in each cell and having a name of img1, img2, .... I have some VB code which I want to run. When I run it am I asked for a Macro name. Could someone, please, give me some tips on how to run this game code? Here is the code: Option Explicit ' TYPES > ' GameType Type GameType img(1 To 6, 1 To 6) As String LastRow As Integer LastCol As Integer Score As Integer End Type ' GameType ' VARIABLES > Public game As GameType ' METHODS > ' MyWait Sub MyWait(ByVal wt As Single) Dim t As Single t = Timer Do Loop Until Timer - t > wt End Sub ' MyWait ' Positioning Sub Positioning(ByRef game As GameType, ByVal r As Integer, ByVal c As Integer) Dim hc As Single, hf As Single, wc As Single, wf As Single Dim nf As String On Error GoTo fim With Range("tabuleiro") hc = .Cells(r, c).Height wc = .Cells(r, c).Width nf = game.img(r, c) hf = ActiveSheet.Shapes(nf).Height wf = ActiveSheet.Shapes(nf).Width ActiveSheet.Shapes(nf).Top = .Cells(r, c).Top + (hc - hf) / 2 ActiveSheet.Shapes(nf).Left = .Cells(r, c).Left + (wc - wf) / 2 End With fim: End Sub ' Positioning ' Hide Sub Hide(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) With game ActiveSheet.Shapes(.img(r, c)).Visible = False End With DoEvents End Sub ' Hide ' Show Sub Show(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) With game ActiveSheet.Shapes(.img(r, c)).Visible = True End With DoEvents End Sub ' Show ' IsVisible Function IsVisible(ByRef game As GameType, ByVal r As Integer, ByRef c As Integer) As Boolean With game IsVisible = ActiveSheet.Shapes(.img(r, c)).Visible End With End Function ' IsVisible Thanks, Miguel Tag: Help with match function, i need to tally Tag: 980921
  - 20
    - Automatically Change Row Height Based on Cell Contents? Is there a way to change the row height based on the contents of a cell? Here's what I've done: 1. I have a worksheet with about 6,000 or so lines, containing data for over 100 departments. 2. I've created Subtotals, and used Ron DeBruin's code to create separate worksheets for each department. 3. The subtotals came over to the individual worksheets just fine. Now I want to change the row height of the rows that contain the subtotals to 25, so the worksheets are easier to read. Each of the subtotal rows has the word "Total" included in the entry in column B. Is there a way to automate this task? Thanks, Sam. Tag: Help with match function, i need to tally Tag: 980918
  - 21
    - Adding a divider to an add-in using vba in excel 2007 I have done some research and thought that I had this figured out, but it is not giving me the result that I am looking for. I am trying to add a divider to a drop down list between different macros. I thought this was done by the BeginGroup property, but it is not adding the divider. I have posted a section of my code below. Can anyone tell me if I am going about this wrong? Any help would be great. Thanks, Jason Set cmdBar = Application.CommandBars("Worksheet Menu Bar") Set cmdBarMenu = cmdBar.Controls.Add(Type:=msoControlPopup, temporary:=True) With cmdBarMenu .Caption = "Tools" .Tag = "JJT" End With Set cmdBarMenuItem = cmdBarMenu.Controls.Add(Type:=msoControlButton) With cmdBarMenuItem .Caption = "Merge" .OnAction = "MergeCells" .Tag = "JJT" .FaceId = 798 End With cmdBarMenu.BeginGroup = True Set cmdBarMenuItem = cmdBarMenu.Controls.Add With cmdBarMenuItem .Caption = "Unmerge" .OnAction = "UnmergeCells" .Tag = "JJT" .FaceId = 800 End With Tag: Help with match function, i need to tally Tag: 980917
  - 22
    - Problem in excel - possible MACRO solution ? I got this file with the trivia questions and answers. The sheet in excel looks something like this : A column : Question 1 a) Answer 1 b) Answer 2 c) Answer 3 d) Answer 4 Question 2 a) Answer 1 b) Answer 2 ... B column : + mark in the row where the answer to the question is right. My question is : can I make a new sheet with the question numbers and the right answers only ? It should look something like this : 1 a 2 b 3 d 4 c 5 d ... Ty all in advance ! Marko Tag: Help with match function, i need to tally Tag: 980913
  - 23
    - Counting and adding values int two different columns I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel Tag: Help with match function, i need to tally Tag: 980907
  - 24
    - Adding items in one row if criteria in another column is met. I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel Tag: Help with match function, i need to tally Tag: 980905
  - 25
    - Warning message while macro runs Is there a way I can display a message while a macro runs in the background? I want to let users know to wait until the macro finishes. I tried the msgbox command but the macro will not run until you press ok and I want it to stay open until the macro finishes. Any ideas? Tag: Help with match function, i need to tally Tag: 980900
- Next
  - 1
    - Option Button FaceID in a custom commandbar I have created a custom command bar, and inserted a pair of option buttons from the forms toolbox. They link to Macros in my code exactly as you would expect. The problem is that I can't figure out how to change the filled dot to an empty dot, ie how to indicate a deselected option. The controls has a .Status property which can be set to msoButtonUp and msoButtonDown, but that isn't actually what I want. I have looked through all 5000 FaceIDs looking for an empty option button, but can't find it (the idea being to toggle the control FaceID through 2 different values). I am beginning to think that it's done some other way, but none of the properties look likely, and the online help seems to be silent on the subject. I have tried Google to no avail too. I can make do with toggling between FaceIDs 1086 and 1087 (green tick, red cross) but that isn't quite the emphasis I want to place on the options. I tried putting a Forms Toolbox option button in the worksheet and looking at it's FaceID, but couldn't even find a FaceID in the watch window. , so that was a dead-end too. What am I missing? Tag: Help with match function, i need to tally Tag: 980899
  - 2
    - Sum Column Y at first blank-bottom cell. The code below fails on this line: sumRng = ActiveSheet.Range("Y2:Y" & lastYRow) The balloon in the VBE says sumRng =Nothing What am I doing wrong? Sub Math() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet Dim myA As Range Dim lastYRow As Long, sumRng As Range, Tot As Double For Each sh In Worksheets If (sh.Name) <> "Sheet1" Then sh.Activate Rows("1:1").Select Selection.Font.Bold = True lastRow = Cells(Rows.Count, "F").End(xlUp).Row For Each c In Range("F2:F" & lastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=RC[-17]*RC[-2]" End If Next c lastYRow = Cells(Rows.Count, "Y").End(xlUp).Row sumRng = ActiveSheet.Range("Y2:Y" & lastYRow) Tot = Application.WorksheetFunction.Sum(sumRng) MsgBox Tot ActiveSheet.Rows(lastRow + 1) = Tot Rows("2:2").Select Selection.Delete Shift:=xlUp End If Next sh Sheets("Sheet1").Select End Sub Regards, Ryan--- -- RyGuy Tag: Help with match function, i need to tally Tag: 980897
  - 3
    - dual zero conditional row deletion I want to set up a condition where a row is deleted ONLY if BOTH cells equal zero. Currently the macro below delete the row when the first cell = zero. Thus when there is something in the second cell and the first cell is zero the row is deleted. The code is below if someone could assist I would be greatful. ' Sheets("Report").Select Cells.Select Selection.Copy Sheets.Add Cells.Select ActiveSheet.Paste Range("D11:H120").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Dim DeleteFlag As Boolean DeleteFlag = True While DeleteFlag = True DeleteFlag = False Range("A11:H120").Select For Each rw In Selection.Rows If rw.Cells(1, 4).Value = 0 And rw.Cells(1, 9) = 0 And IsNumeric(rw.Cells(1, 1).Value) And Not IsEmpty(rw.Cells(1, 1)) Then rw.Delete DeleteFlag = True 'rw.Cells(1, 10).Value = "Zero" End If Next rw Wend Application.CutCopyMode = False Range("D16").Select ActiveWindow.Zoom = 85 Range("A3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Name = Right(Trim(Selection.Value), 5) Application.CutCopyMode = False Range("A3").Select Sheets("Macro1").Select Range("C2").Select Selection.Delete Shift:=xlUp Sheets("Report").Select Range("A3").Select ActiveCell.FormulaR1C1 = "=Macro1!R[-1]C[2]" Range("A3").Select Sheets("Data").Select Range("D4:E88").Select Selection.ClearContents End Sub Tag: Help with match function, i need to tally Tag: 980896
  - 4
    - stuffing japanese text into a variable I have a spreadsheet that has Japanese names in a range of cells. I would like to stuff the Japanese into a variable and move the contents of the variable -- call it NAME -- to another application. Whenever I try to assign the japanese text to NAME, I generate an error -- run- time error 1004. I do not believe the text is unicode. I have dimmed NAME as string. Should I change it to another data type, if so, which one? Is there a way to convert my japanese text to japanese unicode? as always, thanks in advance ... Tag: Help with match function, i need to tally Tag: 980892
  - 5
    - Pivot table not recognizing same field values I created a pivot table in Excel 2003 that uses five field list on a sheet in the same workbook. The fields are Promotion, Paper, Amt, Month, and PromoCategory. The pivot table is returning multiple records for what appear to be identical values in the PromoCategory field. I consolidated data from January through March from a 70-column wide sheet that staff had been using. For that period, and one record in the pivot is returned for the January through March data and another for the newly entered April data. The early data has no value in the promotion field (which is not used in the pivot). The April data--but not the early data--uses a vLookup function to populate the PromoCategory field from the Promotion value. I can group the pivot table records where the PromoCategory produces two rows, but it is time consuming and the enduser is not ready to do this each month. And even when grouped, the final report, which uses GetPivotData, can't seem to find the newly grouped data. Tag: Help with match function, i need to tally Tag: 980891
  - 6
    - Find & Copy Files Listed in Excel Worksheet ? I have a list of filenames (complete with file extensions) in a single column in an Excel Worksheet. All of these listed files are in the same folder (along with many other files) on the hard drive. Each filename in the list is hyperlinked to the actual filespec (drive, folder, filename). The list may contain several hundred filenames. How do I read the list of filenames, find the actual files, and copy them all at once into a separate folder on the hard drive? I can manually search with Win Explorer and select each file using Ctrl-Click & mass copy using Paste, but its very laborious. Is there a commercial software product to accomplish this job? I'm using Win Vista Home Premium & Excel 2007. Also have XP Pro & Excel 2003 available. Tag: Help with match function, i need to tally Tag: 980888
  - 7
    - Find and Replace Question If I have a column that has both numbers and words is there a way to replace the words with an alternate value? For example the column may read: 123456 1234567 adam 4569 eve 111-111 If I were to sort the above column the "adam", "eve", and "111-111" would sort as words and not numbers obviously. I want to write a macro to look at the column and take all of the word entries, ignore the numbers and replace with another word, in this case "Check". When finished the above would read: 123456 1234567 Check 4569 Check Check I found some references here about isnumeric and istext and variations of this but I was not able to make it work for what I was attempting. Thanks for any help. Tag: Help with match function, i need to tally Tag: 980874
  - 8
    - Runs once and only once The code below, slightly modified, is from Ron de Bruin's site. It seems to work fine the first time, but that's it. I start with only one sheet (contains all summary data)and then urn it -- everything is fine. On subsequent runs, with all sheets deleted except for the summary sheet, it fails, usually here: On Error Resume Next Or here: Err.Clear Or here: Application.CutCopyMode = False It must be some kind of memory issue, but I can't figure out what, exactly. Can someone think of a resolution. Sub Copy_To_Worksheets_2() ' This sub uses the functions LastRow and SheetExists Dim CalcMode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Dim DestRange As Range Dim FieldNum As Integer Dim Lr As Long Dim KeyCol As Integer 'Dim sh As Worksheet KeyCol = InputBox("What column #? Choose 6, or 11, or 16") 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = ws1.Range("A1:X" & Rows.Count) 'Set Field number of the filter column 'This example filters on the first field in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = KeyCol With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ' Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 rng.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a worksheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) If SheetExists(cell.Value) = False Then Set WSNew = Sheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number > 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If On Error GoTo 0 Set DestRange = WSNew.Range("A1") Else Set WSNew = Sheets(cell.Text) Lr = LastRow(WSNew) Set DestRange = WSNew.Range("A" & Lr + 1) End If 'Firstly, remove the AutoFilter ws1.AutoFilterMode = False 'Filter the range rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value 'Copy the visible data and use PasteSpecial to paste to the worksheet ws1.AutoFilter.Range.Copy With DestRange '.Parent.Select ' Paste:=8 will copy the columnwidth in Excel 2000 and higher '.PasteSpecial Paste:=8 .PasteSpecial xlPasteValues '.PasteSpecial xlPasteFormats 'Application.CutCopyMode = False '.Select End With ' Delete the header row if you copy to a existing worksheet If Lr > 0 Then WSNew.Range("A" & Lr + 1).EntireRow.Delete 'Close AutoFilter ws1.AutoFilterMode = False Lr = 0 Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function SheetExists(SName As String, _ Optional ByVal WB As Workbook) As Boolean 'Chip Pearson On Error Resume Next If WB Is Nothing Then Set WB = ThisWorkbook SheetExists = CBool(Len(WB.Sheets(SName).Name)) End Function Regards, Ryan--- -- RyGuy Tag: Help with match function, i need to tally Tag: 980870
  - 9
    - Entering a formula I'm having a problem entering this <<Cells(r, sdC) = Cells(r - 1, tsC) * sdFactor>> as a formula in a cell. So far my efforts have produced <<Cells(r, sdC).Formula = "=R[-1]C[4]*sdFactor">> which fails apparently because of the variable "sdFactor". This factor is obtained by preceeding code from a different sheet. Can someone help me, please. Tag: Help with match function, i need to tally Tag: 980866
  - 10
    - Copy past function on cell containing selective formula Hi, I am extensive user of Hyperion Smart view. Usuualy I sent out report after converting them into value as most of the recipeint do not have access to Hyperion server. This make reports un-editable as all formula e.g. sum, average etc are also convereted to values. Any idea as to how to write a code which converts only cells with HSGETformula to value and leave others alone? Thanks Tag: Help with match function, i need to tally Tag: 980854
  - 11
    - closing a variable file name with macros I'm using Excel 2003. I have created a separate workbook from a worksheet and saved it to my hard drive. I am using the current date as part of the file name so that it changes daily. All this works fine, but I can't close the file after I save it. This is what I have now: Dim strappend As String Dim strpath As String Dim str3 As String Dim str4 As String strappend = Format(Date, "mmddyyyy") strpath = "c:\field tickets\" str3 = ActiveSheet.Range("p1") fsavename = strpath & strappend & str3 & ".xls" If Dir(fsavename) <> "" Then fsavename = strpath & strappend & str3 & "a.xls" End If ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename Workbooks("fsavename").Close False This last line is the problem. How do I close this file? All help will be appreciated. Tag: Help with match function, i need to tally Tag: 980853
  - 12
    - I need a way to pull a list based on criteria a user inputs. I work in a budget shop and I need to create a parameter based list on user input. I need a way to create a list based on they type of fund they select. For example, if the user selects 001: General Fund, they see a list of appropriations associated with this fund. Data Example: Fund Appn 001: General Fund 011: Salaries and Expenses - FY 1 001: General Fund 012: Salaries and Expenses - FY 2 001: General Fund 031: Grays Harbor Coastal Erosion-FY1 001: General Fund 032: Grays Harbor Co Coastal Erosion-FY 2 02V: Public Safety and Education Account 071: Washington Technology Center-FY1 02V: Public Safety and Education Account 072: Washington Technology Center-FY2 02V: Public Safety and Education Account 1H1: Food Distribution Program-FY1 02V: Public Safety and Education Account 1H2: Food Distribution Program-FY2 02V: Public Safety and Education Account 1P1: Community Serv Block Grant Prog-FY1 Tag: Help with match function, i need to tally Tag: 980848
  - 13
    - Refer to Column with a variable Hi. I would like very much to figure out how to refer to a column using a variable. The A1 notation does not seem to be conducive to this, and I can't figure out how to work the syntax. Basically, I'm creating a macro that will create a data sheet for me. I enter the number of subjects I have and it creates that many rows. Then I enter how many trials and it creates that many columns. So for each row, I want to insert a formula that averages the values for each subject. For instance, if my data starts in column E and I have 5 trials, I want column J to be the average of E - I. But depending on how many trials I have, this formula will differ. Sometimes it will be 6 trials, so column K will be the average of E - J, and so on. I have loops that iterate through the columns and rows to set up my headings, etc. But I can't figure out how to insert the formula that will calculate a range using a variable name (which is used to iterate through the loops, hold the current row number, etc). Could someone please provide some sample code that shows me how to calculate an average using a range selected by variable names and not A1 notation? I would very much appreciate that. Thanks in advance. Tag: Help with match function, i need to tally Tag: 980846
  - 14
    - select a list, then sort #2 I have a presorted Excel worksheet, & I'm trying to sort only the top part of it from where I end up after the 1st sort (the 1st blank line after the top part of the worksheet, but in tht middle of it[a daily variable starting point]). This recorded macro & my attempt to get it under my control, sorts the whole worksheet & gives me an error 1004 @ Range("J3:J", colA): Sub SortJtop() Dim Blank As Double Blank = ActiveCell ' Grab the row # of the starting (blank) cell ActiveCell.Sort Key1:=Range("J3:J", colA), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub To clarify, I need to sort from row 3 to row x (a daily variable #, not the end of the worksheet) in column J. sutibusan -- sutibusan Tag: Help with match function, i need to tally Tag: 980843
  - 15
    - tabs by dates Not sure that this is possible by simply recording a macro. Is there a way to code this by visual basic? I have a spreadsheet that has a request date column...I am looking to create a macro that creates a tab for each year, and then takes the information in the spreadsheet and breaks it out by tab (e.g taking a workbook with one tab for years 2003-2008, and making a workbook with six tabs showing information by year) Tag: Help with match function, i need to tally Tag: 980842
  - 16
    - Range to array excluding null values Hello, Is there a way to create an array from a range with the formula (varArray = RangeAddress) but excluding empty cells in the range (ot any value) It can be done by looping, but I am looking for the quick formula way Thanks avi Tag: Help with match function, i need to tally Tag: 980838
  - 17
    - locating excel file I have a search form in Access, that creates a query and than will Export to excel once useer selects path: excel 3, excel 4, excel 7 etc. I am not able to see the file. I have edited the export code to show a message if error, and no error messages shows.. The process continues and ask me to name file and etc. I did and name the file test4 and saved it to my desktop. Not seeing on my desktop I ran a search on my computer and the file name showed as test4. xls, size: 1kb, type: shortcut. when i then click on the file it says: Windows is searching for "test4.xls" To locate click browse.. And after the search it says "The item test4.xls that this shortcut refers to has been changed or moved so this shortcut will no longer work properly. Do you want to delete this shortcut?" where is the file going to??? thanks misschanda -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1 Tag: Help with match function, i need to tally Tag: 980832
  - 18
    - If sh.Name <> Sheet1 Then I am trying to get the below macro to perform a certain operation on a series of worksheets in my workbook, but I don't want anything done to Sheet1. For some reason, the code only performs the operation on Sheet1. This is the opposite of what I want. Can someone please help out? Sub Math() Dim LastRow As Long Dim c As Variant Dim sh As Worksheet For Each sh In Worksheets If (sh.Name) <> "Sheet1" Then Rows("1:1").Select Selection.Font.Bold = True LastRow = Cells(Rows.Count, "F").End(xlUp).Row For Each c In Range("F2:F" & LastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=RC[-17]*RC[-2]" End If Next c End If Next sh End Sub Regards, Ryan--- -- RyGuy Tag: Help with match function, i need to tally Tag: 980830
  - 19
    - Macro to insert repeat comments I need to create macros that will enable me to insert repeat comments boxes in multiple cells. There are a number of different comments boxes I'll need, all single words: Spanish French Italian etc Any advice on how to do this much appreciated, I've never used macros before. Tag: Help with match function, i need to tally Tag: 980825
  - 20
    - Remove On Activate Sheet Event I have an Excel workbook that I inherited that wants to run an event every time I activate or deactivate a sheet. I cannot seem to locate the worksheet events so that I can disable this. There are two subroutines with names like "OnActivateSheetName" and "OnDeactivateSheetName". If I simply remove these routines I get a message that says that these subroutines cannot be found, whenever I activate (or deactivate) the sheet. How do I remove this "event" requirement? Thanks, Rick Tag: Help with match function, i need to tally Tag: 980820
  - 21
    - open Workbook path Dear All; I'd like to list all open workbooks from my VC application. My first step was calling GetActiveObject; it returns a pointer to IUnknown interface for Excel Application. Now I can get the names of all loaded workbooks but I also need their paths (Excel10 on Windows XP). Thanks. Boba. Tag: Help with match function, i need to tally Tag: 980819
  - 22
    - Change Save Dir in IE (VBA) Hi, I need some help with automating Internet Explorer using VBA. I have an application to runs a report on our Internet based system and which can be downloaded as a csv file. In order to save this file into the correct location I need to change the directory that intintially pops up in the 'Save As' dialog box. This defaults to the last location used. Can anyone please suggest a way of doing this? For example, in Excel I would use the ChDir statement, is there a similare statement for IE? Any help would be much appreciated! Dave Tag: Help with match function, i need to tally Tag: 980818
  - 23
    - Programmatically call a menu item from a menu from a Add-in I have the Hyperion Smart View add-in in my Excel. I tried to add a command button which basically will call a command item on this Hyperion menu. When I used the Application.CommandBars("Hyperion").Controls("Member Select...").Execute in the VBA code, it does not work. The error message is "Runtime error 5: invalid procedure call or argument". If I call an Excel standard menu item, it works fine. Does anyone know how to programmatically call a non-Excel standard menu item that comes from a third-party add-in? Thanks a lot in advance! Tag: Help with match function, i need to tally Tag: 980815
  - 24
    - Analysis Toolpak Loaded or Not I have a template that uses the workday function that the user runs daily; the analysis toolpak is loaded. Today there is a #NAME error due to excel not recognizing the toolpak. Checking the addins indicates it is "loaded" but when deselecting, closing excel, starting excel and selecting Analysis Toolpak and Analysis Toolpak VBA again, it recognizes the addin and the workday function. I've added coding to load the addin when the workbook opens but have no idea what caused the problem between yesterday and today. Any ideas? -- MacGuy Tag: Help with match function, i need to tally Tag: 980810
  - 25
    - Advanced Filter problems using VBA in XL2007 I used to think I was OK in VBA, but now I'm not so sure ... I have a sheet within which I have a list linked to a MOSS list. (The file was created in XL2003 and I'm using XL2007, so the synchronization functionality is working just fine) I have another sheet on which I wish to filter selected records from the MOSS-linked list. The following code is called from the 'receiving' sheet: Sub Advanced_Filter_Due_To_Complete_Projects() Sheets("Projects Due For Completion").Activate Range("H2").Value = ">=" & Format(Range("To_Complete_Date_1").Value,"dd/mm/yyyy") Range("I2").Value = "<=" & Format(Range("To_Complete_Date_2").Value,"dd/mm/yyyy") ActiveSheet.Range(Range("B5").End(xlDown), Range("B5").End(xlToRight).Offset(1, 0)).ClearContents Range("'MOSS Projects In Progress'! Projects_In_Progress").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=Range("H1:I2"), CopyToRange:=Range("B5:E5"), Unique:=False End Sub The 'destination' sheet is 'Projects Due For Completion' The 'source' sheet is 'MOSS Projects In Progress' The MOSS-linked list is named 'Projects_In_Progress'. If I record a macro whilst performing the filter, obviously everything works just fine. Running the code afterwards though produces no result at all. I've tried qualifying the ranges in a variety of different ways (sheets(...).range(...), range(...) etc.), but no joy. I've also changed the criteria from being calculated as a formula to being created as a string value and entered (as in the code above). I read on another thread that it may be something to do with string manipulation of date fields by XL? (the format on the source sheet list is 'dd/mm/yyyy' as in the code above) I can only guess as to why its not working - maybe something to do with the list/'name' in XL2007? I have tried creating a dynamic range name for the list "MOSS_Projects_In_Progress" referring to the list just in case that's it, but that doesn't work either. Basically, I'm stumped and can't see the solution. No doubt its something obvious and I'll kick myself once its pointed out to me. Can anyone help shed any light on this please? Thanks, Rob Tag: Help with match function, i need to tally Tag: 980806

Ok, I have thousands of rows of data. One of the columns is titled "age" and
each row has a different age. Ranging from 15-100. I need to tally these
rows into age group, 0-18, 19-30, 30-50 etc etc.

Is there a code i can run that will tally these for me?

Thanks,

-Adam

Top

RE: Help with match function, i need to tally by GarysStudent

GarysStudent
Thu May 08 08:45:00 CDT 2008

Let's say the data is in column A and the groups are:

15 - 30
31 - 77
78 - 100

In B1 thru B3 enter:

=COUNTIF(A:A,"<31")
=COUNTIF(A:A,"<78")-B1
=COUNT(A:A)-B1-B2

adapt these formula to match your specific groupings.
--
Gary''s Student - gsnu200784

"Adam" wrote:

> Ok, I have thousands of rows of data. One of the columns is titled "age" and
> each row has a different age. Ranging from 15-100. I need to tally these
> rows into age group, 0-18, 19-30, 30-50 etc etc.
>
> Is there a code i can run that will tally these for me?
>
> Thanks,
>
> -Adam

Top