I'm trying to export to csv and then convert to address labels. I know I could do this with a macro, but I have no idea about how to write them, or even how to get it to lay out the way I want. I need to do it so I can send out all the save the dates for my wedding. I don't want to do it in word (complicated story to do with it not working with the labels I've got).
The columns are layed out like this, with example addresses:
Title,Forenames,Surname,House Name/Number,Road,Area,Town/City,County,Postcode,Country
Mr,Alan,Davies,23,Malvern Road,Sheperton,Leicester,Leicestershire,LE2 3BY,UK
,Jason,Robson,3,Wake Green Road,,Birmingham,,B13 9QD,....................
I've two columns, one being the first name and the second being the last name. Is there a way that I can convert these two columns into the correct format for the email address ? Rather than having to manually do this.
Assume cell A1 contains a name and A2 contains the text "D7".
How would the code look like that 1. reads the contense in cell A2 (which is D7), 2. converts it (D7) to a cell address and finally 3. copies the content in cell A1 to the cell address found in A2 (in this case D7)
Any function to convert a long value into an IP address. I have an extract from a mySQL dbase that has the ip addresses in long format. I need to reverse them back to IP Addresses.
I am trying to take an address book on the internet and copy/paste it into excel- not hard right? Ok, so that is done. The problem I have is that there are over 2000 addresses- and they all copy straight down into one column. I know how to transpose the information- but come on- there are over 2000. that seems like a LOT of work. So my first question would be is there a way to do this all at once? For each company/name/address/phone I need one row per company.
I have a textbox with an address (ie "$C$3"), how do it then activate the correct cell (ie C3). Flash ActionScript has a function that would do this, eval(). I know VBA has a val() function, but I think thats different all together.
I have a simlple macro to add a new customer to a list using a form. One of the columns of the datra beig added is an email address. When entering the data the email address just appears as text, but i want it to be the email address link you could click on and it opens up an outlook email. Just clicking into the cell and tabbing out changes this into the format i want, but wondering how to i get the macro to do this?
I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.
The data is currently in the format below (notice how one entry has a website while the other does not).
First Church 102 Main Street Dallas, TX 12345 email@whatever.com Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
So I'm looking for the data to be formatted like the following:
First Church 102 Main Street Dallas, TX 12345 email@whatever.com Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
I was able to find the following code from a Google search, but it can't dynamically adjust the range.
Sub x() Dim rng As Range
Set rng = Range("A1").Resize(5) Do Until IsEmpty(rng.Cells(1, 1)) rng.Copy Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True Set rng = rng.Offset(5) Loop
End Sub
I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.
I have a long list of names, address, city state zip, and phone number, followed by the next name, address, etc. I need to now be able to bring each one into it's own column.
Example: David Smith 123 Main St. Denver, CO 12345 123.456.7890 Joe Blow 345 Happy Ave. Oakland, CA 34567 567.890.1234
I need to convert it so that I would have
David Smith 123 Main St. Denver, CO 12345 123.456.7890 Joe Blow 345 Happy Ave. Oakland CA 34567 567.890.1234
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.
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1 2 3 4
Here's what each of those "mean" (I have this in another table):
1 - Sick 2 - Overtime 3 - Court 4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.
I have two columns in my worksheet that represent a graph: the A column has x values and the B column has y values. I want to be able to find the x value that corresponds to the max y value.
I am currently using: =CELL("address",INDEX(B1:B2005,MATCH(MAX(B1:B2005),B1:B2005,0)))
This function gives me the cell location of the y max, but is there a way to convert this to the corresponding x location? For example, if the above function outputs $B$1569, I want to know what the value of $A$1569 is.
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
On a worksheet called "Contact Info" column A starting in row 2 I have a list of names (variable length). In Columns B2-D I need the email address, work phone number, and cell phone number.
In Excel 2003 you could use Labels in formula by enabling the Tools-->Options-->Calculation-->Accept Labels in Formula setting. In 2007 I can only find an option for using table names in formula. Is the use of standard labels no longer valid in 2007? If it is where do you enable it?
I have a basic bar graph showing the average home prices per quarter. I know I can turn the legend on and position it to show the average price on top of each bar in the graph. I would like to add an additional legend (on this chart) showing how many homes sold per quarter but not display it as a bar. I just want the number displayed maybe on the bar or below the bar. Can I add a separate (extra) legend?
My excel file brings up a userform as soon as it is opened. The user chooses an entry from the ComboBox and then clicks OK. This brings up another userform which has a Label at the top. I want the caption of this label to show the entry from the ComboBox on the first userform. This should be straightforward, but what I've done so far doesn't work!
I've tried:
Label1.Caption = ComboBox1.Value
within UserForm_Initialize(), but it doesn't work!
I have a file with a small example of sales and % of sales for about 7 persons.
I wanted to insert a pie chart but want to show labels inside the pie chart along with % of sales and the sales person name near each part. How to do this ?
