Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers
I am trying to select a range based on two variables which store the column numbers. what I have is:
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Select Dynamic Range Based On Non-empty Cells
I require code to identify the last row in column 'A' that contains data, and then to select every row up to that one, and each column up to 'H'. My data begins on row 3, and the rows with data varies from row 7 through 120. The columns with data is constant so there is no need to test in that direction.
View Replies!
View Related
Select Range Using Offset With Variables
I am trying to select a range in VBA, where I know the name of the starting cell, and the size of the range are values returned earlier in the VBA function. I guess the pseudocode I am trying to do is: range(range("StartCell"):range("StartCell").Offset(x,y)).Select. Obviously this code doesn't work, but I don't know the correct way to select this range
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
Store Variables Selected In List Box Form
how to store the variables selected in a list box for future use in the macro? I've got code as follows: This is in my main module 'These set up the global variables Public SelectedRegion As Variant 'I've also used string Public NewTabName As Variant 'I've also used string 'This links the list box to the values in a hidden tab named "Regions" and shows the form WSForm.RgList.RowSource = "Regions!A1:A10" WSForm.Show This is in my coding for the form itself (list box & buttons) 'Code for my "Cancel" button Private Sub cmdCancel_Click() Unload Me End Sub
View Replies!
View Related
Cycle Through Whole Numbers For Three Variables Whose Range I Can Define
I need it to cycle through whole numbers for three variables whose range I can define. I need this macro to test the result of all possible combinations of A, B and C, as is shown in the attached file. The initial range boundaries for each variable are as follows: VariableABC Minimum1408 Maximum510012 So the macro should start with the following combination (1,40,8) then (2,40,8) then (3,40,8) etc until it reaches (5,100,12). I need the results for each combination to be posted at a separate sheet next to the corresponding combination.
View Replies!
View Related
Numeric Variables For Row & Column Numbers
I recorded a macro to perform a graphing operation which will be used in a For-Next Loop which will run eight times. I need to modify the “ range” portion of one of the recorded lines to allow the graphing data to be dependent on variables I have set up as integers. See the applicable part of the recorded code below. Dim firstrowno As Integer Dim ltcol As Integer Dim lastrowno As Integer Dim rtcol As Integer ‘code which sets the values of the above variables Is Not shown ActiveChart.SetSourceData Source:= Sheets("PLOTS").Range("AN27:AQ33"), PlotBy _ :=xlColumns How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?
View Replies!
View Related
Find Value In Cells, Merge Found Cells To One Column
I need to mine a huge spreadsheet of about 30,000 cells and make columns from specific words. As in. I need to search for all words "table" even if the word is "My_table" or "aTable" and suck them into one column. I have to do this one several words. Let me know if you have the solution. I am trying to write something but I am unsure of the syntax at this level.
View Replies!
View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this? If Sigma = 0 Then Selection.EntireRow.Insert ' New row for new entries ActiveCell.Value = "NONE" ActiveCell.Offset(1, 0).Select End If
View Replies!
View Related
Sum 1 Column, Based On 2 Variables
A B C D COL 0 COL 0 1 COL 0 COL 0 1 COL 0 EDM 0 I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
View Replies!
View Related
Sum Column Based On Two Variables
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View Replies!
View Related
Sum Of A Range Based On Two Variables
I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates. Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.
View Replies!
View Related
Prompt To Select File/path And Store As Variable
I am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command. The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.
View Replies!
View Related
Merge Adjacent Cells At Each Change In Column
I have a list of values, some are like values, others are not. is there a way to have the column to the right automatically merge the cells which have like information to their left. Attached is a sample file. I want to be able to have the cells in column B automatically merge based on the values in column A. I'm not sure if this can be done, but figured I would ask before I spend hours merging cells.
View Replies!
View Related
Macro For Populating Cells Using Variables From Another Column
I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.
View Replies!
View Related
Defining A Range With Variables And Cells Function
I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select Column Based On ListBox Choice
If I've Listbox1 in "ABC.xls" which contain selected headers of workbook "XYZ.xls". How can I select entire columns in workbook"XYZ.xls" when press enter at Listbox ? Do I've to put the code here ? With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then ? ? ? End If Next i End With
View Replies!
View Related
How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet. here is what I have. Sub clear_data() sheets("sheet1").select lr = activesheet.usedrange.rows.count Here is where I get lost in translation (syntax). I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change. I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Select Last X Used Cells In Column
I would like to select the last few cells in a column, however the number of rows is not fixed, it changes. This can only select the fifth last cell: Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(-5, 0).Select Ultimately, i want to select the 5 cells from the fifth last to the last: from this: Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(-5, 0).Select to this: Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(0, 0).Select If possible, with the cells selected, can i do something similar to this: Range("B62:B67").Select Selection.AutoFill Destination:=Range("B62:B114"), Type:=xlFillDefault Range("B62:B114").Select The underlined parts is the part which make me headache. How do i solve this?
View Replies!
View Related
Vba Select Rows Based On Info In A Column, Then Paste Into New Workbook
I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable? I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.
View Replies!
View Related
Randomly Select Cells Based A Couple Rules
I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters. So what I am doing is having excel designate equal amounts of playing time for each player each game. Is this possible. Can I base this off of previous games?
View Replies!
View Related
Select A Range Of Non-adjacent Cells
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 Replies!
View Related
Allow User To Select A Range Of Cells
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?
View Replies!
View Related
Select Blank Cells In Range
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.
View Replies!
View Related
Loop To Store Data From A Range
I am trying to do is use a loop to store data from a range into a 3 or 4 dimensional Array and then output the data from the Array in another range. so for example the i want loop through the data in this range and store all data that is in account 701 into an array and then output this information in another range (tab). The data would have several different account but I only want to see one at a time. Account Price Amount 701 150 1,000,000 701 125 250,000,000 701 3.25 6,000,000 702 4.25 25,000,000 702 2.35 3,600,000 702 2.55 10,000,000
View Replies!
View Related
Select Huge Range Of Cells - A1:c24999
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.
View Replies!
View Related
Range Select Then Count Down 10 Cells Below And Copy
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.
View Replies!
View Related
|
TRACKER
