I need to update a Variable I called "Croy70" by making up its name from two cells
Left(Range("L" & i), 4) & Right(Range("J" & i), 2)) = i
Where i=2 and L2 is "Croydon" and J2 is "ABC70"
I apologise for this simple question, but I appear to have left my brain at home this morning and all the VBA help files are missing from the computer I'm using!
I am building a chart from a pivot table and the data changes weekly therefore my chart changes weekly and I want to have the same colors on my graphs from week to week. I created this code to keep everything the same. Problem is when some of the data is not there for a given week (say no calls from a certain city) a corresponding data set doesn’t get created and the macro won’t work. Is there a way to run if the a data set is not available and to keep the colors the same?
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With..............
I can start building.
1. User will first input Medication type in first column. Second Column - Dosage Third Column - Start Date, Fourth Column - End date
There will be multiple entries for the same medication because of dosage change. I can simply get them to insert a new row underneath each medication, and they can then input new dosage, new start/end dates
There will be a cell entry for the form start date, as it will be populated on the main page.
The solution I believe will involve an if statement, with argument being if date is still within range of start/end date, that it will generate text on particular date.
I need to build a formula that looks up a vertical value and a horizontal value and retrieves the value where those two values meet
for example
REGION
Grade ST LOUIS MILWAUKEE SOUTH DAKOTA
C10 30,000 35000 45000
C11 27500 25000 30000
C12 22000 60000 23500
I need the formula to retrieve that value for grade C11 and in milwaukee which is 25,000
I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 1-10 on day 1 in the respective column. Then item 2 in lists 1-10 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 1-10 all ready to go, but I am having trouble figuring out how to go about populating the other columns.
View 6 Replies View RelatedI am building a form with a list of names and check offs next to them. There will be a list of 150 names and 3 check boxes next to each name. I want to be able to create a running total of each check mark. So I have a name with a check mark that marks off SSN, Drivers Licence, drug test At the bottom I want it to give me the total names that have a SS Card, Total that have Drivers Licence and so on.
View 1 Replies View RelatedI have a workbook with 3 worksheets titled “Numbers”, “Letter” and “NumLtr DB”.
The “Numbers” worksheet is a dynamic listing of numbers in Column A, which also is a Dynamic Named Range using the formula: OFFSET(' Num'!$A$1,0,0, COUNTA('Num'!$A:$A),1)
The “Letters” worksheet is a dynamic listing of letters in Column A which also is a Dynamic Named Range using the formula: OFFSET('Ltr'!$A$1,0,0,COUNTA('Ltr'!$A:$A),1)
I need a macro to duplicate in column A, on the worksheet titled “NumLtr DB”, the first number down as many rows as are needed to equal the full count of letters, and then, in column B, list each letter in succession. This process needs to be repeated once for succeeding each number and then stop.
We have a number of dogs and information about their sire (male parent), dame (female parent) - and those dogs parents.
In essence something like this:
DOG1: DOGDAD, DOGMOM, DOGDAD's DAD, DOGDAD's MOM, DOGMOM's DAD, DOGMOM's MOM
So...I'm thinking 7 fields for each dog.
I started out with an Excel s/ sheet that looked like this:
Dog Sire Dame SireD SireM DameD DameM
DOG1 DOG2 DOG3 DOG4 DOG5 DOG6 DOG7
DOG8 DOG9 DOG10 DOG11 DOG12 DOG13 DOG14
DOG2 DOG15 DOG16 DOG17 DOG18 DOG19 DOG14
Now here comes the issue....what we are trying to do is to find and exclude any dog (in the first 'Dog' column that share ANY grandparent (SireD, SireM, DameD or DameM) with any other dog from the first column.
(As an example above - DOG8 and DOG2 share the same grandparent (DOG14) and needs to be excluded from the final list).
I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....
How can I see the following information in chart?
-Non con close out time by person- so who is failing to close out on time in this case is completion date
-Frequency of reports by area/ shift
-Number of reports by month, split into critical/major/minor
-Main reasons for reports so trend of type of record
Fairly simple question, but I don't remember syntax.
The simplist cell reference is =F27
If you want to make the column reference a variable and the row reference a variable (=ColumnVarable:RowVariable) What's the syntax?
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
I am trying to create a Macro/VB code that will build 4 lists based on a list (see attached example).
I have a list of applications and the numbers of users having them installed on their computers, these applications are marked using a RAG status (Red, Amber, Green & NBR No Business Requirement) on a seperate work sheet i want to split them by these RAG statuses, the status of applications changes as they are packaged for SCCM, so the RAG status will be changed, so i want the code to be able to rebuild the lists at any time to obtain an accurate view of the numbers at each status.
I'm trying to figure out how best to build a table to do:
IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"
So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.
A sample of the data is as follows;
Staff No:
Arrival time
Processed time
Exit time
1
26/02/2013 07:13
26/02/2013 07:22
26/02/2013 13:52
[Code] ........
I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement it´s sort of a circular validated data.
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View 2 Replies View RelatedI have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies View RelatedI am trying to automatically construct a unique consolidated lists from several other lists. I want this to be formulated in such a way that the mother list is uptodate and reflect all changes made to the subsidiary lists. I have attached an example of what I am trying to achieve.
View 8 Replies View RelatedI'm working on a macro that does the following:
1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values
- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:
I'm running Windows 7, Office 2010
Code:
'Chart variables
Sheets("Summary").Select
Dim ChartRange As Range
[Code]....
Would like to know if it is possible to create a kind of conditional formatting for graphs. I need to work on a graph and when the value is negative the bar changes of color.
Is there any tool or macro code that can do it?
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies View RelatedI am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8
B2 to E9
C2 to F10
D2 to G11
E2 to H12
F2 to I13
Essentially, I am working with a database for a school and up until this point, each row matches up to a student with a bunch of information on them. One of these pieces of info is their parent's names. This is a much simplified look at things, but the screenshot below is a basic version of what everything looks like:
Now, the school wants me to split up the parents names from the student's and place them in their own row. I have recorded a macro to do this where you make the active cell the "full name" column for the row you want to expand, and then run the macro. Afterwards, up to two more rows are added, with the parent's names and some data copied. Below is a screenshot of what that looks like: (yellow is the new data generated, and blue is the active cell I marked to run the macro properly)
The issue is that there are thousands of these that need to be done, and I figured there is a way to do this with a macro. The issue is that not every row has parents names to expand out.
In layman's terms, I need a macro that will look at the two columns titled "Father's Name" and "Mother's Name" on each row of the spreadsheet and then, if there is any data in both or either of them, it should set the active cell to the "Full Name" column for that row then run the macro accordingly. After that it should repeat this all the way down, skipping any where neither of the columns have text in them
In the actual spreadsheet, the columns that need to be checked are AW and AX, and the column that needs to be set to the active cell before running the macro is B.
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedI'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies View Related