I have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.
I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use:
Sub aging_summary() Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Sheets("AR age sorting") Set Sheet2 = Sheets("AR aging analysis") For n = 10 To 13 For M = 6 To 9 Sheet2.Range(Cells(M, 4), Cells(M, 4)). _ Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _ End(x1Down).Value
Next M Next n End Sub
I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.
I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?
Need vba to do the following: Compare 4 Columns for a 100% match: B, F, N, K.
IF ALL 4 columns] contain matches then, it is considered 100% match and can proceed with other code.
'Below is an example of how it should perform the COMPARISON:
'1. Compare what NSN's exists in B3 and B4: '...If = MATCH, proceed to check critical column F for matches '...If = NO MATCH, proceed with 100% FAIL rule.
'2. Compare what NSN's exists in F3 and F4: '...If = MATCH, proceed to check critical column N for matches '...If = NO MATCH, proceed with 100% FAIL rule.
'3. Compare what NSN's exists in N3 and N4: '...If = MATCH, proceed to check critical column K for matches '...If = NO MATCH, proceed with 100% FAIL rule.
4. Compare what NSN's exists in K3 and K4: ...If = MATCH, proceed to the set of rules used to perform updated and merging to these 2 rows. ...If = NO MATCH, proceed with 100% FAIL rule.
PROVIDE MESSAGE: IF all four match, THEN, MsgBox ("100% Match")
IF at any point along the way, one of those columns does NOT match, THEN MsgBox ("NOT a 100% Match")
(for example, if it looks at Col B - finds match, it then moves to F and if F does not match, stop and provide message (no need to continue comparing the last 2 columns)...
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.
I have a 'rota' worksheet that includes staff names and their rostered hours on all dates through the month.
On each date of the month I need excel to find that date in column A and then look across the row to find any cells that aren't blank. When the cell has a value I need the name above it in row 1 and the value (number of hours) itself to copy across to a 'daily activities' sheet. The date is autopopulating on this sheet in cell H5.
The name needs to drop into column A and the hours worked into column B. I would like the first cell to be A9 and then down from there with no spaces in between names. The other info on the sheet will then complete itself using the name that has been dropped in using VLookup.
I'm presuming I can use a macro to do this for me but am really struggling where to start with it, and how to ignore blank cells as they will change cells on a monthly basis.
In Cell (W6) I have a value for example of 100,000 In Cell (AE6) will have a value of for example -100,000. In Cell (AF) I want to put a formula that says "if the number in (W6) is a positive number and the number in (AE6) is a negative number the return a value of "1". and vice versa if the number in cell (W6) is a negative number (-100,000) and number in Cell (AE6) is s positive (25,000) then also return a value of "1".
I need to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.
Attached file is the example : Matic Interpolation.xlsx‎
what I have is a spread sheet of over 3000 numbers.
Now this is what the number looks like:
0000123456000
what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?
However data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.
I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.
e.g
grade1 grade 2 grade 3 grade 4 grade 5 A A C B E
I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.
I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2() 'Delete all blank data rows Dim Rows As Double Dim Rownum As Double Application.ScreenUpdating = False Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1 NxtRownum: Next Rownum Application.ScreenUpdating = True End Sub
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:
I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached
I have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.
I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.
Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..
I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.
I am attaching the spreadsheet.
These are the websites I am taking my data from: [URL]
Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.
What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.
I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.
First Few Rows Of The Table: Excel-Keyword-Matching.jpg
What I Am Looking To Do: Keyword-Frequency-Complete-Example.jpg
I have a bunch of spreadsheets in a folder and I need to take one line of each spreadsheet (which is in the same place on every one) into a master spreadsheet.
I am always adding to the spreadsheets in the folder.
Is there a way to have my master spreadsheet look out for changes in the folder and when there are changes add the row into it?
I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......
I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)
This is what I would want it to look like:...........
I have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?
I have a userform that searches a data base for an address an pulls up the corresponding information. I was wondering if there was a way to get it so that if I put 1234 5th Street when it puts the information into the data base it would separate it into two cells so 1234 would be in one section and 5th street would be in another.
I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:
Sub test() Dim myDir As String, fn As String, ws As Worksheet myDir = "C: est" fn = Dir(myDir & "*.xls") If fn = "" Then Exit Sub Do While fn "" Set ws = Workbooks.Open(myDir & fn).Sheets(1) ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1) Workbooks(fn).Close False fn = Dir Loop End Sub I have tried using this code, altering the Dir String to the folder the documents are in.
However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.
To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.
