I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:
F|45|A|30|F|15|F|10
Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.
SUMIF(A1:G1,"F",B1:H1)
What I need to do is specify a range of every second cell in the row (starting with Cell A1) for validating they equal F, and a range of the alternating cells (starting in column B1) for the range containing the data to add. How can I specify these ranges (I can't name each cell individually as I have more than 30 cells to add up in my real life situation and the IF function allows selection of no more than 30 values)?
I've always been able to select non-adjacent cells in spreadsheets, but today noticed that I can't. I need to be able to do so to collect data from one spreadsheet to another for my monthly accounting reports. It makes collection of specific data fast and simple. Without this function, it takes far longer to do reporting and is very un-reliable. With the function working, I click on the cell ranges which are black, leaving the cell ranges which are red alone. We use black and red to identify charges which appear or do not yet appear on our monthly VISA statement.
I select all the black charges along with their corresponding account codes, then double-check the page before copying the non-adjacent ranges to make certain that all of the charge amounts and account numbers which are black on the page are selected and no red ones; then copy and paste the data into another sheet. It often makes collection very quick and easy.
When there are lines interspersed throughout a sheet with black and red charges strewn here and there, it is very easy to select only the black data and skip the red. Doing so by copying the entire range then deleting the red is laborious, and time-consuming.
What has happened to Excel's ability to select non-adjacent cells? Today, I can't select even two separate cells, using Excel's ability to do so by holding down the CTRL key, as I have in the past.
I'm using Excel 2007, on Windows Vista 64 bit; and I've made sure that my Insert function is turned off (thought that might change things?) and my Scroll Lock is turned off (I've bumped that many times in the past and found Excel doing weird things when trying to navigate a spreadsheet with the arrow keys).
If I write some VBA that selects a specific cell, for example the highest value in a coumn of data, how do I then copy a given (say 3) cells above and below (and including) this selected cell.
How would you select cell values from differing rows based on the name in an adjacent column. ie, if it says John, add that data to the source data, if not move on.
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
I am looking for a formula that searches a range of data and then returns the sum of the numeric values in the adjacent cells. I have a calendar style spreadsheet with 2 columns for each day. In the first column it has a list of names and the second column a number.
I would like a cell to return the sum of the numeric values next to each name. All the names are entered into the columns randomly and are unorganised. The same name will appear multiple times in the range and have different numbers next to it each time. I am currently using (for example) =COUNTIF(H6:AL14,"bob") to tally up the number of "bobs" but really what i want to do is find a formula that in plain english does:
Search a range of columns and where cell = "bob" take the value of the adjacent cell (to the right) and total them up!
i have attached an image - i hope it makes sense what i'm trying to do - i would like Q3 to total up the numbers to the right of all "bobs" across all the days - so it should return 42
I'd like to sum a range of cells if the date in the adjacent cell is last month, so... A2:A1000 holds the dates and B2:B1000 holds the values, i'm trying to figure out how to sum all of the values for the previous month (i.e. June).
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
I have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top. The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F), Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That 1. puts the name of the sheet I am copying from in column A 2. the deadline date in Column B (that date is always in C10 of each worksheet) 3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet Dim DestSh As Worksheet 'worksheet in which to paste summary Dim Last As Long Dim CopyRow As Long 'row to copy Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1) End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
Why the following code gives me an error. I have so much trouble with ranges, I use the syntax as it is prescribed. Here is the code. I will include some code lines underneath since I imagine they wont work either.
[Code] .....
I eventually will be turning the range statements into copy and paste instead of select. At the moment I am using select to make sure the proper cells are being selected.
How can I select a range of cells dynamically, not know how many cells down for two columns that I will need to select, there will be a blank cell at the end of the needed range.
I'm looking for a macro that only selects the unprotected cells in a range. So that I can copy a formula in these cells.
I found the below macro on the web but it will select all the unprotected cells in the worksheet. I guess this one can easily be adjusted to only select the cells in a range, but I do not know how to do this.
Code:
Sub SelectUnlockedCells() Dim WorkRange As Range Dim FoundCells As Range Dim Cell As Range Set WorkRange = ActiveSheet.UsedRange For Each Cell In WorkRange If Cell.Locked = False Then
I have come up with this bit of code by joining in two separate sets of code that I used earlier. What I want to do is this, for a given range I need to find each cell that has a comma and once that cell is found a formula has to be applied in a cell few columns to the left.. Here is the code that I am using..
Code: Sub Macro8() Dim i As Long For i = 1 To 1000
[Code]...
The sheet can be downloaded from here, I want column H to be (Col B / Col C) whenever Col A has a cell with comma in it. For this sheet col H needs to be populated 3 in places. The code above does it only for first instance.
I need to select data starting from cell 16B until 16K until the end of excel file... i will use this to export data into database later... I have attached a sample excel file with records needed to be selected
I have a spreadsheet which always contains data beginning in column A and through row H. However, there may be blank cell(s) in columns B through G. Therefore, if I use
Range(Selection, Selection.End(xlDown)).Select
it will not take me to column H if say column d has a blank cell. The same applies to my rows which always vary.
I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?
Private Sub CommandButton1_Click() Worksheets("Sheet1").Activate Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial End Sub it errors to: SELECT METHOD OR RANGE CLASS FAILED
I have a sheet that has 65000 rows and 3 columns, I need to load this sheet into a system that will only deal with 25000 rows at a time. How do you do a huge range select with Excel?
I need to select range a1:c24999 cut it out and paste it into another sheet. I have about 75 of these these sheets to do is there a way with a formula, a script or? to do these huge selects?
I am a Mac user, running MS office 2008 for Macintosh.
How do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
I am having a problem putting a range select statement in my macro. Here is what I'm trying to do. I want to select E1, then count down 10 cells below and copy. My sheet is filtered, so those rows are not continues. When I do the record a macro it highlights the rows and it looks like this (e10:e506, but it's only 10 items). I only want it to count down 10 cells and copy. Is there a statement for this.
I’d like to copy a range of cells: (A1 – A4) and paste them into Cell A5, but here’s the rub: I would like the values after I paste them to be separated by commas.
I have a column with rows from 41:254, the first cell contains a word, and the next is blank, and this repeats the whole way down.
a previous macro i run changes one of the cells in the range (one of the ones containing a word) to a number value, i want to write a further macro to select this cell that has now changed. when it changes it will be the only cell in the range with a number in.
