I have a string containing the addresses of a range of rows.
an possible example: "7:62,63:168,169:270,310:318,319:345,346:350,351:355,360:366"
i would like a sub/ function that will somehow be able to combine/reduce this down to:
"7:270, 310:355,360:366"
would anyone have any good ideas as to how this can be achieved?
I have a problem where I extended a formula down to over 40,000 records which has increased the file size substantially. I only need it to scroll down to a few thousand rows now that I realized that there is alot less data to populate the worksheet. Is there any way to get it back to a scroll range that is more modest in size?
View 1 Replies View Relatedhow do return the cell address of a certain string?
I'm seraching in a named range called "positions" for the string "GK"?
I have the following string of text and need to find a way to extract the address and city (separated)
Paste!A2 = 000-0000-0000 12-Mar-13 0.00 12-Mar-13 100.00- J Doe 1111 Excel Rd Vancouver BC 000.00 W
Paste!A3 = 000-0000-0000 12-Mar-13 0.00 12-Mar-13 100.00- J Doe 111-2222 Random Ave Calgary AB 000.00 W
The spaces separating the name address and City do change so i have not been successful using a simple Mid/right/left Find formula. Also the street addresses can include a unit # as shown in the text string above. I am honestly at a loss
End results would of course be:
G2 = 1111 Excel Rd
G3 = 111-2222 Random Ave
H2 = Vancouver BC
H3 = Calgary AB
Hey guys i have the following problem. This is what i have:
View 6 Replies View RelatedI have a whole host of email adresses
My issue is that in cell A1 i would have the name of the contact (John Smith)
In cell A2 i would then have the email adrress of the contact though it starts as E-Mail: JohnSmith@hotmail.com
What i would like to do is to extract the e mail address of each contact
Another issue is that there may be some blanks so there may be a name of a contact(A2) and then his email address(A3) then a few blank cells so cells A4 and A5 are empty then another address
I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
I have address that sits in one cell only and I need to separate into two or three columns (streetname, street number, streetletter).
Is this possible as the length of street text, numbers and letter differs all the time...
Attached is a sample file with before and after result
address.xls‎
Excel 2007.I'm looking to extract parts of an address into different columns, I've already extract the Country, Post Code & Town. But what i'm having trouble doing is extract the street address and county, the reason I'm having trouble is because not every address has a county.
Below is what I have so far:
I need to extract Street (which is in red) into the Street Column
County (which is highthlighted in dark blue) into county
Excel 2010
A
B
C
D
E
F
[code].....
I recieve a spreadsheet each month with the following column of data:
9 Paddington House
PO Box 73
Whiteleys of Bayswater
9-10 Grove Road
134/138 Drymen Road
44-46 The High Street
1 Midland Road
48 Donegall Place
As you can see, it consists of addresses, each line is a separate address. I need it to be converted so that the data is split into the following 5 fields:
Street number from
street number letter from
street number to
street number letter to
street
If there is no street number , then it should just enter the data into the street field. If there is a range such as 44-46, it should enter the 2 numbers into the from and to fields. Sometimes this is in the format 44/46. Also, sometimes there are letters, eg 44a-44b, in which case each section (ie 44, a, 44, b) needs to be in a spearate field.
I would like to "reverse concatenate" an address text string as follows: ....
View 9 Replies View RelatedI have something like the following:
If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then
..do code (I know the cell location of interest at this point)
End If
Then I End up needing To Do something Like the following
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("RawData").Range("L1:N1")
“L1:N1” above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.
to make the user experience better by inserting the " Stock Search" sheet to speed up the process. The spreadsheet attached is a simplified version of the current version, where it would normally have many many more sites, areas and models of cars. The existing process means the user has to trawl through the sheet where they are only interested in what sites in their area has stock. I can not change the format of the "Red" and "Yellow" sheets so hoping to insert another sheet to do the work.
I am hoping to have on the "Stock Search" sheet a few drop down boxes.
The user will select
1. Area
2. Type of Colour (the type of colour selected should link with the sheet name in effect, so only stock within the "Red" sheet will be shown if the "Red" colour is selected from the drop down box)
3. Model of Car
This will then give the resulting Store/Stores with stock (value 1 or above) with those conditions met which is showing in the relevant sheet
So as per the attached sheet, if a user selected Area "South West", "Red", "Renault Megane"
then SO10 Clifton 1 and SO15 Yeovil 3 would be shown in a list.
Attached is a file which shows the problem. I bascially have two headings. The first heading contains the letter "b" 9 times but only has values against two "b"s. When this is entered in a pivot table and the value is drilled down (in this case 3, highlighted in yellow), I only want the numbers attached to the two "b"s to be shown rather than all the 9 b's (thereby omitted all the zeros).
View 3 Replies View RelatedI have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A
Row2 56
Row3 44
Row4 65
AllData is a named range that refers to the range A2:A65536
AllData_UsedRange refers to A2:A4 by way of this formula.
=OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work...
ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address
Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).
In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70
I'm looking for a way to be able to use this typed range as the range for some chart data.
I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?
View 4 Replies View RelatedI am attempting to categorize IP Addresses. My lookup table looks like this:
Begin End Category
1.50.50.0 1.50.50.255 3
5.185.40.0 5.185.43.0 2
5.22.22.65 5.22.22.128 5
I have a list of IP addresses that need to be categorized.
IP Address Category
5.185.41.15 2
1.128.128.0 Null
1.50.50.128 3
The category column needs to be populated. The ranges in my look up table are not end to end (there are gaps).
Function Lookup got close, but assumed there were no gaps.
I have a list of random I.P. Addresses i.e. 192.168.1.0, 172.19.16.0 etc
i would like to copy them into another cell but change them to a range. i.e.
192.168.0.1-192.168.1.254
172.19.16.0-172.19.16.254
I am trying to get the addresses of different cells containing numbers. This addresses should be ranked by the size of the value from largest to smallest.
Currently I am using this formula
=CELL("address";OFFSET($A$2;MATCH(LARGE($A$2:$A$10;1);$A$2:$A$10;0)-1;0))
and just increasing the variable of the LARGE function for every rank.
This works fine up to the point some numbers are the same. If I have for example two cells containing a 80 and 80 is the largest value, rank 1 and 2 result in the same address (of the first cell containing the 80). But I want the formula to result in specific addresses for every number.
I thought about using MAX instead of LARGE, but I don't get a working solution for the second (third, fourth, ...) largest value.
I have a named range, called 'event' relating to cells a2-a200, I have a named cell, 'eventcounter'. I was hoping that I would be able to use the named cell eventcounter in formulae on the cells in the event range rather than having to use the cell address. So instead of having a formula that was something like this, =if(a3= etc etc.
I could refer to the range event and eventcounter. Eventcouter gets its value simply by counting the number of non blank cells in the event range, so consequently - I can only figure out how to address the last entry in the event range in the manner that i wish.
I have to macros I'm working with the first take a user set range and prints the . Address to a cell.
The second Macro I'm trying to get to look at that cell and pull out the range. So is there an opposite fuction to .
Address that will convert excel format to a VBA Range format. Ex A cell with $A$1:$A$2 to Range("A1",A2")
Macro 1
Code:
For j = 1 To x
c = 1
For i = 1 To ws.Range("AD" & rc).Value
ws.Range("AE" & rc).Value = Application.InputBox
(Prompt:="What is the Heading of Data set #" & c & " Table " & tc & " This entry may repeat", Type:=8)
[Code] .........
Macro 2
Code:
'A lot of code here but I want to be able to set multiple ranges to the ranges list in the cell values.
rng(2) = sh(0).Range("AG2").Value
I am having troubles with using two methods together. I know how to find the address of the first or last occurance of a MAX in a range. I also know how to create a MAXIF function using criteria to determine what to consider for the max calculation. I do not know how to return the address of the result from the MAXIF.
I am using Office 2003 on WinXP.
Ultimately I want to take the address of the first result of a MAXIF and use it to do a lookup for a corresponding value. I'm not sure if I will be able to do this with a combination of functions, or if I will need a macro.
Heres an example:
Data:
Time WeekNum Count
01:02 1 12
02:05 1 35
03:09 2 456
14:20 2 123
12:24 2 122
Usage:
Use the maxif lookup to return the time of the largest count for WeekNum 2
Result:
03:09
I have a variable which contains the column number. How do I then reference a range in VBA using that column number, as opposed to the letter? I know how to convert the number into the letter, but I also know there has to be something more simpler. For instance, a form of the ADDRESS function, but in VBA?
View 9 Replies View RelatedI have this simple requirement
Business ProcessBp1Bp2Bp3Bs. Consulting
The Bs. consulting can take any values from business process like Bp1 or Bp1 or Bp2...
for this i have written validation macro
If Target.Address = "$C$21" Or Target.Address = "$D$21" Or Target.Address = "$E$21" Or Target.Address = "$F$21" Or Target.Address = "$G$21" Or Target.Address = "$H$21" Then
do the validation;
end if;
this works fine, but i need to use or condition , is there any better way to write this, a more compact way,
I need to select a variable number of cells on a line. I know Range("A2:c2") will do exactly what I need EXCEPT i need to use variables to do it. Is this possible, and if so how.
View 5 Replies View Relatedit seems that the string range.address is not always equal to the complete range address.
I mean, I noticed that, if rg is a range,
Dim rg As range
...
rg<>Range(rg.address)
In the cases I noticed it, the range was an union of many ranges, and the string Address was already big, but not 1 kBite long.
My question: is there a way to get the *complete* range address, without this restriction?
I would like to use the address property, because I would like to have my range without sheet information.
So I use often : rg=Range(rg.address)
I want to define the range from a given cell and all data below within the same column strDataStart is the named cell in the worksheet that want to start from. Below is what I tried and failed.
Function rngDataCol(strDataStart As String) As Range
Dim rngDataEnd As Range
rngDataEnd = Range("strDataStart").End(xlDown)
rngDataCol = Range(Range("strDataStart").Address, rngDataEnd.Address)
End Function
I am trying to create a correlation matrix that uses the =ADDRESS function to define the various columns of data which I want to correlate against each other (I require this flexibility because the number of columns and rows of data to be correlated will change). The basic formula I am using is something like the following: =CORREL((ADDRESS(Sheet1!$C$2+3,L2+2,,,"Sheet1")),(ADDRESS(Sheet1!$B$2,L2+2,,,"Sheet1")))
However, while the individual ADDRESS fomulas deliver the correct start and end cell references required, it seems that CORREL function won't accept the cell addresses that result from using the =ADDRESS function.
get the address of the last range copied to the clipboard? I tinkered with GetFromClipboard but I would like to find a solution that is not dependent upon DataObjects if possible. Since copied cells are "marqueed" in Excel, is there a property relating to marqueed cells address?
View 8 Replies View Related