I have a filename in the format XXX-YYY-ZZZ.xls, where all the XXX and YYY and ZZZ are of variable length. Sometimes it is of the format UUU-XXX-YYY-ZZZZ.xls
I wish to delete all the characters to the left of the rightmost "-" character, including that "-" character.
I have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.
<First Name> <Middle Initial(if any)> <Last Name>
My actual records contains the following:
BIMBO EPPING - OK
AHMAD SADICK - OK
Salana, Laura R - need to be arranged to > Laura R Salana
Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos
I have a excel sheet where I have a list of strings seperated by a "-". I am looking to take the particular cell value and swap the characters before the dash with the characters after the dash. So for example:
FG03-FJ04 would become FJ04-FG03
AB02-CD12 would become CD12-AB02 and so forth....
I have tried findling around with the split function but cant seem to get anywhere, does anyone know a tried and tested method of doing this?
I have a .csv file that contains numerical data in the form of strings, much of which starts with leading zeros (i.e. "012345"). I double checked to make sure the .csv file has the leading zeros and that they are wrapped in quotation marks in notepad, and it does. However, when Excel opens the file, it must read those strings as numbers, becuase it chops off the zeros. Does anyone know of way to fix this? Can Excel read numbers as strings straight from a .csv file?
View 3 Replies View RelatedI have some code traversing a directory of folders and subfolders on a shared server and returning a list of those file paths. Around 6000 folders with about 2 subfolders on average. This returns a list of 12000 filepaths but takes around 13-15 minutes to do. Each path looks something like this:
C:Folder1
C:Folder1Folder1SubFolder1
C:Folder1Folder1SubFolder2
C:Folder2
C:Folder2Folder2SubFolder1
C:Folder2Folder2SubFolder2
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
I am working with excel sheets for dimension population of metrics.
I want to make some process less manual. In one column I am given some values like ((1110 + 1230 + 3120 + SERVSELCT) - (1110.21 + 2110.30))
Based on these values I have to write the expanded form like:
The values that do not include the decimals are known as parents, for which I have to write PCYDYNNOTRANS_1110 and for the values that include a decimal I have to write CCYDYNNOTRANS_1110.21
I have to write this in the adjacent column for all the values I had mentioned above.
I was wondering if there is a way that I can make this less tedious, and I can have a list that would pop up in the adjacent column where I could select which one I should use i.e. either PCYDYNNOTRANS_ or CCYDYNNOTRANS_ and I would have to just copy paste the values.
I'm looking for a formula or code that would take a client's name that appears in one cell, with the last name first then a comma and then the first name, and paste it into another cell with the first name first and then the last name with no comma.
Example: cell A2 contains "Smith, Joseph", cell B2 should have "Joseph Smith". If I need to provide additional information,
I have a large .txt file that I would like to edit using VBA. What I would like to do is to find each of the following text strings in the text file :
De:
De :
Envoye
A:
Objet:
If the character right before those text strings is not a carriage return (chr(13) then add a carriage return.
I would like the code to read each line in the text file and execute the task mentioned above.
I have a spreadsheet list in the following format (Note Tax ID would be cell
A1, etc):
A B C
Tax ID Numbers Street
11-03-3040-0178-01-794HIGMAN PARK
11-03-2310-0001-00-3100HELMAR
11-03-3040-0169-00-0102HIGMAN PARK
11-03-3040-0165-00-4104HIGMAN PARK
11-03-0020-0039-01-6104S CRYSTAL
11-03-0021-0006-01-9105S EUCLID
11-03-0024-0004-08-8105PAUL
11-03-0018-0029-00-1105HIGMAN PARK
11-03-1880-0069-00-4107S CRYSTAL
This continues downward to cells 6577. I'm looking for a way to carry these three columns over at some point so I can put more data on a single page, thus minimizing the # of pages I will need to print, but yet remain in the proper sequence as established in column B.
=IF(AND(B3:ZZ3="Jan",B10:ZZ10>=500000,B13:ZZ13="POST"),"1","0")
And I'm pulling from a spreadsheet that looks like this:
Jan[B3], Jan[C3], Jan[D3]
500,001[B4], 632,000[C4], 992,000[D4]
PRE[B5], POST[C5], POST[D5]
(and the function is calculating how many januarys, greater than 500,000 that are also post).. how do i get it to ADD the amount of fields that meet that crieteria instead of giving me a 1? I want it to say "2" (adding together all that meet those needs)
I have two inputboxes. I want to set the default value of the second inputbox as (effectively) one cell down from the first inputbox entry. In other words, if I type G17 into the first inputbox, I want the second inputbox to then have a default value of G18.
View 5 Replies View RelatedI am unsuccessfully trying to manipulate and copy a formula.
Column A would typically be used as a benchmark for one retailer and have the base price with the other columns simply showing differences in margin etc if the price was changed. This would typically mean the BWP and fixed terms etc would be constant.
However in this circumstance I need to ascertain for different retailers a,b,c,d, etc the shelf price and compare their NTS and % Margin. Whilst I can enter the different prices and change the terms as required, the whole sheet seems to link back to column A rather than simply drawing from the info in each respective column.
a
b
c
Price On Shelf
20.12
20.05
18.87
Shelf Price Excl. GST
17.50
17.43
16.41
[code]....
I have a userform that accepts some information and checks to see if the values have already been used (address and work order number) If the value is used the user must change the value ,an input box is triggered to get the new value. My question is how do I transfer the value back to the userform??
View 9 Replies View RelatedI have 3 pivot tables I'm using a macro to get a value out of, by changing the 2 "Page" fields. However, after the macro's done running, the page fields are messed up. (The order changes, one of the options becomes "").
View 9 Replies View RelatedIs it possible to modify a link so that you can change the link easily by the value of a certain cell.
='[Labour Plan Week 24.xls]Hours'!$E$135 and I want something like ='[Labour Plan Week " &H5& " .xls]Hours'!$E$135. Where H5 contains the week number
Theres a progam created using Firebird as described here: {url}
I want to be able to enter data into the firebird application from an Office application (e.g. Excel) using VBA.
I want to also be able to extract data from the application using VBA.
I am working on a macro that opens up a workbook, formats the data by moving columns etc and then spits out the data to different workbooks according to a string in col R (M,C,S etc). I am able to split the file into the workbooks, but it copies the whole sheet and not just the visible cells (its working via a filter). I have tried adding code into the loop to basically copy the visible cells to Sheet2 and then delete (or clear) sheet1 so that I only have the relevant data but have very interesting. This is only the first of a few procedures I would like to add within the loop. Ideally I want to have a prompt for the filename and path for each file, and also another procedure to add a number into each seperate file. If I know why I cant get this simple function to run I will, hopefully, be able to add in the other bits!
Function SplitSheets()
On Error Resume Next
Dim ws As Worksheet, source As Range, dest As Range
Dim daily As Worksheet
Dim sType(4) As String
Dim i As Integer
Dim strSaveFileas As String
sType(1) = "M"
sType(2) = "S"
sType(3) = "C"
sType(4) = "N"
Set daily = Worksheets(1)
For i = 1 To 4...................
I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.
So to be sure I am describing correctly, I am after a formula that will enable me to
1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.
I am trying to wrap up a project. The project consists of taking the input values from a userform and entering them into various Word documents and Excel workbooks. The procedure is run from Word. I have no issue completing this part.
However, there are a few Word documents that have an Excel worksheet embedded into them. How can I manipulate these worksheets using VBA? I have attached an example for your reference.
I have found out how to get into edit mode, but I still do not know how to actually edit the worksheet.
i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.
View 13 Replies View RelatedA B C D
--------------------
Mike Fax 5 60
John Tel 3 30
Mike Tel 2 30
Mike Fax 1 60
John Fax 1 60
--------------------
hi all, i am faced with the following problem. before i describe the problem, i do not need help with recording a macro or using functions to solve the above problem, i know how to do that.
writing a code from scratch to do the following:
I need a code that will run through the above columns, and IF A=Mike and B=Fax, THEN C*D...so I want to calculate a total for everytime Mike sells a Fax (or a Tel). Column C is quantity, Column D is price.
I am sure this is a simple loop code, but I have spent days trying to figure it out and I can not do it. Recording a macro is useless, because it is too confusing to look at the code and play with it and try to personalize it.
I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).
What commands to I need to enter into VBA to get this to work?
I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.
The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.
The current values that these cells hold are:
L6:
=Graphics!$J6:$J9
L7:
=Graphics!$K6:$K9
Oh, and the name of the chart is "Chart 1"
What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)
If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.
I have a contiguous range of rows starting from row 5. ...It starts from row 5 but could end anywhere below.
The name of my Sheet is Tester.
Now in Tester, columns B,C, and D have numerical values in them. Using VBA, how can put in values in corresponding row of column A.
So if there are values from B1:D39 and B40 onwards is empty, then A1 will have value B1*C1*D1, then A2 will have B2*C2*D2 and so on until A39 = B39*C39*D39
**I need to write the code so the whole process is done fastest.The code may be long but the macro needs to run as fast as possible.
(Also, I remember using Screen.Updating = False when I wrote similar stuff many yrs ago. Would this be useful here?)
I Am trying to create a pivot table. I have attached a sample of what i want. I have same kind of data for different dates. I would like for one field to list out seperately under each date and one field to sum up for the entire week( Total paid hrs). How do i go about doing this... is there a work around?
View 3 Replies View RelatedI would like to give credit and thanks to StephenR for producing the current Macros in use on this spreadsheet.
This was to sort out Raw Data that I get from a database, and with lots of help sort it and format so for a history of this you can look there.
Now I need to enter in some formulas, data from another spreadsheet and cell formatting…
Unsure if all this is possible but am hoping some of it is…
First Thing
The column Run Time has the data in them already this is the time in minutes am looking for a way to put a formula in, that will make them into hours so the formula will read like this “=180/60” this will give the number 3 as the whole number.
Second Thing
Using the whole number that the formula made in the Run Time column, am looking to fill cells with colour, too see what I mean here its best to take a look at my mock up spreadsheet.
Third Thing
Two rows below all the data that gets sorted out I would like to put in two totals, one for Run Time and another for Available Hrs “=SUM (Range)”
Forth Thing
I have data in another spreadsheet that is the Resrce Name, and Available hrs am looking to take that data from this spreadsheet that will be closed opened if needed, this data will assign workers to Work Centres.
I would like this to be a separate macro to run though all the sheets if possible.
This could be a bit complicated, but hopefully also useful for others. We have the following sample of 10 cells from a large file with similar data:
N: 1; B: 162; M: 278, 278; T: 24; A: 103, 105; I: 393; Ḥ: 7240, 7390, 7465, 7546, 7617, 7756, 27399, 8380, 8741, 8894, 8985, 9559, 9671, 9741, 10119, 10211; W: 40; D: 766.
N: 2; B: 246, 889, 1136; M: 255, 255; A: 55; I: 286; Ḥ: 22731, 22802, 22857, 22906, 22948; D: 685.
[Code]...
We want to extract the same letters with its numbers and put the same letters with numbers in one column and do this for all letters, but - this is important - while keeping them in their original row. The purpose of this is addition of data and easy rearrangement of the letter plus numbers order, e.g., to: B M N A T I W D Ḥ. As you can see not all the rows have all letters, nor are the seizes the same. There are thousands of such cells. Some parts are partly in bold, we want to maintain that layout after sorting. Such a thing should be easy for computers, but how could this extraction and manipulation be done (I have Excel 2010)?
P.S. There are two spaces after every ; that were eaten up here.
I have an origin and destination matrix with one set of zones and need to convert it to another set of zones. This involves combining some zones together. I tried a sum if, but it didnt work. I have done the calcs individually but will have to repeat the process so was looking for a wuicker, more efficient and less error risk!
Just to clarify, I have to convert a 23 by 23 matrix into a 14 by 14.
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View Related