Concatenate A Range Rather Than Click Each Cell
Feb 3, 2009Can you concatenate a range. I want to join 30+ cells together but dont want to click on every one?
View 10 Replies
ADVERTISEMENT
Concatenate From A Range To A Single Cell
Jan 18, 2011Here is the set up:
I have a calendar sheet where the dates go down the rows, and there is one column per member of staff. The staff is split in to three streams (different apps to cover, etc).
In the calendar, we mark a "C" for when someone will be on-call, R for when they will be doing a release, and so on.
In a separate sheet in the workbook, rather than having people look at the whole calendar of 30+ people to see who is on what shift for a given stream, I have an On-Call Rota where the rows are again the dates, and the columns are the streams. In each cell of this sheet, there should be the names of all the people of that stream who will be on-call for that date, so a concatenation of the actual staff members from the calendar, separated by CHAR(10).
I have been unable to find a combination of INDEX, MATCH, LOOKUP, VLOOKUP, etc, etc that gets me past the main problem: they only ever match the first C (or the first R for the On-Release Rota). I can't get a formula for a single cell in the rota that will return all the names where there is a C in the calendar in such a way that I can concatenate them.
Here is the kind of thing I have had to do .....
Go To Range On Cell Click
Dec 1, 2006I have a spreadsheet with many worksheets in it. Sheet ' Names' has personal data for a person. Sheet 'Credits' has more information about that person's work.
When a cell in sheet 'Names' is clicked, the user is forwarded to cell containing the same name on sheet 'Credits'. I know how to do that with hyperlinks, but its not going to work becuase the list of names is updated.
1. How do I make the Sheet 'Names' react to clicking of the cell? Do I use procedure 'Worksheet_SelectionChange()'?
2. How do I switch sheets to highlight the cell on sheet 'Credits'?
3. How do I compare values in cells that are on 2 different sheets?
The code to highlight the cell is this, i think:
For x = 3 To <some number>
If Cells(x, 1).Value = <value of cell In sheet 'Names'> Then
Columns(x).EntireColumn.Activate
x=x+1000000
End If
Next x
I know this may seem trivial, but I am very new to VBA.
Run Macro On Double Cell Click If Cell Part Of Named Range
Feb 6, 2008I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
View 2 Replies View RelatedRun Macro When Double Click Any Cell In Range?
Feb 7, 2014I want to run a macro any time any cell in a range is double clicked. I can make it work with just on cell, but not any cell in the range.
View 3 Replies View RelatedDouble Click Calendar In Cell Range Only
Feb 4, 2007I've been reading through the forums, but do not know how to double click to get the calendar pop up,only in a certain range (e.g. Cells G5:G85).
I already have the module: frmCalendar
Running Office 2003
Click To Copy Cell Within Range To Destination Cell?
Oct 18, 2013My goal would be to click on a cell within a range of cells (in a column) and have the value copied to a specified destination cell. There would be a few different columns with source cells and two destination cells. Each column would to copy to a specific cell. My ojective is basically to deal with different processing times (days, weeks, months).
Here's a little visual, clicking on C4 for example would copy the value "5" to cell B2. Clicking on D4 would copy "12" to A2.
row/col
A
B
C
D
E
1
Days
Weeks
Processing time X (weeks)
Processing time Y (in days)
Processing time Z (in weeks)
2
3
10
110
3
4
11
111
4
5
12
112
5
6
13
113
Macro To Right Click A Cell And Select Item From Right Click Menu?
Feb 7, 2014I wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedVBA To Concatenate Variable Range
Oct 24, 2012I have a problem with a worksheet that my company accounting system exports every month.
Attached : sample of the worksheet.
In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title - if it begins with "Cost Center"
2- Check for the Title - if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"
The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ). Which led to wasting many hours , and high risk of error while copying and pasting formulas.
Concatenate A Large Range.
Jun 19, 2009I have a column of numbers in A1:A200 that I need to concatenate with a ";" in between each into one cell. Is there a way to do this without clicking in each cell individually? =concatenate(A1:A200) just returns the value in cell a1.
View 2 Replies View RelatedConcatenate A Range Of Cells (A + B + C = ABC)
Sep 29, 2009I know how to do the "normal" type of concatenation (concatenating?), but I was wondering if there was a way to concatenate a range of cells.
If my first three cells look like this:
A1 = Adam
A2 = Bob
A3 = Chris
I'd like cell A4 to say "AdamBobChris". I'm not worried about punctuation at this point. I thought I could just do =concatenante(A1:A3), but that's not working.
How To Concatenate A Variable With A Range
Feb 28, 2008Following advice from this forum I have developed the following code to save a workbook with a filename made up from Ranges within a worksheet. What I had also hoped to do was include the first 2 letters of a Range.
The code that works is as follows
Concatenate A Range Skip Blanks
May 22, 2009I have copied the following codes to create a Function of "Concatenate a Range Skip Blanks". However, I humbly seek help to add "," in between every word and also take away the empty space when there is only 2 words are concatenated. Pls refer to my attachement for easy understanding.
View 6 Replies View RelatedVBA Concatenate Email List Range
May 25, 2012I get the correct results in K1 when I have a list of email addresses in column D, but when it tries to email it states, "Unknown recipient name found in the list of recipient list. Use a valid name and try again."
Code:
Dim x As String, rng As Range, cel As Range
Dim myString As String
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)
[Code] .......
Extra Separators In Concatenate Range
Feb 7, 2007Return a list of each " case" that includes "item X" (both cases and items are stored as text). Each "case" has a list of associated items, which are stored in another worksheet. "Item X" may be associated with many cases (e.g. not unique to a single case).
The results should look something like this (item in one column, concatenated list of cases in another column): ....
Run Macro When Click In Range
Apr 12, 2008I want a macro I have made to run when I click in a particular range.
The macro I want to run is named: SortSizes
I want the macro to run when I click any cell in the range: A12:A4000
with some code and instructions to do this.
Go To Range Name By Double Click
Jul 28, 2014I have this list of all rangenames in my workbookin column A:A
For some reason I want to doubleclick on the rangename in the list to activate this range.
The reason is that I want more smooth activation-prosess; The Excel Range List is too long to practical use as a direct lookup, so I want to activate directly from my own list (where i have added definitions to the range names.
I have tried to build a hyperlink formula, but failed. Maybe the macro below can be modified to do the job, but I am stuck here as well.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim RangeName
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
[Code] ...........
Run Macro When Click In Range
Apr 12, 2008I want a macro I have made to run when I click in a particular range.
The macro I want to run is named: SortSizes
I want the macro to run when I click any cell in the range: A12:A4000
Double Click Range
Aug 31, 2009I have a code for doubleclick on a cell and a gdt application chart will open.
Private Sub Worksheet_BeforeDoubleClicK(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect
ThisWorkbook.Unprotect
Dim i As Integer
Dim gdt As Long
Dim S As String
Cancel = True
With Application
If Target.Address(False, False) "C11" Then Exit Sub
'Place your direct path to open this GDT application
S = "C:Documents and SettingsUserMy DocumentsAndy's MachineINSPECTIONGDTGDT BitmapGD&T_Font.exe"
If Dir(S) = "" Then
MsgBox "File does not exist:" & vbCrLf & S, vbCritical, "Error"
Exit Sub
End If
gdt = Shell("""" & S & """", vbNormalFocus)
End With
ActiveSheet.Protect
ThisWorkbook.Protect
End Sub
What would the code be for a range of cells if doulbe clicked the range would be C11 thru C35
Also, I have a double click code for another symbol chart to open.
How, would this be used in the same manner if a coulmn or row were to apply on double click.
Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List
Oct 6, 2007I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF
Aug 11, 2013Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
RefEdit Control To Click On The Desired Range
Jul 8, 2009I am using a refEdit control on a userform to get a range from the user (they use the refEdit control to click on the desired range). My problem is that the user typically has 3 or 4 different workbooks open and if one of them is maximized withing Excel, the user is unable to get to them to select the range (only the maximized workbook is displayed and usable). Is there a way around this?
View 4 Replies View RelatedRange To Work On The Worksheet Before Double Click
Jan 19, 2010I am encountering is that with my knowledge I can only get one range to work on the worksheet_before doubleclick function and thats the sort range.
I need several different ranges with each range giving a target value uinique to that range.
Excel 2010 :: Highlight Range And Right Click Greyed Out
Apr 4, 2014I'm using Excel 2010 and when I highlight a range and right click 'name a range' is greyed out. I can bypass this by going to the formulas tab and defining a name from there.
Anything I can check to see why this is not an option for me?
Add Controls To Right Click Menu Based On Range List
Sep 28, 2007I have customised the right click menu's for this spreadsheet and currently it is all hard coded. I was wondering if it is possible to dynamically modify the right click menu? The code currently has right click buttons for each staff member, and when clicked other actions are performed. I have added a "Staff" sheet and was wondering if there is a way the code can reference that sheet and create the list based on those entries for example, when new staff join or other staff leave?
I'm sure that there is a better way to go about this, perhaps a For... Next loop but I don't know enough about it. In the mean time, I will keep bashing away at it in hopes of a brainwave... it's a Friday before a long weekend and I think my brain has decided its holiday time.
Option Explicit
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
. CommandBars("Cell").Controls("Add Nick").Delete
.CommandBars("Cell").Controls("Add Toby").Delete
.CommandBars("Cell").Controls("Add Ben").Delete
.CommandBars("Cell").Controls("Add Matt").Delete
.CommandBars("Cell").Controls("Add Zoe").Delete
.CommandBars("Cell").Controls("Add Anne").Delete
.CommandBars("Cell").Controls("Add Craig").Delete
.CommandBars("Cell").Controls("Add Unknown (1)").Delete
.CommandBars("Cell").Controls("Add Unknown (2)").Delete
.CommandBars("Cell").Controls("Remove").Delete
End With
With Application
.CommandBars("Cell").Controls("Cut").Visible = True
.CommandBars("Cell").Controls("Copy").Visible = True...............................
Click Button To Rename Sheet Tabs Based On Range
Jul 20, 2006I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc).
What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
If / Concatenate Into One Cell
Nov 14, 2008I have 6 columns:
A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2
I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).
So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")
Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".
Click On Color Button And After Button Click It Will Color Entire Row Of Cell?
Jan 23, 2014what code I can put behind a button so that once I click on that button,
What ever cell i click on, the whole row of that cell will be colored a certain color.
Is this where i would used a target approach?
Concatenate IF Cell Is Not Empty
Dec 4, 2013I have been sent a very large file of product data from one of my suppliers, They sent all of the details in separate fields (for example, colour, height, width, depth, material etc)
I have processed all these columns to appear how I want them, but now need to join them together so they can all be added to my main description. I want each one to be on a new line when it's displayed on my website, so I'll be adding a <br /> tag (break tag) after each item. This I can do no problem.
This would all be fine, if all the columns contained data, but a lot of the time the columns do not contain data (eg. I have a column for "knife length" but not all of my products are knives, so not all of them require this information"
for example, I have something like:
SKU | Colour | height | width | depth | material
ABC | blue | 50mm | 10mm | 60mm |
BCD | | 80mm | 75mm | 30mm | wood
CDE | red | | | | plastic
I could use something like :
=CONCATENATE($A2, " <br /> ", CHAR(10),$B2, " <br /> ", CHAR(10),$C2, " <br /> ", CHAR(10),)
This however means writing a piece of code for each cell I want to join, My cells go from range "O2" to "DW2", I don't fancy writing that for every single column!! Also, it means that If I have some empty fields, it will still add a break tag, this will look very odd on my site.
I really need a way of writing into the function "include if cell contains data" for each cell. If it doesn't contain anything, skip it and move to the next. (the char(10) in this just gives me a line break in excel so I can see what it'll look like when it's exported)
Concatenate Based On Cell Value
Apr 29, 2009Using Excel 2007. Can I use IF along with CONCATENATE.
=if(h26<.0099(concatenate(ak26,AK27))if(h26>.010(concatenate(ak26,aQ26,ak27))
Can't seem to get the proper syntax.