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.
I have recently discovered the concatenate formula. Is it possible to include 2 variables with 1 constant entry. i.e. I wish to establish file names with 2 variables, e.g. 0051 and 1025 followed by a file extension, .EC0 - the file name being 00511025.EC0. The variables are in Excel columns. I can achieve the wanted result by establishing an extra column and copying .EC0 into every cell of that column; but I would prefer not to have that extra column in the completed Excel file.
View 2 Replies View RelatedI 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
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.
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.
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 RelatedSampling 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 ,
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] ......
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 RelatedI 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.
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
My goal is to write an equation that allows me to total all of the numbers in a column until the value = 0.
When I write the following equation using actual cell references, I am able to get the results I am looking for:
=IF(LEFT($E$78,6)=" Expe",SUM($J$78:INDEX($J$78:J1000,MATCH(0,J78:J1000,),0)),0)
When I try to write it in a macro using variables, I get all messed up:
Selection.Offset(1, 0).Select
Selection.Font.Italic = True
ActiveCell.FormulaR1C1 = " Expense"
[Code].....
(Possibly unnecessary information: This is for a budget spreadsheet I am creating. Ideally when I am finished with the macro it will allow me to add a line item under a specific program and update the total amount spent. The budget is broken out by month, rather than program. When a new program begins the amount in column J will always be zero, hence the Sum until 0 is reached.)
Basically, there are 5-6 worksheets and I want this to go into each sheet and update the pivot table by changing the dates to today from pulldown menu in pivot table.
But how do I replace that in below recorded macro?
Workbooks.Open Filename:= _
"M:xxxxxxxxxDaily TemplateAGT_TM3 evolvement.xlsm" _
, UpdateLinks:=0
Range("AU11").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Prices ObservationDate].[Observation Date].[Observation Date]"). _
VisibleItemsList = Array("")
[code].....
I am trying to categorized a dataset by country. The dataset need to be updated everyday and the size of each category is different each time. So I try to define a ranged name using variables.
Dim rows_CN As Integer
Dim star As Integer
Dim endn As Integer
Dim lookup_cn As name
Dim lookup_HK As name
Sheets("criteria").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "= COUNTIF(criteria!R37C2:R500C2,""CN"")"
rows_CN = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(criteria!R37C2:R500C2,""HK"")"
ActiveCell.Select
rows_HK = ActiveCell..................
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.
Will running a Macro from within another macro allow the second macro use the first one's variables?
Example:
Macro A Starts
Variable x set
Macro B starts
Macro B uses x from Macro A
Macro B ends
Macro A ends
I would like to use a macro to do the following...after selecting a cell eg C12 the macro would copy its contents and paste them into S12. However I would like it to be able to do this with any row I select and column "S" contains links to another worksheet, for example... C12 contains 123456, S12 contains =654321!C41.
I need the numbers to be the same in both cells. So after the macro has run C12 would still be 123456 and S12 would now be =123456!C41. The numbers in the cells and the rows are variable (columns are always the same, "C" & "S").
In my code I am using several variables to create a concatenated string and this is making my code quite lengthy..Now I have manged to use it in the SELECT CASE but it still remains big..
SO I was thinking if it were possible to break it into smaller parts and create a Sub-Routine which can be called in the Main code in the select case for the specific condition this way I can use it in Multiple places without hassles..
find the code which I need passing the variables..
Code:
Sub Condition_I()
Dimm Search_Value as string
Dim INo As Long
Dim Whole_Text As String
Dim Lookup_No1 As String
Dim Lookup_No2 As String
Dim Condition_I_Text As String
Dim WksI As Worksheet
[code].....
I need to pass the Search_Value from the Main Code and also the whole_text back to the Main Code..
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 RelatedI have a matrix that has variables in the rows and in the columns. Then I have a regular table. For each row in the table, I need to run a macro using the combinations in the matrix. I just don't know how to combine these two to make my macro run appropriately with the right combos for the right amount of time.
View 2 Replies View RelatedI am looking at using forms, as in the Userform... Not used them before, but would like to know, if you create a text box on it, is the value global, so any module can use the var? or how can I, so that value entered can be used on the whole book ?
View 3 Replies View RelatedUnzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
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.
I want to run a Shell command to open lots of files.
I can get the basic command working, but I want to open a lot of different files and am seeing if there is a way to save time by having a variable input, rather than having 1000 different shell lines with IF statements.
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 6 Replies View RelatedI have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.
In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.
trying to select multiple ranges of data at once using variables as my selection range criteria.
I.e., I want something similar to:
Range("A10:A30,B10:B30,E10:E30").Select
But would like to be able to perform the same selection using variables.
StartVar = 10, EndVar = 30
I'm sure it's just a matter of syntax, but I can't seem to get it right.
I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.
Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "$A:$C"
strResult = "='" & strDate & "'!" & strColumnRange
Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub
I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.
I gather that I can't use 'Call' as the second macro is in another workbook.
I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.
How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?
I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:
Sub Create_Kicbacks_Sheet()
' Create_Kicbacks_Sheet Macro
' Creates "Kickbacks" sheet for invalid information.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Kickbacks"
Sheets("Kickbacks").Select
End Sub
This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:
if Column A = Y and Column B = Mandatory -> Leave Alone
if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks
if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Best Efforts -> Delete Row
Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:
Sub Moveto_Kickbacks()
Dim r As Range, LR As Long
With Sheets("Data")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Set r = .Range("A2").Resize(LR - 1)
.Range("A1").AutoFilter field:=1, Criteria1:=""
.Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"
[code]....