Loop Copy Columns Action
Sep 6, 2009
I need to copy several columns (G:L) underneath (A:F)
I got this going using the recorder, and copy the action several times in the macro (See below).
I obviously would like to have this in a loop.
Range("G1:L35000").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:L").Select
Application.CutCopyMode = False....................
View 9 Replies
ADVERTISEMENT
Nov 19, 2008
I am trying to loop through an action within a macro and require some assistance.
I have the following
View 2 Replies
View Related
Jul 17, 2012
Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.
What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."
My if/then statement in question lies following my note " 'here lies my current dilemma to solve"
Code:
Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro
[Code]....
View 6 Replies
View Related
Feb 6, 2008
I ahve looked loads on here but I really did not understand the " find" statement (!) so have had to do a lookup the only way I know - with loops.
I have two problems:
The first being that it takes 6mins to run and return values.
The second AND MOST IMPORTANT is the following....
What i want to do with the following code is - on the first "If" - if I find an emptycell/blank, i want to exit the "if" so the first "next" statement is incremented - in other words, be able to check if the next cell along is blank/non-empty
I tried using the "exit for" but I only want to opt out of the if statement - not the actual "for..next"
I would have used a goto to get to the line I wanted but it has different meanings with this language....
Numrows2 = Worksheets("File2").Range("A65536").End(xlUp).Row 'Gets the File2 Row count
For e = 2 To 7 'Cols B-G (2-7) contain the headings
For d = 1 To 229 'There are 228 cells down to check
For f = 7 To 212 'File1 has 206 cells across cols 7-212
Worksheets("Looklist").Activate
If IsEmpty(Worksheets("File2").Cells(1, f)) Then
Exit For
View 4 Replies
View Related
Nov 21, 2013
I have the following code that rearranges the data based on the values in the first column (cannot post attachments). Currently, the code is grabbing the whole columns when performing cut and paste and with larger sets of data I am getting "Excel cannot complete this task with available resources" error. Need to modifying the code so it only grabs the used range as opposed to the whole columns.
Code:
Sub ArrangingColumnsByNumber()
Dim Count As Long
Dim CurrentNumber As Long
Dim CurrentColumn As Long
Count = 1
CurrentColumn = 1
CurrentNumber = Cells(Count, CurrentColumn)
[code].....
View 4 Replies
View Related
Feb 7, 2007
I have a macro to copy/paste B14 to B14 in next months workbook. Once a year the event occurs twice in a month, resulting in B14 and B15 both having values, the other 11 months B15 is blank. For this one month I need to copy/paste B15 to B14 in next months workbook. The macro is quite long. So it is If B15 has value then copy/paste B15 to B14 in Feb workbook, otherwise copy/paste B14 to B14 in Feb workbook. Unable to work it out myself.
View 8 Replies
View Related
Aug 23, 2013
I am trying to develop a system to allow me select different parts from multiple sheets and then add them into a bill of material on a separate sheet.
[URL] ...........
I have written some VB script the best I can and it includes a 'commandbutton' to make it easy for the user of the workbook to use.
This is how it works:
Go to 'Step 1 - Manifold 8640' tab and highlight one of the yellow cells.Once you have selected a yellow cell, you then click the 'select' button above.The value (along with other values) are copied to the 'Smart Calc' sheet.Then you go to 'Step 2 - Gland Plate' tab, select a yellow cell, click the 'Select' button.Notice how the 'Step 2 - Gland Plate' value is entered into a different group in the 'Smart Calc' sheet - this is great................BUT
The issue I am having is:
If the user goes to 'Step 1 - Manifold 8640' tab, highlights the desired yellow cell then presses the 'Select' button twice, it will add two lines of data in the relevant 'Step 1 - Manifold 8640' section....This is ok.But when the user clicks a third time, the 'Step 1' data will overflow into the 'Step 2' section of the 'Smart Calc' tab...because the script tells the data to look for the next available line to write to. This is what I am trying to avoid!
Instead, i would like a message box to popup to indicate to the user that they cannot add anymore data into into the 'Step 1' or 'Step 2' sections of the 'Smart Calc' tab until they clear the data from within the relevant section in 'Smat Calc'.
I would like to set limits on where each 'Step 1' or 'Step 2' data is written to the 'Smart Calc' sheet (so it is within its relevant section). As you can see the script basically looks for the next available cell but this isn't really good when the data 'overflows'.
Note that you are able to highlight the yellow cells in the 'Smart Calc' tab and press the delete key, it will delete the data to clear the line. That is how i designed it to work so if the user makes a mistake they are able to just delete the relevant line and enter new data.
View 1 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Aug 16, 2014
I want to perform double_click event for combobox, but only for cells in certain columns and rows.
this is code :
[Code] ....
I have to loop through columns 6 to 67 with step 1, and rows 19 to 409 with step 5, but don't know how . I attached sample, coloured cells needs that double_click event.
Attached File : Loop_Column_Row.xls
View 12 Replies
View Related
Apr 15, 2008
I am having some problems in setting up a macro that is intended to do the following
Run for every cell in a collumn a check if cell =2
If true then B1 is copied to that cell.
If false then nothing
This seems rather simple but the loop should go to collumn B, C, D, E, F,... until the last collumn.
Every time the collumn shfits the cell to be copied also shifts so is B1, C1, D1, E1, F1,...
View 14 Replies
View Related
May 12, 2011
I have a data set that I need to reorganise
Currently the data runs across the columns as:
COL A:C = Descriptions
ROW 1:1 = Week End dates
ROW 2:2 = Week Number
and D3:BC122 = Values
AccData
[code].....
What I need to do is create some VBA that will loop through E:BC moving the data in the column to under the data in Col C each time (and also A:C)
I can create loops that add 1 to row number but am stuck adding 1 to column in VBA.
View 7 Replies
View Related
Apr 5, 2013
I have two columns of data J2:K1000, I need loop through both columns at the same time and copy the data into cells into E1 and F2, then run through the remainder of the code. The code below I have adapted but cannot get it to work correctly.
HTML Code:
Sub Test()
Dim LastRowColJ, LastRowColK As Long
Sheets("Sheet1").Select
LastRowColJ = Range("J65536").End(xlUp).Row
For Each c In Worksheets("Sheet1").Range("J2:J" & LastRowColJ).Cells
Sheets("Sheet1").Range("E1").Value = c
For Each d In Worksheets("Sheet1").Range("K2:K" & LastRowColJ).Cells
[code]....
View 4 Replies
View Related
Aug 21, 2009
my worksheet has columns e1:il100 populated with data. what i am trying to do, is to insert a column to the right of column 'e', copy the contents of cell 'e1', to rows 3:100. then i want to cut e3:f100, and place it in the next available row in columns b:c, delete columns e:f, and then goto the next populated column, which i believe is now column e, and start over, until all columns have been moved to columns b:c.
View 9 Replies
View Related
May 3, 2007
I have a dataset with has 12 columns of data followed by 49 columns of values (one for each product) What I need (to be able to pivot it) is one column of values. I had written a quick for each loop but this doesnt work for colums:
Sub Adjust()
' Find last cell populated by data
Range("A65535").End(xlUp).Select
Last_Cell = ActiveCell.Row
'Rename a column heading
Range("M1").Value = "Partner"
Range("M2:M" & Last_Cell).ClearContents
'Loop through each column and copy data values
For Each Column In Range("N:BJ")
' Find first empty cell
Range("N65535").End(xlUp).Select
Start_Cell = ActiveCell.Row + 1.....................
View 2 Replies
View Related
Apr 30, 2012
i would like to create loop throug among columns, is xlup or xldown methods will make sensne ?
View 2 Replies
View Related
Nov 17, 2008
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
I tried this:
For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next col
View 9 Replies
View Related
Nov 22, 2006
I am OK to set values to the third row of "Loop Folder" from a column in " T" but I haven't figured out how to capture the remaining 22 columns of this worksheet.
Workbooks("Loop Folder.xls").Sheets("Sheet1"). Range("A3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C5").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("B3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C6").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("C3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C16").FormulaR1C1.............
View 3 Replies
View Related
Nov 28, 2006
I am trying to loop all columns in each of a number of sheets using current region.
It selects the current region OK but the column counter only shows 1 as the number of columns in any sheet.
The Cells(6,1) likely has something to do with it but I do not know any other way to point to the current region I need. I do not know in advance how many rows or columns I am starting with but each has the required Row 6.
For x = 1 To Sheets.Count
Sheets(x).Activate
Cells(6, 1).CurrentRegion.Select
Selection.CurrentRegion.Name = "Mydata"
'Loop all columns in sheet
For y = 1 To Range("MyData").End(xlToRight).Column
Cells(5, y).Select
Next y
Next x
View 6 Replies
View Related
May 28, 2014
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
View 12 Replies
View Related
Feb 13, 2010
I am trying to run a loop on a few columns simultaneously, from the last row to the top (well, until the 2nd row).
So I have a few columns of data, A through I, and the number of rows they populate changes all the time.
So how would I run a loop that will check for the following:
If a certain row in Column F is greater than the previous row in column F by 1.5times, then in that same row, if number in Column B is greater than number in E, the code should put the the value of E in a new column, lets say M (in the same row)
I know that sounds very confusing, but is that even possible? I am playing with loops, and I can only do very simple loops where I define a range, like
View 14 Replies
View Related
Nov 18, 2011
My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
View 1 Replies
View Related
Jan 10, 2014
I have a table of data (lets say A2:H30) i want to populate so i want the macro to loop based on how many columns i have? I am using this to post the data based on # of rows..
Sheets("Main Cal").Select Dim bottomA As Integer
bottomA = Range("D" & Rows.Count).End(xlUp).Row
Dim c As Range
Dim ws As Worksheet
For Each c In Range("D3:D" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then
View 1 Replies
View Related
Mar 25, 2014
I am stuck with looping columns and rows.What i am trying to do is... I have to loop through columns E3 to G3 and also rows of each column and check for a condition inside the column.How can i achieve that? I have the following structure.. How can i code it?
For i=E3 to G3 'Column loop
for j=4 to 47 'Row loop
'code to check if the value isnumber
next j
next i
View 9 Replies
View Related
Jun 23, 2014
I have a list of several hundred columns, beginning with column "G:G", with varying numbers of rows of data in column - each row dipicting a monthly data point. I'd like to average the numbers in each column (need to average over the appropraite time-frame) and compare that average with the corresponding average (same time-frame) for benchmark (column"F:F"). The problem is I don't have the same number of data points in each column; some have data points for every month for the past 33 years, and some just a few years; almost all have differing beginning and ending dates as well.
[Code] ....
View 9 Replies
View Related
Jan 9, 2009
I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.
How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.
View 9 Replies
View Related
Jan 26, 2009
I've managed to get my macro to work ok - but what I want to do now is to get it to loop through more columns. Currently it looks for email addresses in column AH (which will always be the same) then it looks for a "yes" in columnAB and if there is no "SENT" in column T then it sends an email,after which it adds the word "SENT" to column T. What I want t do is for the macro to then run on columns AC+U, AD+V, AE+W and AF+X. So basically it will loop through one column to the right each time for a total of 5 pairs of columns
View 2 Replies
View Related
Oct 27, 2009
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
View Related
May 15, 2007
I have a problem with the next code and i don't know how can i solve it... i have a workbook with 8 columns and variable rows per day... This workbook has 2 sheets. What I want to do is find in the first sheet all the rows that in column E have the number 570 or 640, and after this, choose some of the columns and copy them in another sheet repeating this all the time until the last row... and copying in the next row of the other sheet, starting in the 20th...
LastRow = Range("A65536").End(xlUp).Row
Dim i As Integer, j As Integer
For i = 2 To LastRow Step 1
If Cells(i, 5) = 570 Or Cells(i, 5) = 640 Then
For j = 20 To 26
Cells(i, 2).Copy Destination:=Sheets("Final").Cells(j, 1)
Cells(i, 4).Copy Destination:=Sheets("Final").Cells(j, 2)
Range(Cells(i, 6), Cells(i, 8)).Copy Destination:=Sheets("Final").Cells(j, 3)
Next j
End If
Next i
View 3 Replies
View Related
Nov 15, 2012
I've been trying to loop data for column A from row 1 until 6 and print the result on specified cell. But the problem is, it keeps looping until column B,C and D.
I want to calculate average result separately for column B,C, and D without taking the previous data.
Here is the code that I've done.
VB:
Sub Avg()
col = 1
Row = 1
Do
Name = ThisWorkbook.Sheets("Sheet2").Cells(Row, col).Value
If Name <> "#" Then
[Code] ....
I attached my excel. The green box indicate the result that I want : function.xls
View 5 Replies
View Related
Apr 17, 2014
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
View 10 Replies
View Related
