I have a variable COMTXT that loads (via loop) several short lines of text to form a "mouthful" of comments together. But have 2 issues with it.
1- I need to add a carriage return after loading each short line of text. [code] Module1.COMTXT = Module1.COMTXT + Sheets(3). Cells(Module1.COMCODE, 1)
[?CODE]I have try'd [& vbCrLf &] in many syntax's to no avail.
2- When displaying COMTXT to a sheet, its too big for the cell ! and not able to enlarge the cell without major disortion to rest of sheet. How do I acheive this and allow the text to display like a textbox or label, covering many cells and rows.
The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this.
Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)-1 ?
The basic spreadsheet consists of a list of names down the left hand side with 19 columns across - one for each art activity (I have pasted a copy of it below - but it doesn't paste so good!). Each person has selected their three activities and this has been recorded by placing a 1, 2 or 3 in the relative columns. Each person has been told that they will get a place on two out of three of their chosen activities (in which case we will change one of their choices to a 0).
What I would now like to be able to do is to create a mail merge in Word which looks at each person and then goes along that row to find out which columns have a 1, 2, or 3 in it. The heading for that column would then be used as a merge field so that I end up with a word document which looks something like this:
I have a macro that takes input from the user and replaces certain text on several worksheets. One of the inputs is a username in the format of firstname.lastname. I need to manipulate this input such that the dot is removed and the first twelve characters only are used, all in upper case. e.g. Michael.Jackson would become MICHAELJACKS
I know how to use cells.replace to replace the text but I don't know how to use a formula to manipulate the inputbox text before I do the replace.
I had a problem today where I had to make some changes to a couple of large text files (100mb+). It seems that the File System Object (Microsoft Scripting Runtime) can only read or write at any one time so I ended up writing this. It opens two text streams at once , one to read and one to write and creates a revised file. Not very exciting but I thought somebody might find it useful ...
Sub ChangeTextFile() ' Manipulating a text file with VBA ' Loops through text file and creates revised one ' This code requires a reference (Tools > References) to Microsoft Scripting Runtime Dim FSO As FileSystemObject Dim FSOFile As TextStream, FSOFileRevised As TextStream Dim FilePath As String, FilePathRevised As String FilePath = "c: est.txt" ' create a test.txt file or change this ' adds "_Revised" to your file name FilePathRevised = Left(FilePath, Len(FilePath) - 4) & "_Revised" & Right(FilePath, 4)........................
I have the following text file. I need to show in excel as follows:
1st column : Company No eg 006 2nd column : Expense Type eg 060 3rd column : Agreement Type eg HIP 4th column : Agreement Number Columns C&D added together. 5th column: Tot Def Expense
The breakdown above should be at an agreement level.
I was looking to have a Word for example 'Apple' but want it as a number e.g '10'. I want to use it so that I can add up the prices of items by just clicking on the name of the item. So If I want to add up 3 items e.g. an apple , a banana and a pear which all cost £10 each I want to be able to click all three items and get the total of £30 displayed in a certain box.
I am using the following math formula in cell C12:
=IF(B12="","",IF(B12<2,-1,INT(B12/5)))
What I want to do is if cell B3 equals a text value of the word "All" then cell B12 would display nothing. If B3 equals any other value then the math formula above be used.
I'm using an Excel spreadsheet as a database (mostly text based), so that in columns 1 and 2 are names and in column 3 is notes. I'm keeping all the columns single height (ie: not wrapping text). Where there is nothing in column 4 onwards, the text from column 3 displays across the subsequent blank fields.
I'm having to re-learn formula commands in order to continue to use my spreadsheets (I had tried to get the Quattro Pro converter to work in Excel 2003 with no success). Actually, I would have stayed with QP since I find it much more user friendly, but with Excel being the standard spreadsheet that most people use, I figured I better get used to it. Anyway, I'm currently trying to find out how I might get the same text that shows up in varying cells in an otherwise blank column (other cells contain formulas, but no text) to also show up in another fixed cell (which can be where I put this formula).
I am trying to display watermark text in a cell based on the value of another cell. For example:
If in Column B, cell B2 states "Mileage", Column C, cell C2 needs to say "Please enter Start and Destination...". I want column C (cell C2) to be my input cell as well hence the reason i'm avoiding the use of an IF formula.
I'm open to a VBA solution if this works? Tried conditional formatting however it will only display colours, not text?
I have a bunch of cells that are only displaying ########## (they contain strings of text, and the actual text can be seen in the formula bar).
I know for a fact that the cells do not exceed the character limit for a cell and other cells in the same column are being displayed just fine. I've also tried adjusting the row height and column width but it doesn't seem to be working.
I'm putting the finishing touches to a grade sheet for the new academic year. I have one column that calculates a grade using the OFFSET function from a given number of points. However, I only want this cell to show a grade when the nine individual unit columns have been populated. If fewer than 9 columns have been populated, the cell in question needs to be blank. I've tried using COUNTA to count the number of populated cells and then linking that cell to an =IF(Q5=9... formula, but haven't been able to crack it yet. I'm probably missing something obvious...
At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day. What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
I wanted to count the number of instances that Matt's been late. If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.
In this example, I would need a result of 4 instances.
tell if the blank cells will affect the formula or if I still need to do something about them.
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HYEYDH/14 222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HDGTEY/56 333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
I'm using the following code to copy columns of data in a worksheet of mine. The code once activated will open "notepad" and copy the columns of data in my excel worksheet. Here is the
I have some sample data in "Sheet1" and another sample data in "Sheet2".
The intended output is shown in "Sheet3".
For example: if "Student1" in Sheet1 match the data for "Student1" in Sheet2, then all the entries for "Student1" in Sheet2 will be copied to Sheet3 (ouput). The same thing goes to other students in the list (Sheet1).
Code: Option Explicit Option Compare Text 'for Case-Sensitive matching change Text to Binary Sub List_Matches() Dim sPattern As String, sPath As String, sJob As String Dim sMainDir As String, sCommonSub As String Dim c As Range, lRow As Long Dim d As Range Range("B:B").Clear lRow = Cells(Rows.Count, "E").End(xlUp).Row If lRow < 8 Then Exit Sub
[Code] ...........
The red is the part that I tried to manipulate to add the new directory, the problem I can see is that the new directory contains folders with the following name "WO#____" as i can see the # sign is probably throwing the program off because it can't open up the link to that folder in order to look for files. The program as it stands still works fine with looking up the first directory. I also know we had created a function to solve the sign problem for directory 1 file names. How can i use that function to directory 2.
I am trying to find a way to protect and close column groupings of a number of worksheets on workbook_open procedure by looping the worksheet codenames instead of just the worksheet names in order to prevent potential problems with renaming the sheets.
This code didn't work...
Code: For i = 6 To 25 With ThisWorkbook.VBProject.vbcomponents("Sheet" & i) .Protect "rbse" .Outline.ShowLevels columnlevels:=1 End With Next i
I have a dataset that has replicated Data Values for example "Bob 25" in one row, "Bob 32" in another and so on. This is contained in one data sheet. I want to be able to do something like a VLOOKUP however I want to every instance that the data value occurs to be represented sequentially in my table, so that every instance of "Bob" would be in represented in my table.
Range("B5").Select With ActiveSheet. PivotTables("mypivot).PivotFields("team") .Orientation = xlRowField .Position = 2
This will move the data row field called "team" to be the second from the left. I would like a way to move whatever (without mentioning the field's name) field is in position 2 to position 3. Similar to an "offset" to the right.
