Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

View 4 Replies


VBA Select Range To Copy Based On Two Variables

Jan 13, 2014

I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.

For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.

What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between

Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long


View 3 Replies View Related

Automatically Select From Dropdown Based On Variables In 2 Other Cells

Jan 6, 2013

I have a sheet where I have a "Status" column in each row which could be "Waiting for Band", "Waiting for Organizer", or "Ready to Go!". These options are set as a data validation list. I have 2 cells later in each row where the response can only be "Yes" or "No". I want for the Status column to be set automatically based on the responses in the later two cells. So for example

where C3 and M3 are Yes, the status column in B3 should read "Ready to Go!"
where C3 is no but M3 is yes, the status column should read "Waiting for Organizer"
where C3 is no and M3 is no, the status column should read "Waiting for Band"

The above is actually an exhaustive list of all of the potential outcomes(!)

View 4 Replies View Related

VBA To Merge Cells Based On Other Column Values?

Mar 19, 2014

Now I know that merging cells is usually not a good thing in VBA, but I'm working on a data report that just looks bad unless longer pieces of text can span several cells to minimize column width.

So here's the problem:

I have a column of 1's and 0's in columns E and F, and text in Column G. I want to merge the cells in columns G and H for that specific row if there is a 1 in either the E or F column for that specific row. And I need to do this for a long range...rows 7-5000. Any ways that VBA can do this?

View 4 Replies View Related

Merge Cells Based On Used Cells In Column 5

Jan 7, 2014

I have data written in Cells B5, C5, D5 etc that changes as you add more column data

I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.

View 6 Replies View Related

Select Cell With Numbers In From Range Of Cells Containing Words

Mar 26, 2014

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.

View 4 Replies View Related

Detect Numbers In A Cell Range And Then Change Cells Based On What Numbers Are Found?

Feb 27, 2014

I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.

What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.

View 7 Replies View Related

Select Range Based On Last Row And Last Column Used

Aug 12, 2008

I have defined my last row and column by using:

lr = Sheets("week1").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("week1").Cells(1, Columns.Count).End(xlToLeft).Column

Now how do I select the range starting at A2 through the last used column ( LC ) through the last row ( LR )?

View 9 Replies View Related

Split Range Of Numbers From One Cell Into Column Of Multiple Cells All With Individual Numbers

Mar 5, 2012

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

View 4 Replies View Related

Select Range Using Offset With Variables

Apr 23, 2008

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 2 Replies View Related

Select Column Or Range Based On Current Time

Nov 18, 2013


Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone

What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc

View 2 Replies View Related

Using MAcro Recorder To Store Variables?

Jun 22, 2006

Is it possible to use the Macro recorder in Excel to store user variables that they put in through a UserForm? So say when a textbox on a userform is changed, it records a macro of what is put into the textbox, which it can then call to retrieve the value.

View 4 Replies View Related

Select Cells Based Value In Adjacent Column

Jan 4, 2008

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.

View 5 Replies View Related

Select Dynamic Range Based On Non-empty Cells

Aug 7, 2009

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 2 Replies View Related

Select Range Of Cells Based On UserForm Input Value?

Jun 18, 2012

So I'm creating a UserForm that takes user input to create a worksheet based on the values input. My question is: How to select a range of cells based on a variable input value? For example, it asks how many competitors there are, and the user can input 5, 8, 10, etc. So I want that many cells in Row 1 selected, whatever the value input is.

View 9 Replies View Related

Select A Contiguous Range Of Cells In A Column

May 7, 2009

I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:

View 5 Replies View Related

Store Barcodes In Range Of Cells In Excel?

May 2, 2006

If I want to store bar codes in a range of cells in Excel, how do I format the cell so the barcode appears as scanned in.

We are scanning in Barcode from our inventory to our excel stock book.

I am doing it now but when we export it out or link it to an access table, it either does not appear or is truncated.

View 4 Replies View Related

Store Variables Selected In List Box Form

Jun 13, 2008

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"
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 9 Replies View Related

How To Select An Entire Range Of Non-Contiguous Cells In A Column

Aug 19, 2009

I recently found this code for selecting a whole column of non continuous cells.

How can I change the "a1" & "a65536" so it can work and be activecell instead?

View 9 Replies View Related

Numeric Variables For Row & Column Numbers

Mar 2, 2008

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 _

How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?

View 5 Replies View Related

Cycle Through Whole Numbers For Three Variables Whose Range I Can Define

Dec 1, 2006

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:


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 9 Replies View Related

Fill Range With Sequential Numbers Based On Corresponding Column Groups

Feb 15, 2010

In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?

View 2 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

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 2 Replies View Related

VBA To Get Average Of Range Of Cells Based On Named Range In Different Column

Apr 10, 2013

I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.

I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.

View 1 Replies View Related

Concatenate Varying Numbers Of Cells Based On Duplicates Found In Separate Column

Jul 25, 2011

I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.


1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B

Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.

View 2 Replies View Related

Prompt To Select File/path And Store As Variable

Jan 29, 2009

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 6 Replies View Related

Sum Of A Range Based On Two Variables

Jun 18, 2009

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 2 Replies View Related

Merge Range Based On Active Cell Row

Jul 27, 2007

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 4 Replies View Related

Sum 1 Column, Based On 2 Variables

Dec 9, 2009

COL 0 1
COL 0 1

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 9 Replies View Related

Sum Column Based On Two Variables

Jun 28, 2007

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 8 Replies View Related

Copyrights 2005-15, All rights reserved