I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies
I have a list of names with blank cells in Column A cell 2.
I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).
In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.
I need numbers in column B cleared as they are concatenated into the single cell.
Spreadsheet.jpg
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T
he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is
=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).
VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer
[Code] ....
My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?
VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"
i have a problem with a nested loop:
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================
Sub new2()
Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant
r = 1 ..................
I think its something simple I'm missing here. I have a range of stock prices (Range("E3,E4,E5,E6")) what I'm trying to do is use for each next loop to calculate the number of shares that I could trade. and place those shares in a range of cells adjacent to its corresponding price (Range("G3,G4,G5,G6")). I think the problem is in my loop statements but I can't seem to find it . Could someone kindly show me the error in my ways.
Code: ...
I'm trying to finish writing the last portion of a macro created to automate my data analysis project for my research but I'm having trouble writing the last For Loop of the code. I have a target cell B25 which states a given temperature of my reaction. Once a value is entered in B25, 5 activity coefficients are calculated and displayed in cells B31,35,39,43, and 47 which I would then like to copy and paste in cells J31-J35.
However, I'd like to calculate the activity coefficients for a range of temperatures which are listed in cells J30-W30. Thus, I need to create a For loop which copies the value from J30 into B25, copies the values from B31,35,39,43, and 47 to J31-J35 and then moves to the next column replaces the value of B25 with K30 and repeats the same process until all the columns are filled.
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet
Class Number
Number of Arenas Used
Class 1
[Code].....
I'd like to know how I can loop through a series of frames and loop through each of the option buttons in each frame and write the value of the selected option back to a worksheet.
Here's an example of how I'm doing it now:
For Each ctl In fraWorkMode.Controls
If ctl.Value = True Then
ws.Range("A1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTransport.Controls
If ctl.Value = True Then
ws.Range("B1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTravelTime.Controls
If ctl.Value = True Then
ws.Range("C1").Value = ctl.Caption
Exit For
End If
Next ctl
I'm also thinking that OFFSET is maybe a better option than specifying each cell for the collected data.
These frames are on Userforms for a survey and some of the frames contain up to twelve other frames so a nested loop approach appears to be more efficient.
how to speed up the following loop? It takes about 40 seconds.
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Integer
Dim y As Integer
Dim Snominaloc As Double
Dim Scs As Double
Dim Sirating As String
Dim Mnominaloc As Range
Dim Mcs As Range
Dim Mirating As Range
Dim Mcbrating As Range
Set Mnominaloc = Worksheets("Parameters").Range("b10")
Set Mcs = Worksheets("Parameters").Range("b22")
Set Mirating = Worksheets("Parameters").Range("b12")
Set Mcbrating = Worksheets("EL").Range("b33")......................
I have a problem with a nested loop in my macro as Excel hangs every time i run my script. I suspect it is because my sheets have too many elements.
I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.
I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.
In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.
Into sheet2 I copied the old english in column A and slovenian translation in column B.
On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC. So, this should fill most of the translations I need to do, and the rest I will do manually
I am quite new to VBA so this is what I came up with:
[Code]....
The number of rows is around 44000 in each sheet. How I could optimize or even completely avoid the nested loop, because each time i click the button I need to wait for almost an hour and then Excel hangs.
is there any body kind enough to look into this code and tell me from where the loop body starts and ends and which is the loop variable and how it is being incremented or decremented. i know it is a stupid question but i admit here that i learn and manage to update my sheets only because of this forum help, as i have absolutly no idea of VBA when i started some couple of months back but know i can do little bits.
thanx again for all the help i got so far and hope to get some solution this time as well
Sub LowRisk_Chart()
Clearout
Voffset = 0
Vcounter1 = 2
Vline = Sheets("ChartWork"). Cells(Vcounter1, 1)
Vblocks = Sheets("ChartWork").Cells(Vcounter1, 4)
Vstdman = Sheets("ChartWork").Cells(Vcounter1, 5)
Vlength = Vblocks
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.
ie. cells R2: HS2 should all be divided by Q2
cells R3:HS3 should all be divided by Q3
etc until R2000:HS2000 are divided by Q2000
I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
I'm having some problems understanding how to change the sequence in loop structures..
This isn't the entire code, but maybe it will get the point across.
Currently the loop is giving me the following sequence..
"CL" "WTI OI" "HO OI" "RB OI"
"HO" "WTI OI" "HO OI" "RB OI"
"RB" "WTI OI" "HO OI" "RB OI"
What I want to get is:
"CL" "WTI OI"
"HO" "HO OI"
"RB' "RB OI"
Is there a way to the loop to go to: x y x y x y
Instead of: x y y y x y y y
Dim ContractCode As Variant
Dim ContractTab As Variant
ContractCode = Array("CL", "HO", "RB")
ContractTab = Array("WTI OI", "HO OI", "RB OI")
For Each x In ContractCode
For Each y In ContractTab
Debug.Print y
Debug.Print x
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=x
Cells.Select
Selection.Copy
Sheets(y).Select
Range("A3").Select
ActiveSheet.Paste
Next
Next
I'm trying to concatenate a few cells but seem to be missing the mark with my code. show me where I went wrong.
Code:
Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range
Range("E1").Select 'Set the range for the loop
Selection.End(xlDown).Select
[code]....
I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells
Sub Analysis()
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer
TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row
For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg
ter.jpg
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.
Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................
I have the below set of data for example,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
I need the output as,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.
I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column
D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name
This is what i'm trying to get it to look like in column d:
("First Name Last Name" OR "FirstName.LastName") AND @companyname.com
writing a macro with following requirement
i need to display the third column with the concatinate values of column 1 and column 2
if A1 = Hi
B1 = All
then C1 should be Hi All
Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
2050377 ($20,000)105
I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.
I have three columns as follows:
ItemsWeekEnding Count
a14/7/2007 10
a34/7/2007 3
a14/14/2007 11
a14/7/2007 5
a44/7/2007 6
a14/21/20078
a44/14/20072
a34/21/20071
a24/7/2007 6
a14/28/20077
a24/21/20074
a94/28/20079
I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example:
a1 4/7/2007 15
as a1 for this weekend is occuring twice.
I need the formula to do it in excel.
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
View 14 Replies View RelatedI have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....
Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?
I need a simple macro to take a list of words in 1 column, and concatenate them with each word from another column
So if Column A had "bird"
and Column B had
food
feeder
house
the output would be
bird food
bird feeder
bird house
I will have many many more words than just "bird" though.
I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.
I've found solutions that say use
=concatenate(A1, " ", B1)
or the VBA equivalent
I have at least 15-20 columns which I wish to concatenate.
Is there an easier way than to type for all 15 columns?
=concatenate(A1," ", B1," ", C1,....)
Also, the number of columns of each row vary
Is there an easy way to concatenate two columns and provide every possible combination?
E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2
