Macro Or VBA To Concatenate Or Automate Ampersand Use?
Jul 10, 2014
I have got the general principles of macros and VBA downpath.
Excel Example.xlsx
The document I'm working with is confidential, so this is an example. Basically, I'd like to string together the content of all cells from the second column associated with each company in the first column (separated by comma + space). In the original document, the companies are located in B2 to B8486, and the key words are located in D2 to D8486. My example uses ampersands, but I seem to understand Concatenate would yield the same results.
View 9 Replies
ADVERTISEMENT
Sep 11, 2013
I have worked out how to get my macro behaving the way I want however now I would like to automate the whole process. Basically I would like the macro to work its way down a column (in this example column F) until it has completed and there is no more data in the row before the last.
Here is a code snippet of my macro
Code:
Keyboard Shortcut: Ctrl+a'
If ActiveCell = 2013 Then
ActiveCell.Offset(, 2).Range("A1:E1").Select
Selection.Cut
ActiveWindow.SmallScroll ToRight:=24
ActiveCell.Offset(0, 24).Range("A1").Select
ActiveSheet.Paste
End If
[code]......
View 1 Replies
View Related
Feb 15, 2014
I am trying to write a small macro involving Len and Left formulas. This will take input from two cells and and give the value on a third cell. I know how to do this on excel. This goes as follows:
Value on A5 = HB_AM_1
Value on B5 = 1
My formula to extract the specific text =LEFT(A5,LEN(A5)-LEN(B5)-1).
My result on D5= HB_AM
Here, my inputs are clearly defined and I can write this formula on cell D5 to get the result. I can copy this down to the end of my data and I can get my result.
My challenges: I need to do this for various reports where the data extends to various rows. I think looping will solve the problem, but not sure how to do it. The value on cell B5 will always show the last number at the cell A5 (1 in the eg above). So, if the number in A5 is 10, value in B5 will also be 10. This is already there in my data, so I do not worry about this part.
View 5 Replies
View Related
May 20, 2008
I have a stock sheet of about 1500 items that is printed every 3 months for stock take.
The changes are then entered manually. I have set up a stock order sheet using VLookup and manually enter the items that need to be ordered. I'm wanting to know if i could use a macro to do this automatically and check only for the stock items that have changed (ie. items have been sold ). Stock levels needed on hand are usually constant but is changed from time to time so will have to be taken into consideration. I have included an example worksheet which would better explain what i have been doing so far. Also if possible could the macro generate a new worksheet for the order sheet and name it the corresponding date and month of generation.
View 11 Replies
View Related
Oct 6, 2008
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
View 14 Replies
View Related
Oct 27, 2011
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
View 4 Replies
View Related
Oct 4, 2007
I've recorded a macro which selects "1" in a filter drop-down box then prints some pages, then comes back and selects "2" in the filter then prints some pages, etc etc etc.
I've looked at the code for this and it treats the "1", and the "2" etc as text each time - originally I had the filter on names, but the names change each time I need to do this whilst the process doesn't, so I filtered on numbers instead.
However, I would have to enter the repeated blocks of code down to 1000 to get it to select down to "1000" in the filter. About 3/4 lines each time but with me manually typing in 1, 2, 3, 4 .... 999, 1000.
Is there a way to say in VBA 'repeat filter selection until you've run out of numbers then come back to "All" and stop ?
View 9 Replies
View Related
Oct 1, 2008
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
View 9 Replies
View Related
May 29, 2007
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
View 3 Replies
View Related
May 3, 2014
I've got a problem with copying cells from table A to B.
A short insight of the situation:
Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.
I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.
I've added an xlsx file with more detailed description.
Example_WHS.xlsx‎
View 8 Replies
View Related
Nov 5, 2008
I have a spreadsheet tool that I use to calculate values on a set of variables, based on values on an initial set of other variables, for a large number of cases.
The calculations require a number of sequential steps to be repeated to get the calculated values for each case, and I am hoping there is a straightforward way to use a VB macro to automate this process.
Essentially, these steps are required to calculate values for a single case:
1. A data input sheet holds the values on the initial/input variables for each case. The first step involves copying the string of values for the case on the input variables into a sheet where the calculations are done.
2. As soon as the input string of values are pasted into the calculation sheet, the calculation formulas generate values for the case on the new, output variables.
3. The output variable string is then copied from the calculation sheet into a “results output” sheet. This copy/paste procedure needs to offset from the first row on the output sheet, so that the row number where the values on the output variables are stored correspond to the case ID. For example, case ID 10 is stored 10 rows down from the first row (i.e., on row 11). Case ID 100 is stored 100 rows down from the first row (i.e., on row 101).
As there are a very large number of cases, I need to have a “control panel” in place, where I can indicate the range of case IDs that will be processed. This takes the form of two cells – one indicates “From ID” and the other “To ID” (for example, “From ID” 1 “To ID” 100). The macro then uses this information to loop through the calculation steps outlined above for these case IDs.
View 4 Replies
View Related
May 17, 2007
Is there a way i can dictate to Word from an Excel macro that i want it to open, Start a mail merge, use a pre-made file as a template, use the excel file the macro is in as the source, get it through the whole setup of the Mail merge all the way to the print function?
View 9 Replies
View Related
Sep 16, 2006
I have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4. I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially:
1. open A1
2. run a Macro in A1
3. close and save A1.
4. open A2
5. run a Macro in A2
...
12 close and save A4.
The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A. It would be great if start Excel, open Main, and run the Macro in Main fully automatically.
View 3 Replies
View Related
May 8, 2007
I'm looking for information on building a macro that will automate making copies of a spreadsheet into another workbook that contains very particular information. My master sheet has lists of my customers and the rep's that service them. What I need to do is have macro buttons for each rep. that will pull all the shops that they service and all information for these shops into another spreadsheet.
View 9 Replies
View Related
Jan 23, 2013
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
View 4 Replies
View Related
Oct 6, 2007
I 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
View 5 Replies
View Related
Aug 11, 2008
I'm trying to write a macro concatenate a report but the report outputs data on different rows when ideally I'd like it to all be on one row. The C column will have something in on the top line but blank for the rest until it gets to the next set of data.
Unfortunately it's not always the same amount of lines every time.
View 9 Replies
View Related
Sep 10, 2009
I have the following example values in Column A and I want to concatenate each unique combination to a list.
A
B
C
D
Example necessary output:
A vs. B
A vs. C
A vs. D
B vs. C
B vs. D
C vs. D
Notice there are no duplicates (e.g., A vs. B / B vs. A). I have found many macros that will create all the duplicates but none that will create only unique.
View 3 Replies
View Related
Dec 9, 2008
I have some records. I want to merge them through macro in a singel cell, like I have selected a range then I want to merge them in active cell with a comma.
View 9 Replies
View Related
Aug 11, 2013
Sampling 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 ,
View 9 Replies
View Related
Mar 12, 2014
I am attempting to concatenate several columns and I am getting the Type Mismatch error. I understand why I am getting this error, but I do not know any other way to accomplish what I am seeking.
[Code] ......
View 9 Replies
View Related
Mar 16, 2009
Is it possible to run a macro (concatenate) automatically on a sheet when data is refreshed on that sheet from access database? It there a code that has to be written in vb?
View 5 Replies
View Related
Sep 3, 2007
I have attached a spreadsheet illustrating my query.
I would like to run a sub procedure called generatedataset then I'd like to enter a date from column J (starting from J4) into G12 ( cell G12 is a named range called effective_date). Then enter the concatenation of the adjacent cells K4 and L4 and place this into cell G9 (cell G9 is a named range called dataset_name). Then call the procedure again. Repeating for all values in range starting at J4 extending down, and for all corresponding concatenated values in K and L.
Data would always be taken from columns J or K&L and placed in the same cells ie G12 (named range effective_date) and G9 (named range dataset_name) respectively.
View 5 Replies
View Related
Mar 8, 2008
Need to take column J20:J255 and column K20:K255 and concatenate into activesheet K20:K255. This needs to happen when OptionButton1.Value=True. The information in each cell will be different. The following code works well, but it will not allow me to put a space in between the two strings.
Private Sub OptionButton2_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("D20:D54")
If OptionButton2.Value = True Then
With DescriptionCell
.NumberFormat = General
.Formula = "=CONCATENATE('Bill of Materials-3'!F20,'Bill of Materials-3'!I20)"
End With
End If
End Sub
View 2 Replies
View Related
Nov 23, 2006
I need to write a line of code which puts the following into a cell:
=+C4 & " - " & 1
The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"
So my code will be something like:
Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"
This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.
View 7 Replies
View Related
Aug 17, 2007
I have a worksheet of 5 columns. What I would like to do is subtotal an amount in column d, where the item code in column b are the same. Lets say there are 5 rows with code 8150 in column b, I want to add all these rows up BUT ALSO add up any rows which are 8150+1 (x+1) i.e. 8151. All transactions where the code in column B, x + (x+1) = 0. All true results should then be cut and pasted into a new worksheet (one worksheet for all transactions that are true).
e.g.
8150 1233
8150 12
8150 644
8151 -2084
8151 -10
8151 -205
Total of above amounts is zero, therefore cut and paste to new spreadsheet.
Now that I think about it, its actually quite complicated.
View 9 Replies
View Related
Dec 25, 2007
Can I adapt this code so that users receive the alert if they try and go to the next field without filling in the required ones, or - better - to combine it with a code that does not run a macro, but instead gives the alert, if the required fields have been missed. (I don't require anything to do with printing, this was the closest code I could find)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheet1. Range("A1").Value = "" Then
MsgBox "Cannot print until required cells have been completed!"
Cancel = True
End If
End Sub
View 4 Replies
View Related
Jul 23, 2014
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
View 7 Replies
View Related
May 21, 2014
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies
View Related
Jan 26, 2010
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
View 2 Replies
View Related
