I have an excel sheet that has conditional formatting. Whenever few cells are not in required format, the cell's backcolor will become red. I am trying to design a macro to check if there are any cells in red due to conditional formatting.
View 1 RepliesSo I have a bunch of not so simple excel sheets in a Excel file. I'm trying to automate the testing of these (right now, I'm doing the testing manually).
To simplify, let's say there are two sheets - both of them have a cell called "Total" at the end, which is nothing much a total of some column. My test is to check if these two totals are the same. We don't know the number of rows before hand (it is coming from a database). The way I'm doing right now, is searching for the cell containing the text "Total" getting it's row and column, and adding 1 to the column, to get the cell that actually has the total value. Do this for both the sheets, and I have both values, and it's easy to compare now.
If someone changes the text from "Total" to "Totals" then this will fail.
Questions:
1. Is there a better way to do this? Say, setting a unique parameter or something on the cell that actually has the total value, and using that unique parameter to search the cell?
2. Or is there a totally different (better) approach to this type of testing?
I need a macro to identify when cells are >= 90% in a range, and count them. Then put the result in cell Z58. Range is I51,L51,O51,W51,Z51. i.e if all cells are 90% then put value of 5 in cell Z58. Here is code i have that isnt working:
View 3 Replies View RelatedI have an excel sheet containing data as given below, wherein we receive certain report from each department on monthly basis and the same is mentioned as received against each month under header column. We need to remind the department about non receipt of report for particular months which contain empty cells. Macro is required to generate the report of empty cells mentioning the months against each department.
Available Data
Dept
Sep-12
[Code]....
I have a worksheet with a large amount of data in it. Every couple of months the data will change and I place this in a different sheet (sheet 2, this comes from a software tool) . Is there's some code that can run a check on sheet 1 compare all values against sheet 2 and highlight the rows that have a different value between sheet 1 and sheet 2? Perhaps paste the rows into sheet 3 with the different value highlighted??
Example of original and changed data attached : example_2.xlsx
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
how to compare two columns on two worksheets and extract any different entries - I have tried using filters and formulas but can't seem to get this to work. I've been looking at Macro's and VB but as a novice am finding it difficult to construct something that works!
My workbook contain two worksheets containing Journal titles and their ISSN's from different sources. I need to ensure the lists match on both columns and if not have a new summary worksheet of anomalies.
Example:
worksheet 1 worksheet 2
Column A Column B Column B Column C
publication_title print_identifier Title ISSN
Abacus 0001-3072 Abacus 0001-3072
Accounting 0810-5391 Accounting 0810-5391
Archaeologica 0065-101X Archaeologica 0077-4204
Acta Neurologica 0001-6314 Acta Geologica 1000-9515
So ideally my summary worksheet should have results:
Column A Column B
Archaeologica ISSN error
Acta Neurologica Title on worksheet 1
Acta Geologica Title on worksheet 2
These are lists with around 3000 entries on so VERY time consuming to do manually!
Macros and Often I find it challenging to locate the last row and/or column on my worksheet.
Any specific syntax, command or function I need to use?
I have a VBA script that works great when run each day, as it's intended. But if a user skips a day, we end up with an error when deleting an old worksheet. The old worksheet to be deleted has a date as the sheet name (i.e. "2014-May-23").
We've developed some code to find the most recent FILE if there wasn't one created on the previous business date, but what VBA code can I enter to correctly identify and delete the old, outdated worksheet? My final spreadsheet should contain just two dates worksheets - one each for the past 2 business dates.
Here's a snippet of my current coding:
Code:
Sub Master_3D_Macro()
'
' Master_3D_Macro Macro
'
' Keyboard Shortcut: Ctrl+j
'
Dim CurrDate As Date
Dim Holidays(1 To 9) As Date
[Code] ..........
I am using an event macro to trigger a set of calculations. In the spreadsheet, users have a choice of 3 input cells to work out an answer; these 3 cells work out the same cost from different angles depending on the variables available to the user.
Users only ever need to fill in one of the three cells to work out the answer as the event macro I designed, should (upon input by the user), work out the remaining two input cells using logical arguments.
The macro and calculation work fine, except in the event of users amending a value in an input cell previously updated; in other words, if all 3 cells contain a value and 1 of them is being changed by a user.
The issue: I need the event macro to recognise the location of the last change made by the user in order to determine which 2 of the 3 cells need to recalculate. However, users will invariably press enter or tab etc... upon making changes and this has caused me issues so far.
What I have tried already:
1) running another event macro "on selection change" to record elsewhere in the model all cell selections as and when the user interacts with the model
EFFECT: it made the "on change" event macro very slow and clunky; so I need to avoid
2) declaring a range as set r = activecell.address
EFFECT: this did not work as the active cell's address would in fact be the address on the last cell; e.g. the one selected further to pressing enter (often the one directly below the cell recently changed)
A solution to add to the existing "on change" event macro that identifies the last edited cell
I have been given an excel model to review;the workbook has 53 sheets. I would like to know if there is a way to create a flowchart/matrix with the relation of all the sheets. I would like at least to get a list of all the sheets related to each sheet. All what I was able to do is to get all the sheet names in one sheet. (I am using Excel 2003).
View 2 Replies View RelatedI have written a UDF to do some calculations i a workbook. I would like to run a sub, checking the cells in the spreadsheet to see if my formula is present. and if so, then add or change an argument in the formula. My problem is:
How do I write the code so that it will recognize my formula ? Is it possible to attach a tag to a formula which Excel can search for ? I cannot search for the formula including arguments from specific cells, relativ to the formula cell, as the user is free to input the arguments with reference from anywhere in the workbook.
I have attached my sample XL sheet. Column A & B will be imported from computer system, only one p/n and quanity. Column C & D will be scanned in from multiple trucks and warehouse, p/n might show up multiple times with different quanities. I need to compare A&B to C&D and come up with a recount sheet that shows the difference as in E&F. Ideally this could be put on another sheet that could be printed out for recounting. Is it possible to do all these different functions automatically. There will be somewhere between 3000-5000 different p/n's.
View 12 Replies View Relatedi got a little problem with a piece of code that i can't seem to understand.
i have a column that is dedicated for a vlookup and there are some #N/A values.
im trying to take these #N/A values and copy and paste them somewhere else.
If Range("P" & i & "").Value = "#N/A" Then
I need some code to identify a cell value in a row and copy the entire row to a new worksheet, then move down to the next row and repeat to the end of the worksheet.
There are 4 vlues I would need to identify:1,3,7 and 28.
Am looking for a VBA code that will highlight a changed/edited text from a particular cell. For example - If I have a text in cell B2 as - TEST, and the same text is in C2 as well. However, when I change text in C2 to TEST1 or TES, the change should be highlighted.
View 6 Replies View RelatedI need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
I have a list of over 3000 numbers, many of them duplicates, and would like to write a Macro that lists down the top ten most occurring numbers, in the correct order, with their respective frequencies.
View 3 Replies View RelatedI am working on a file where the data for numerous customer gets uploaded on a daily basis.
So we have to update our record daily for respective customer.
In the attached file, Sheet1 provides data to be uploaded. I have included only 5 customers in my example).
I want a macro which can first identify the date (cell F4 in tab Sheet1) for which data is available in "sheet1" & then uplaod the same for respective customer & date in tab "sheet2" & "Sheet3".
Sheet1:
******** ******************** src="">*********>Microsoft Excel - QUERY.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF4=
DEFGH4**24-Sep**5*****6*****7*****8*Total*Open*ReceivablesMTD*ShipmentsPast*DueCurrent*=*Total*Current*+*Receipts*at*Risk9AAA65434761211983.6103855.4650004010BBB1250367782723.41203167.6103903111CCC2812807997329.21240538.2261891412DDD370306.763030.7998460.74264839.413EEE1003432390122.543242.33921159.6Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Sheet2:....................
I am trying to write a macro to check for duplicate numbers. My logic is as follow:
If(A2-A3=0) Then
B3=1
I want to write the macro as follow:
Select Check>0
Do While Check <885
If(A2-A3=0) Then
B3=1
End If
Loop
What is the best way to track blank/empty range cells?*Have tried 'IsBlank' but it keeps showing "False" (cell range: H7:P7, no value and spaces)
View 3 Replies View RelatedI am working on a spreadsheet in Excel '07 that will pull data from online and reformat it in a way that another program can digest. The last thing I need is for excel to recognize if there is a 6 digit number in a specific column.
This is tricky for two reasons:
1. Numbers come in three different formats, ### ###, ######, and ###.###
2. Sometimes there is also text within the cell, however I need to pull out just the 6 digits, and place them in another cell with format ### ###
Is there a formula or macro that will identify which cells on a spreadsheet containing various dollar values will add up to a predetermined total? I work in Accounts Receivable and sometimes vendors send in payments for several invoices with no instructions as to the application. I want to be able list the available invoice amounts on a spreadsheet, and be able to tell which of the cells add up to the payment amount.
View 2 Replies View RelatedIs there an easy way to show/return any Unprotected Cells on a Protected Worksheet?
View 8 Replies View RelatedI want to select the cost for fuel based on the date using the select case method.
Sub FuelCostApplication()
Dim Check As Integer
Do While Check < 10
Select Case Range("B4").Value
Case 8 / 30 / 2007 To 9 / 5 / 2007:
Range("C10").Value = Range("B4").Value
ActiveCell.Offset(1, 0).Select
Case Else
Range("C10").Value = 0
End Select
Check = Check + 1
Loop
End Sub
CREATE TABLES LIKE BELOW?
-----------A----------- ---B---- --C-- --D-- --E-- F
1 US Air - Ground Expense
2 Fuel
3 Week1 Week2 Week3 Week4........................
I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
So I have a list of numbers in column A with an amount in column B. There are duplicates in column A. I need those duplicates pulled out into another cell with its amounts from column B, then total the amounts from column B. This would give the user the total amounts from column B. I have attached a sheet with the examples hand typed.
View 4 Replies View RelatedI am working on a formula by it is repeatedly failing.
Based on input 1 which is the month name, I am looking for a formula to identify the position across columns as the starting cell and then the value in input 2 needs to be repeated 12 times from there.
Example:
Input 1 is Apr-14
Input 2 is 51.8
I have columns with month names starting Apr-13 to Mar-15
Since the input 1 is Apr-14, the formula should identify the appropriate column named as Apr-14 and from there it should copy the value (input 2 ) 51.8 for the next 12 cells which is until Mar-15. I have attaches the examples for your quick reference.
This should be pretty straight forward but the solution has escaped me so far. I have some formulas that ratably spread monthly budgets across the life of a program. Ocasionally these budget formulas are hard keyed over with a value which overrides the ratable budget formula. I would like to conditionally format all cells that contain hard keyed values so they don't get inadvertently copied to new budget lines.
View 8 Replies View RelatedI regularly import a list of data into Excel containing employee team names, employee names, and (for these purposes, irrelevant) data. The team name is the only data in column I, and therefore I can easily pick this up an summarise it in a lookup. The employee name is always one row above it, and in column D. However, it is not the only data in column D, and each employee's records contain varying numbers of rows. How can I have Excel copy the employee name in to column J, alongside the team name in column I, but ONLY in rows where there is data in column I?
View 3 Replies View Related