Do While Loop - Copying Contents Of Row
Jan 28, 2014
I have a sheet where I want the contents of row G (where it has contents) to copy itself to the corresponding row in column A until the code has gone right through column G. The first such item is in row 1383 column G and occurs randomly down the rest of the spreadsheet. I've tried writing this but it doesn't do anything.
Here is the code:
Sub Loop1()
Application.Goto Reference:="R1383C1"
Do
ActiveCell.FormulaR1C1 = "=(RC[6])"
Selection.Copy
[Code] .......
View 1 Replies
ADVERTISEMENT
Aug 27, 2012
I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.
For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)
text
1
text
text
[Code]....
View 5 Replies
View Related
Feb 13, 2014
Effectively I have a cell that contains the following data :-
63303-9600
63303-5280
63303-9700
What I need to do is loop through this cell and take the first 10 characters and ouput to a row, then take the next 10 characters and output to the next row and so on until it has been through all the cell content.
View 1 Replies
View Related
Apr 28, 2014
Have a cell (G6) that contains the following formula, which looks up the cell adjacent to it (H6):
=IF(ISNA(VLOOKUP($H$6,Customers,2,0)),"",VLOOKUP($H$6,Customers,2,0))
I want to copy this formula to hundreds of cells below the first cell, with a formula that increments as follows:
=IF(ISNA(VLOOKUP($H$7,Customers,2,0)),"",VLOOKUP($H$7,Customers,2,0))
=IF(ISNA(VLOOKUP($H$8,Customers,2,0)),"",VLOOKUP($H$8,Customers,2,0))
=IF(ISNA(VLOOKUP($H$9,Customers,2,0)),"",VLOOKUP($H$9,Customers,2,0))
....etc.
Is there a way to copy this formula, and have it increment as shown?
View 3 Replies
View Related
Feb 7, 2007
I export data from another programme into excel and I wish to write a macro to copy the contents of columns G, H, I and J and paste it into columns A, B, C, D but starting at row 4. I then carry out various formatting tasks. I have written a macro that works for the copy and paste and the formatting but it only works when the data is the same length. The length of the data exported varies each time and so I am looking for some code that stops at a blank cell and copies the range above. I believe this uses xlup or xldown.
View 8 Replies
View Related
Jul 8, 2014
I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?
View 3 Replies
View Related
Apr 26, 2013
I am working on a manpower spreadsheet and need for one spreadsheet to extract data from another and automatically update if it is changed and I am struggling below is an example of what is on spreadsheet 1:
Mentor.jpg
and the information from spreadsheet 1 is copied to spreadsheet 2, however if amendments are made to spreadsheet 1 they are automatically updated on spreadsheet 2:
Spreadsheet 2:
C0-ord.jpg
View 1 Replies
View Related
Mar 20, 2014
I'm sure there is an easier way than copying and pasting each individual cell from one spreadsheet to another. Is there a way I can define a batch of cells (city, state, phone #) and copy them into the other spreadsheet so I don't have to do each cell individually?
Here is a picture to show what exactly I am trying to do.
SS.PNG
Also, the cells that belong in the same column and row on one spreadsheet are equal distance from each other throughout the other spreadsheet that has all the info in 1 column.
View 1 Replies
View Related
Jan 7, 2012
I am using Excel 2007, here's my question:
I just wrote two macros that each produce separate text files (call them 'A' and 'B'). I want to open 'B' with Excel VBA, copy all of its contents, and paste that content into 'A' right after a specific location in 'A' (where I have 10 consecutive asterisks, i.e., **********).
Most topics relating to Excel VBA and .txt files have to do with either importing / exporting into Excel (not what I want), or with associating .txt files to Excel (also not what I want).
View 7 Replies
View Related
Jun 17, 2008
I have a workbook called "Staff Planner". I need to check Columns C to I in Sheet "Plan" to firstly find text in a red font, and then copy this value to Sheet "Useage" in Column K. (So, search C1:I1, find red font, copy contents of cell, paste value to K1). I then need to repeat this with blue font/value to H1, green font/value to E1, and finally pink font/value to B1
View 9 Replies
View Related
Dec 17, 2013
I have a script that copies data to files based on many cells contents but where I am having a problem is creating series numbers for each file.
File-01.txt
File-02.txt
..
File-100.txt
In my current code I copy files to folders by date and each folder I need series of files (Lab Testing series)
In column A1:A100 I have a series of numbers 01, 02, 03 ...100
Column B contains the Files to be saved
C:LAB2012Jan1file-01.txt
C:LAB2012Jan2file-01.txt
C:LAB2012Jan3file-01.txt
C:LAB2012Jan4file-01.txt
...
..
C:LAB2012Dec31file-01.txt
So this works fine
I now need the Script to do is to loop to Column A and select Cell 2 and do the File Copy again on the Next series
C:LAB2012Jan1file-02.txt
C:LAB2012Jan2file-02.txt
C:LAB2012Jan3file-02.txt
C:LAB2012Jan4file-02.txt
...
..
C:LAB2012Dec31file-02.txt
When Complete repeat until it reaches the end of column A
Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.
Column B is built using
=IF($C1="","",$I$1&$G$4&$D1&""&$I$8&$H$8)
=IF($C2="","",$I$1&$G$4&$D2&""&$I$8&$H$8)
View 1 Replies
View Related
Jun 5, 2008
When I run the following code, I get "Run-time error '1004': Application-defined or object-defined error"...most of the time. Sometimes it will work when I don't pass any variables to the function, and insert a raw number for the variable "counter". But it still gives me the error most of the time.
Sub WriteLatLongs(counter) 'counter
Dim j As Integer
Dim toprow As Integer
Dim bottomrow As Integer
For j = 1 To counter
toprow = (((j - 1) * 250) + 1)
bottomrow = (j * 250)
Sheets("PixelCoord"). Range(Cells(toprow, 2), Cells(bottomrow, 3)).Select
Selection.Copy
Sheets(j).Range("B7").PasteSpecial Paste:=xlValues, Transpose:=True
Next j
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
Sheets("PixelCoords").Select
ActiveWindow.SelectedSheets.Delete
End Sub
The error highlights the following line: Sheets("PixelCoord").Range(Cells(toprow, 2), Cells(bottomrow, 3)).Select.
View 3 Replies
View Related
Aug 11, 2009
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
View 3 Replies
View Related
May 18, 2012
I have a worksheet with a various data in column B.
1/ I want to search column B one row at a time looking for a string value of 'Town'.
2/ When the first row containing this string is found select a left 35 chars substring of the value in the column C and copy this down in column A if the value in Column B = '0000/00' UNTIL the string value of 'Town' changes
3/ Repeat 2/ until the last instance of 'Town' has been processed.
4/ Column A will have some empty cells where Town has not been populated. These rows should be deleted and replaced with one empty row between each change of Town value
3/ Continue pasting this string value into each successive row
View 8 Replies
View Related
May 29, 2014
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer
[Code].....
View 14 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx
View 10 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
May 15, 2014
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
View 2 Replies
View Related
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 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
Dec 12, 2007
I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-
View 12 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Oct 24, 2009
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
View 3 Replies
View Related
Apr 27, 2014
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Aug 2, 2007
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
View 9 Replies
View Related
Nov 30, 2006
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 ..................
View 9 Replies
View Related
