I have hundreds of lines with unique tasks in each cell. I wanted to add 'P&C- ' to the front of each task and do not want to go through and add individually as it will take take.
Is there anything that can place in one cell and just drag to the last cell and complete?
E.g.
Current Cell: Task A
What I need: P&C- Task A
I cannot add attachments as my work seems to have a restriction on doing it and always asks for admin password
What I'm trying to do is add a line where it counts the number of cells with either the word "NEW" in a certain row (Countif) or the number of cells with a value in a range(Counta). CountA is not working because if I run the macro twice it adds a second total line and messes both the count and total up. COUNTIF I just cant get to work because im a VBA SUPER NOOB and im just pieceing code together from this forum and the net.
View 5 Replies View RelatedFor example, on cell A1 I have 123456 Total, how do I delete the word "Total"? I have abut 2000 lines. What formula do I use?
View 5 Replies View RelatedI have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document.
For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document
'or
Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I have a workbook with 2 groups of data on it side by side. Goal here is to have them line up. The problem is one side has more data then the other OR the smaller side may have data thats not in the larger side.
example:
Accnt #, Name, address Acct#, Name, address
I would like:
1234 Mike Thomas 1234 Mike Thomas
1234 Jayne Thomas 1234 Jayne Thomas
1235 Chris Beaton
1236 Susan Hamel 1236 Susan Hamel
I have an image which i have reduced to a mathematical representation of light intensities. We can consider each cell in my spreadsheet as a pixel, the value within the cell corresponding to how much light that pixel it took in during an exposure. There is a trend along the pixels that i would like to isolate as exactly as possible: a diagonal line, not quite straight from the bottom left corner to the top right.
What I would like to do, if possible, is to treat each cell in my spreadsheet as a point on a coordinate plane, and to use some type of formula or macro to give me a line of best fit using the positions of the highest valued cells as the data points in my extrapolation.
The tool I'm looking for would take data organized like this:
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 200 1 1 1
1 1 1 1 1 1 200 1 1 1 1
1 1 1 1 1 200 1 1 1 1 1
1 1 1 1 200 1 1 1 1 1 1
1 1 1 200 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
And give me an equation of the form y=1x + b (describing where to find the maximum values among the cells), although my actually data will need to be fit to a higher order polynomial.
Once I can do this, I will also need a way to take perpendicular sums automatically along the line of best fit at distance of two pixels in both directions and a width of one pixel. You can imagine drawing a rectangular box perpendicular to the line of maximum values and adding the proportions of each cell enclosed by that box. This means that for the example above, i would get 209 for all five points of interest: the maximum value plus the two values of one in the upper left perpendicular direction, the two values of one in the lower right perpendicular direction, half the values of the two cells neighboring the exactly perpendicular cells and a quarter of the outermost cells touched by our imaginary pixel box.
The following image may be useful in clarifying what I'm looking for. The red bar represents the imaginary box containing perpendicular pixel sums, where the green beam is the line of best fit derived from my maximum values.
Attachment 337419
Also, here is a snippet of my actual data to experiment with in solving these strange and exotic problems
Attachment 337420
I am trying to create a formula that will
1. count all the cells in the 15th line and tell me if there are more than 10 cels filled in a row in a 30 day cycle.
2. If it there are more than 10, to flag another cel with a code word.
3. if there is a break in the count, to remove the code word and start counting again.
I am setting up a charity accounts sheet, that will also calculate money raised from Raffles, so each raffle will have a name, I can do this by matching the amount with a description but wondered if I could calculate it by looking for specific words or reference in a cell
Sum all cells that the adjacent cell contains the reference "Raffle 0001" but not "Raffle 0002" this needs to be calculated on another cell
Sum all cells that the adjacent cell contains the reference "Rescue" this cell could contain other text as well, so could say "Rescue for Sweetie by Laura Herarty" or "Rescue for Bliss by Jane Telford" all need to be calculated.
I'm trying to write a word or more but i want it to be in different cell.
Like: this sentence "Excel Program" write it in cell a1 but it slip to the cell a2. (so in cell a1 adn a2 this sentence).
In cell A1 I would like to have a formula that indicates the number of times the word "Yellow Pages" appears in cells C10:C1000. The C column inidicates a referral source. So some of the cells will have "Yellow Pages" and some wont. In cell A1 I would like to have it tell me the total # of referrals from the lead source "Yellow Pages".
View 12 Replies View RelatedIs possible to make a script that search for a ”Word” in cells with more than one word?
View 9 Replies View RelatedI would like to automate this process that I'm doing currently through the "Text to Columns" function.
Basically I have various cells that contain many worlds such as:
FRESH LUNETTE SOLEIL REES 1'S
INFOKUS SOLEIL LECTURE LUNETTES 1'S
KENSIE LUNETTE SOLEIL ELEORA 1'S
KENSIE LUNETTE SOLEIL ELIZABETH 1'S
KENSIE LUNETTE SOLEIL JAMES 1'S
KENSIE LUNETTE SOLEIL VESPER 1'S
KIDS LUNETTE SOLEIL HOUSTEN 1'S
KIDS LUNETTE SOLEIL MINNIE 1'S
My goal is to separate the first world from the rest of the words in one cell.
Since all of the words are seperated by space, I use the the "Text to Columns" function and separate the words, so that I can get the first word into one column. The rest of the words have to be merged again through "Concatenate".
What I'm looking for is a VBA code or Excel function that can identify the first world (which vary in character lengths) and put it into one column.
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I want to erase all line breaks which I am not using from my work sheet. But still keeping the line breaks which has something written to it.
this as a example of one cell.
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 3
- Example text number 4
- Example text number 5
---------------------------------------------
Using "Find & Replace" I got to replace the ones I want to erase with nothing, so its blank. Too bad the Excel doesn't replace the line breaks with it, so this is what I get.
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 4
---------------------------------------------
The result which I want to have is:
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 4
---------------------------------------------
I know about the Clean Function but that function eases all the line breaks from the cell, which I dont want to.
If not that whould mean that I have to delete the line breaks by hand for hundreds of cells.
There used to be a function called "snap to grid". What happened to it? I want to draw a line and make it "snap" so that the ends of the line are at the intersections of cells.
View 3 Replies View RelatedI have a database of address which have been inputted into text wrapped cells like this:
Line1
Line2
Line3
Line 4
Can I turn these lines into columns?
I wish to create a Look up that looks at a range of cells on the same line Eg C6:G6
Then looks at a different tab and completes the look up.
E.g.
TCR!A:B,2,FALSE
No within the cells C6:G6 i want the formula to only use the highest match
E.g.
C D E F G
070031070031#N/A#N/A070
#N/A#N/A#N/A#N/A070
So in the first line i want the result to use the number 070031 and in the second example i want it to use 070 for the look up.
I would like to copy several cell entries from one worksheet ("Entry") and paste then into a new line of another worksheet ("Contact DB") which will serve as a repository/database. Ideally, I would like to clear the values in the first "Entry" worksheet once this is done.
The thing that is throwing me off is that the data entered into my first "Entry" worksheet is not all on one row. However, I have recorded all the starting point references and destination cells (i.e. "B2" from "Entry" --> "A5" of "Contact DB") so it's ready to go into code.
Using the following code (which I tested for copying and pasting the first cell of interest) but I can't seem to get it to work:
Code:
Public Sub Macro1()
Application.CutCopyMode = False
Sheets("Entry").Range("B2").Copy
Sheets("Contact DB").Range("A5").Paste
End Sub
I'm brand new to vba so I'm struggling to understand the code and where it goes. That brings me to Q2 (and a dumb one it is). Where do I put this code? I already have one bit of code (for allowing multiple entries in a dropdown list) for the "Entry" worksheet under the "Microsoft Excel Objects" folder. Is this where this copy and paste could would go too? Or do I need to insert a "module?"
if I wanted to slowly move a shape (small circle) in a straight line between cells R20 & W7.
The straightest line possible is by drawing an arrow line between the 2 cells.
Is there a way to then use this arrow as a guide for moving the shape?
In other words, would it make sense for the porgram to draw a transparent line between the cells, and use that line as a "path" along which to move the shape?
I just don't know if this is worth the trouble.
I have some code (authored by Dangler) that i want to pull not just one range but also the contents of one specific cell and then copy the range and cell to a line on another spreadsheet.
Question:
f .Cells(i, 8).Value > 0 Then
.Range(Cells(i, 1), Cells(i, 200)).Copy
Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)
End If
I want to add a single cells contents to the LINE that is being copied .... something like this...
f .Cells(i, 8).Value > 0 Then
.Range(Cells(i, 1), Cells(i, 200)) AND A1.Copy
Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)
End If
the output would be like this: CELL CONTENTS, RANGE CONTENTS
or
K21, 40012122 400 450 450 50 001 BAGS
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
I am trying to find a formula that will give me a word count in specific cells.
View 14 Replies View RelatedI would like to do a text split for the name only in cells where contains the word "Name". For example: Name A&A Enterprize
Text split function for it is =MID(A1,FIND(" ",A1)+1,255)
so it only recognizes A&A Enterprize.
How would I add an IF, Then function (If the any cell contains the word Name, apply the text split function, for cells don't have the word Name, just leave it blank) if i have to do this for a large data file so i dont have to copy this function for every cell that contains the word Name.
This does not seem to be working and I do not know why.
If Range("T3:T999").Value = "Duplicate" Then
MsgBox "It did work"
Else
MsgBox "What happened??"
End If
How can I delete all rows that have a cell that starts with the letters APP
View 3 Replies View RelatedI need a macro code that will edit cells containing links similar to this one "[URL]..." by adding "/current" at the end of them. There shouldn't be any space in between. There are over a thousand of them, and I think this would save me time,
View 9 Replies View Related