I have a spreadsheet that has a contents page on, with a list of words as hyperlinks, that link to that same word on another sheet. However when I insert rows, the hyperlinks to the words below where I have added a row go out of sync
Is there anyway that I can link the hyperlink to a cell that contains certain text, so that it will follow it around?
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies View RelatedI want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies View RelatedI have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies View RelatedI have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.
Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
Looking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
Name Spreadsheet Ex.xlsx
This formula counts the number of spaces between words in a cell:
View 5 Replies View RelatedI have hyperlinks betwene one worksheet to another - they only only hyperlinked thourh column & rows (eg: A100) etc. How can I lock these hyperlinks but still allow users to insert new rows without losing their place?
View 2 Replies View RelatedI am having a slight problem copying hyperlinks. I have written some code that sorts data by date and then creates a simple diary. It originally placed the name of the event in the new diary sheet. What I would like to do is instead of copying the name into this new sheet copy a hyperlink that I have created to the company's website for that event.
I can create the hyperlink using hyperlink(B1,A1) where A1 is the company name and B1 is the web address but since the new sheet will not have the underlying data I need to actually copy the values and format of the hyperlink rather than the formula.
I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.
Example given below and also attached the file for your reference.
New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC
[Code]....
I have a column that is a list of addresses. For example:
I need a formula to separate out the last words (city and state) from the rest of the address and put into a different cell.
I have 1 or 2 maybe 3 words in a cell (difference words). I need to count how many of the same words are in the cell and place the number of words another cell I have included a file the same word may appear in other cells in same column which has to be counted into the same cell
View 2 Replies View RelatedI have a cell containing text like this
Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685*
I want to separate them into different cell though which every word or number contain his own cell
like
A1 = Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685
then i want it in this way
B1 C1 D1 E1 F1 G1
Ph(Off): 5754112-5750441 Cell: 0300-8406693 Fax(Off): 5712685
How do I write a macro to extract the last two words of a cell?
View 10 Replies View RelatedI have a problem to count some words in a column: in fact, in the cell they can write several words and my objective is to count in this column how many times there is the word "info" but as they can write several words in the same cell,(for example: speciality, info, degree) I can t really count how many times the word info is written in the column.
View 13 Replies View RelatedI am trying search & count 2 or more words within a cell using a worksheet function. See below example:
The texts are in A1 (only)
Entered by John at 02:19 PM on 02/13/2007
Entered by Smith at 02:20 PM on 02/13/2007
Entered by John at 02:30 PM on 02/13/2007
Entered by John at 02:21 PM on 02/12/2007
Entered by John at 02:22 PM on 02/11/2007
The words I am looking for are "John" and "02/13/2007" and the count should be 2. I bolded what should be counted.
I've a column which contains data in below format
alves, martine
burraq, joys
ande, kallis
I want to know the LEN of words before the comma, which would be as in this case
5
6
4
I need a formula for this.
I have many cells that have full names in them. They are currently formatted as follows; last name, first name, middle name. I am trying to extract only the last name and first name (keeping the comma in between them) and drop the middle name.
View 9 Replies View RelatedI have a data on a cell that says "Member: Name of Member"
The question is how could I remove the "Member:" and come up with "Name of Member"
how do i extract the last 2 words in a cell?
ex. 8137 Plaza Amber, Ortigas Center, Pasig City
how do get just pasig city?
removing certain words/charactors in a cell.
For example, I have these information in 1 excel cell.
"Company allows staff to purchase small value items (items amounting to less than $3000) with approval from the staff's director."
So in this case, I need to remove the HTML/XML tags manually. Therefore, the final result should be,
"Company allows staff to purchase small value items (items amounting to less than $3000) with approval from the staff's director."
It is very tedious with the huge amount of data to process.
I have a list of email addresses, about 2000 rows long. they are listed like so but in 1 cell Internet EMail Address: Jo.bloggs@avivagroup.com.au This is what is exactly in the cell, except different names. What I want to be able to do is run something to delete the Internet EMail Address: part but keep the actual address. The problem is its all in the one cell as stated
Any suggestions, list goes something like this
Internet EMail Address: Jo.bloggs@avivagroup.com.au
Internet EMail Address: Larry.holt@avivagroup.com.au
Internet EMail Address: matt.blank@avivagroup.com.au
Internet EMail Address: lance.legend@avivagroup.com.au
What I am trying to do is go through a large form and remove everything except for a certain set of cells, I am using this code
For i = 10000 To 1 Step -1
Set rng = Range("A" & i)
If rng.Value <> "Employee" Or rng.value <> " Total" Then
rng.EntireRow.Delete
End If
Next i
What I want it to do is go through the sheet and delete anything that doesnt have Employee or Total in column A, but my problem is that they dont JUST say Employee or Total, its more like Employee: Team A or Employee Group 1. The Total part always says "Total of " and a number, but the number is always different. How do I make it not delete it if it has Employee or Total anywhere in the cell?
I am looking for code to look at a cell and see if it contains more than one word. So if Cell A has one word Cell B = ? If Cell A has two or more words Cell B= something else. I assume that searching for spaces would be best way to accomplish this
View 5 Replies View RelatedI have a file with 12,000 user records (one user per row). Within each row there is a cell that contains all of the product that each user sells. The product within each cell is listed between commas.
I need to Alphabetically sort the product names within each cell and have the data remain in that particular cell.
So as an example, in cell A12, I have something that looks like this:
Apples, Zucchini, Pumpkins, Beans, Squash, Corn