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 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'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 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.
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 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.
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 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.
(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 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 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.
I 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.
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??
I 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 "").
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 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.
A 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 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 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?
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.
With Sheets("regrade pharm_standalone") For Each r In .Range("standaloneTerritory") If r.Value = "X101" Then r.EntireRow.Copy Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues End If Next r End With ------------------- I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
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.
I have written the following code. I've made the code that works look like a comment. The code should delete rows 1 to 4; Insert a column and in column B sum the row to the right. Right now B1 is correct but it stops there. I've tried coding this as an if then else statement and the same thing happens.
Data: A B C D E F G
B206 5 1 1 1 1 1
[Code] .....
VB: [SIZE=2]Sub GetFlyingDays()[/SIZE] [SIZE=2]Dim Names As String[/SIZE]
I have a workbook with two sheets. One contains raw data without any formatting. I'm trying to link the cells in "Raw Data" sheet to my "Clean Data" sheet.
The data are financial results by divisions and each division by year. So my goal is to do a loop 'Do until or 'Do while by the years to separate the divisions. My years go from 2008 to 2013 repeating itself for each divisions. Doing that way, when I add a year in my "Raw Data" sheet and re-run my code it will add the 2014 financial results to the "Clean Data" sheet.
I have an add-in that can run the code below. It works fine, however, I need it to run whether there is just one row of data, or 100 rows of data. I have headers, so the first row of data starts in row 2, and the last no matter what will be in row 100 (so 99 rows of actual data technically). There are usually only 10 rows of actual data as the rest are usally set to blanks with iferror. The code below works, but I need it to run for however many rows of actual data there are.
I'm trying to get data from one ws to another with corresponding keys, and wondering what the best and most efficient way would be to accomplish this.
INDEX...MATCH ? Do While Not IsEmpty(ActiveCell)...ActiveCell.Offset(1, 0).Select...Loop?
Here's the setup:2 worksheets: source and target source ws has 2 relevant col: A and K which are key/val pairs target ws has x number of col val from source ws are to be written in 1st avail blank col on target ws some of the keys will exist in both ws, some only in one if a key from source ws is not present in target ws, it will need to be added along with its val
The # of the first avail col is targetCol: targetCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
What I am trying to do is pretty simple (this is what I imagine)
I have a 4 columns - the columns 1 and 2 contain a number of days and an associated number - the columns 3 and 4 contain a number of days and an associated number
My formula should do this: - if the number of days (col 1 and 3) are the same, then multiply the number in col 3 and 4 together.
- more specifically, for each number in col 1, the formula looks all matching values in col 3 and makes a simple calculation - result displayed in column 5
Constraints - col 1 has more data than col 3 - the data in both column are not sorted
Dim x As Integer
For x = 1 To 10 Step 1 If Cells(x, 1) = Cells(x, 3).Value Then Cells(x, 5).Value = Cells(x, 3) * Cells(x, 4) End If Next x
