Copying Non-adjacent Selections To End Of Range
Jun 21, 2006
I am using code that will copy several formulas in adjacent cells to the end of range; however, when I try to use it to select and copy formulas in non-adjacent cells, I get an error. Tried to modify, but I'm new to VBA...still learning. Here is the code that I currently have.
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("D3").Formula = "=RC[4]/RC[3]"
Range("F3").Formula = "=RC[-1]*RC[1]"
Range("J3").Formula = "=RC[-1]/RC[-3]"
Range("D3,F3,J3").Copy Range("D3,J" & LastRow)
View 6 Replies
ADVERTISEMENT
Jul 19, 2013
I am working on a macro, which should cycle through a column and find all rows with a given value. Then for these rows it should paste values from other columns into another worksheet.
Example:
1 2 3 4 5 6
Find all cells in row 6 with "yes" and paste the value from row 3 into another workbook.
Issue:
I cant seem to get the the value from row 3.
ActiveWorkbook.Worksheets(datasheet).Cells(rowtocopy, 6).Value.Copy has an error. Currently "Object required". Ive tried: .cells().value.copy that gives me a different error.
Script is started in the workbook where the extracted values should go.
Below its called "Template (2)".
The workbook with the rows is called "Datasheet" (which may be "Volume" or "SNiC data" depending on what is written in Worksheets("Template (2)").Range("B4").Value
Code:
Sub copychanges()
'################ EXTRACT ORIGINAL PAYMENT DATA
originaldata:
Dim currentwb As String, SuppXls As Excel.Workbook, datasheet As String, i As Long, targetcolumn As Range, modcorrect As String, fromcolumn As Range, rowtocopy As String
currentwb = ActiveWorkbook.Name
SuppPath = ActiveWorkbook.path & "" 'file path (assuming all file are in the same folder
SuppName = Worksheets("Template (2)").Range("D4").Value 'filename of corrected file
[Code]....
View 2 Replies
View Related
Feb 23, 2009
I am trying to write a routine (via two comboboxes) that will enable users to select a choice of charts from one of three possible worksheets and have the charts on the selected sheet copied to PowerPoint. My difficulties are these:
a. I cannot determine how to pass the selections to Excel (see b. as well)
b. In some instances my charts are actually groups of a chart and a picture. how I should reference these? They are obviously not ChartObjects: when I use the macro recorder all I get is a range.
View 9 Replies
View Related
Jul 7, 2013
I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.
VB:
Sub SwapGroup()
If Selection.Cells.Count < 8 Then
MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"
[Code] .....
View 4 Replies
View Related
May 8, 2014
I have a spreadsheet which has a source worksheet, containing 2 columns with thousands of client numbers and account numbers. I have a destination worksheet which contains certain account numbers which are featured in the source sheet.
What I need to do is pull the matching client numbers across from the source sheet into the destination sheet. An example is attached.
View 5 Replies
View Related
Nov 13, 2006
If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.
View 9 Replies
View Related
May 26, 2006
In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc.
I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.
View 4 Replies
View Related
Feb 2, 2014
I have a spreadsheet with temperature data, all in one column (D), that was recorded every 10 minutes for several months. In two adjacent columns (E & F), I would like to record the daily max and min temperatures. I can manually do this with MAX and MIN formulas in column E and F respectively, MAX(D1:D144). But when I copy this formula to the next row, I get MAX(D2:D145) when what I really want is MAX(D145:D288)....and so on...
View 1 Replies
View Related
Jun 6, 2012
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
View 2 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Nov 5, 2013
I am trying to use defined range names in a vlookup function. The target ranges contain non-adjacent columns. The formula wizard gives me an error msg "#value" and the vlookup returns #N/A.
View 2 Replies
View Related
Jul 30, 2008
I only want to select the adjacent range from
Range("A2", Range("A2").End(xlDown)) to the left
View 11 Replies
View Related
Jan 2, 2014
I want a cell to countif the sum of two adjacent cells is greater than the number in another cell so for instance
Count if f101+g101 is greater than the number stored in cell u12, repeat this for cells f102+g102, right down to f50100+g50100.
View 2 Replies
View Related
Jun 29, 2006
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)?
View 12 Replies
View Related
Jan 11, 2010
I have three columns (A to C) with contineous data (# rows always variable).
I would manually type a number in a cell (S2) that would correspond with a number in Column A. What I would like the code to do is that it finds the number in column A and selects the whole range in column B and C starting from that row, and copies that whole selection to N10.
After that all the data in column N should be substracted with a value given in S3 and all data in column O with a value given in S4.
View 9 Replies
View Related
Apr 17, 2009
Split from: Clear Range To Right Of Active Cell Down. I was working through this the first step was to clear the contents of the cells. After they are cleared, I have another sub that then goes and retreives a unquie number and places it back at the top of the column that I just cleared. Would this same code that cleared the cells contents work to copy the contents of the active cell down that same column, until the cell to its right is blank? -R-
View 5 Replies
View Related
May 16, 2012
I am new to VBA and have tried to develop the code for finding a specific stock symbol (Column A) from over 4200 symbols from a downloaded csv file, copy the data in in its (specific symbol's) row (in the next 5 columns B to F) to the 3rd blank row in the master workbook (data arranged from Bottom to Top). In the Master workbook each stock symbol has a worksheet with its symbol as the tab e.g. BHP.AX is the tab or sheetname for BHP stock in the Australian Stock Exchange. The downloaded file comes from a subscribed site EODData which provides daily OHLCV (Open/High/Low/Close/Volume) data against each symbol.
My attempt is shown below and it is very primitive. It does not work! I wish to run it from the Master workbook (name:-0PortfolioASXMultipleIB.xlsm) and not from Csv Data file.
VB:
Sub Macro0CopyFromCSV()
'Insert a blank row and format it in Master workbook
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[Code] ......
View 9 Replies
View Related
Jun 23, 2009
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
View 2 Replies
View Related
Jul 25, 2012
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).
View 7 Replies
View Related
Jul 19, 2006
When i press say a command button " Add Repair Information" i want the User to Enter the serial number which i have given it a range called SerialN from the excel sheet and enter a repair action. Is there a function in Excel which will allow me to search the Serial Number and allow me to put the Repair action across in the cell on the same line?
View 5 Replies
View Related
Jun 21, 2007
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:
Sheet1
cup Y
bowl Y
spoon
fork
knife Y
Sheet2
cup
bowl
knife
..not..
cup
bowl
knife
View 4 Replies
View Related
Jan 5, 2014
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.
View 2 Replies
View Related
May 8, 2008
I'm trying to use combobox1, say, department, to filter items listed in combobox2, say, employees. I don't want all employees from different departments listed in the combobox2 so as to make the user spend time looking, but only names of a particular department once that department is selected in combobox1.
View 9 Replies
View Related
Jun 25, 2014
Median Indirect: I would like to find the median in a range of cells and then bring back the 2 adjacent cells
Range...............Adj Cell 1............Adj Cell 2
1.......................L1......................L2
2.......................P3......................P4
3.......................O9......................010
So the median of the range is 2, thus we would want the formulas to bring back P3 and p4
Column 1..............Column 2
P3...........................P4
View 12 Replies
View Related
Jan 30, 2014
I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.
The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.
For example.
AI:AI contains a list of ID's which will be manually input every day so they might have a different order.
C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.
The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.
I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.
View 5 Replies
View Related
Jan 20, 2010
I have a spreadsheet set up that keeps a running total of the money in my checking account. I've set up the "balance" column to only display the balance if the "debit" or "credit" columns have data. For example:
credit debit balance
3.00 12.00
2.00 10.00
5.00 15.00
______________
15.00
This way I have space to add more entries, without having to copy the formula in the "balance" column every time I add an entry (or have my balance copied all the way down the column when there are no entries that alter it). However, the problem I'm encountering is displaying the balance at the very bottom (underneath the line). I would imagine I need to somehow look up the last value in a range in the "balance" column, but am unsure of how to proceed.
View 4 Replies
View Related
Aug 29, 2008
I would like to work with a range of cells.
I have a headerCel (A2)and a footerCel (A20).
If I use the line
View 9 Replies
View Related
Jul 29, 2014
i am trying to copy few cells with data, and i have something like this:
Range("A3:AO" & Format(intRowCounter, "###")).Select
So this will copy all data withing those cells range, however, i want to copy only data from specific columns, ie, from column A3, and from K3:J
View 1 Replies
View Related
Apr 16, 2006
how to make such code by VBA. but i'm desperate to have it.
I need a code to copy a two different ranges from two sheets and paste them below the original ranges in each sheet "a range a sheet". And then if a clicked again the same ranges should be pasted after and so on so forth.
View 3 Replies
View Related
Mar 17, 2014
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
View 2 Replies
View Related
