I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
Sheets("mySheet").Range("myRange").Name.Copy
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
the active sheet contains a number of defined named ranges. i would like to delete range names beginning with "Sales", yet the number of defined ranges changed from sheet to sheet = for example, Sheet1 may have 3 defined ranges (Sales1, Sales2, Sales 3), Sheet 2 may have 1 (Sales 1). Is it possible to included a wildcard search in the following code?
Sub DeleteRangeNames() Dim RangeName As Name For Each RangeName In Names ActiveSheet.Names(RangeName.Name).Delete Next RangeName End Sub
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I have few macros that I use to reconfigure a particular workbook into a sort of "developer mode". I keep them in a text file that I can paste them from when I'm working on it and then delete them before I send it out to other people.
I'd like to be able to script them so they only run if the workbook is open on a specific machine (namely mine). Is there any way to identify the specific computer a spreadsheet is being run on?
I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).
To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).
I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:
[Code] .........
For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc): - In condition (row) 1: "x" is a specific value only found in Group 1 - In condition 1: "y" is a specific value only found in Group 2 - In condition 2: "x" is a value found in majority in Group 1 - In condition 3: no specific value can be associated to Group 1 or 2.
What I would like to get is a measure of whether: (1) there are values over-represented in one of the groups, or 100% specific to one group. (2) what are these values (3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB: ="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
I need to calculate a formula in a constantly changing range. In the example below, the formula in column C would yield the % change in column A between the first "P" value in column B and the minimum value prior to the next "P" value.
So, the first result would be =(A2-A1)/A1, which is (1896.3274 - 1973.4764)/1973.4764. The cells in column C would be blank until the next formula, which is =(A8-A7)/A7, then it gets tricky. The next would be =(A14-A10)/A10.
A B C 1 1973.4764 P 2 1896.3274 3 1922.5499 4 1905.2061 5 1985.6797
Is there an easy way to identify range or cell addresses in a formula. I want to find the alphanumeric part of the address.
For example in the SUM Formula =Sum(A4:A230), I wish to separate out A4:A230 part. Or at least I want to remove the number part in the address and just want to get Sum(A:A).
Is there a function that can do this? I don't want to do this using usual string/text operations as it becomes a laborious process and has to consider all possible cases of range addresses.
I'm trying to create a list of missing numbers in a range, but I can't fiure out how to do it. The problem lies in that the range of numbers is in the middle of a larger number. ie. xxx-xx-0001-xx would be number 1, and xxx-xx-0500-xx would be number 500.
I need to scan multiple sheets containing these numbers, and produce a list of the numbers missing from that range.
Example:
Sheet1 has xxx-xx-0001-xx through xxx-xx-0009-xx, and xxx-xx-0018-xx through xxx-xx-0042-xx.
Sheet2 has xxx-xx-0053-xx through xxx-xx-0062-xx, and xxx-xx-0067-xx through xxx-xx-0072-xx.
Sheet3 needs to have a function that produces a list showing xxx-xx-0010-xx through xxx-xx-0017-xx, xxx-xx-0043-xx through xxx-xx-0052-xx, and xxx-xx-0063-xx through xxx-xx-0066-xx.
I need to be able to do this without VBA. The list doesn't necessarily have to have a different number per cell, it could even show them all on 1 cell if it's easier, but it would be more presentable if it was 1 number per cell in a row or column.
I want to select the cost for fuel based on the date using the select case method.
Sub FuelCostApplication() Dim Check As Integer Do While Check < 10 Select Case Range("B4").Value Case 8 / 30 / 2007 To 9 / 5 / 2007: Range("C10").Value = Range("B4").Value ActiveCell.Offset(1, 0).Select Case Else Range("C10").Value = 0 End Select Check = Check + 1 Loop End Sub
CREATE TABLES LIKE BELOW? -----------A----------- ---B---- --C-- --D-- --E-- F 1 US Air - Ground Expense 2 Fuel 3 Week1 Week2 Week3 Week4........................
I am having no success using either: 1) COUNTIF with wildcard (*) or 2) Case Statements to find text strings in a cell. I've even tried using Copy/Paste Special values to change the way Excel 2007 interprets the field.
The Do While loop looks at target cells in Col. 18 which contains one or more specific last names. I want to put a text string (also a name) in Col. 19 IF the target cell contains the last name in the COUNTIF or Case Statements. The COUNTIF normally puts a TRUE or FALSE based on the condition, but I'd like to put the the last name based on which condition is met and then continue looping to Row 3. It's a small number of rows, less than 100.
I'm looking for a way to identify the first cell of a page without using the pagebreak function: Sheet6.Range("B" & Sheet6.HPageBreaks(i).Location.Row). The reason being is for overall speed and performance as well as avoiding <out of range> errors on the last break. If I use totalPages = Sheet6.HPageBreaks.Count + 1 to determine the number of page breaks, is there a way to identify the first cell of page i in this way?
I need the peices and hours total for each name on another sheet. So I would have John on my sheet and would need to to grab and add the info from B2 & B6 into one cell (since it is the same person). I can always drag the info over for hours once I find a way to do this for the pieces I would think. The problem is that I don't want to add in B1 & B2 for John because those numbers are not a part of the total.
So is there a way or formula for one cell to look at the entire sheet and everytime it sees the name john to add the information one column over and one cell down and then give a total?
I may be able to do some formatting and have all the info I need directly to the right. So I would have (A1) John, (B1) Data, (C1) Data. The issue would be a cell finding the name, taking the information directly to the right of it and adding it as many times as the name is found on the sheet.
I have a directory that contain a large number of files. How do i preserve certain files with specific filenames while other files are deleted?
For example, I have files that are named : "58-26150B", "58-26200", "58-26200B", "58-26500B", "58-26550" etc. Please refer to uploaded screenshot.
The files that i want to keep from the above are "58-26200", "58-26200B". The code should be able to loop through the entire directory & preserve only specific files while deleting the rest.
Private Sub CommandButton18_Click()
Dim nWave As Integer, i As Integer Dim ConvergedFile As String, Pattern As String, KillFilePath As String, SimilarFile As String
nWave = Worksheets("nomogram").Cells(13, 4) For i = 1 To nWave ConvergedFile = CStr(Worksheets("nomogram").Cells(33, 3 + i)) 'Loop through list of wanted file names "58-26150", "60-25950" etc Pattern = Mid(Trim(ConvergedFile), 1, 3) 'Update the filename prefixes i.e. "58-", "60-" etc
I have a code that goes through and deletes everything that doesn't contain a certain word, but I have several words ( Names actually) that I need to keep in my sheet, but all others can be deleted, along with their rows...the names are on in Column B...there are about 11 names currently and probably be adding a few more next month...This is a macro that is set for work to go through like I said a lot of Rows and delete all them but those that have the names of the workers I need to keep in the sheet...the names are scattered throughout, which is the reason for a need of a macro to do it for me cause it takes me forever to manually do it each day...The code for just one person I can't get to do multiple names so this is what I came up with based off what I know plus reading hundreds of pages on the net regarding similar style needs, but none exactly the same....
This is the Code that I'm trying to use but unfortunately I get Type Mismtch Error 13 ( I think it's error 13)...what am I doing wrong? (I only am working with one sheet that contains all the data...
Sub SortOutMyTeam() Dim wst As Worksheet, wstNew As Worksheet Dim rData As Range, r As Range, rTotal As Range Dim avNames() As Variant avNames = Array("Name1", "Name2", "Name3", "Name4", "Name5") Set wst = ActiveSheet Set rData = Range("B1:BL" & Cells(Rows.Count, 1).End(xlUp).Row) Application. ScreenUpdating = False wst.AutoFilterMode = False With rData For i = 0 To UBound(avCrit) Step 2 . AutoFilter field:=3, Criteria1:=avNames(i), Operator:=xlOr, Criteria2:=avNames(i + 1) On Error Resume Next Set r = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells
I need a macro to identify when cells are >= 90% in a range, and count them. Then put the result in cell Z58. Range is I51,L51,O51,W51,Z51. i.e if all cells are 90% then put value of 5 in cell Z58. Here is code i have that isnt working:
How can I identify the last cell of a single column range . . . i.e. the last cell address of range C1..C15 is C15. The range will have a name of "reg".
My company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)
So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.
What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.
If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.
I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .
I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.
How can i make a best XI team from a list of weekly scores in my Fantasy Football league?
Players are as follows:-
One goalkeeper - Top scoring goalkeeper per week Four defenders - Top four scorers per week Three midfielders - Top three scorers per week Three forwards - Top three scorers per week
I need it to find the top scoring players in the above categories and then display them in a specified area.
I also need this to happen for each individual week, NOT cumulative. literally, the highest scoring XI players, as listed above, for the present week.
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.
I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!
I am trying to enter only the named ranges in the active worksheet into an Array. The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level. ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest() Dim sArray() As String Dim sJoin As String Dim y As Long
x = 1 y = ActiveSheet.Names.Count z = ActiveSheet.Name
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
