Increment Address References In Loop
Oct 11, 2007
I try to select a range with a counter, but cannot find how to do it.
My aim is in the following loop to select the range ("B,i:V,i").
B and V are the colum, i is the counter that marks the row number, counting from 8 to 16.
Sub Copy()
For i = 8 To 16
Sheets("DB2").Select
If Cells(i, 7).Value <> 0 Then
Sheets("DB3").Select
i = i - 1
Cells(i, 2).Select
'this is the cell that should be a range ("B8:V8") to ("B16:V16"), as the counter goes from 8 to 16
i = i + 1
Selection.Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Else
End If
Next i
End Sub
the Sub works with a cell, can somebody please tell me how to turn the cell in the range?
View 9 Replies
ADVERTISEMENT
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
Mar 5, 2009
I have a sheet which needs to look up one reference and then fill a table with the rest of them.
EG:
Cell A1 contains '0091 911'!$E$2 (cell E2 contains value 100)
Cell A2 contains =indirect(A1) and displays value 100
I need a formula which will auto fill the remaining cells in the table.
eg:
Cell A3 fills to contain '0091 911'!$E$3 (row +1)
Cell B2 fills to contain '0091 911'!$F$2 (column +1)
so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....
View 14 Replies
View Related
Jan 17, 2008
I'm using a macro to update individual formulas in a sheet that contains data from external documents. (i.e. ='C:September[Book2.xls]Sheet1'!$AZ$1 + 'C:September[Book3.xls]Sheet1'!$BA$1 ) Every quarter I have to change the column references in this formula (i.e. $AZ$ will become $BD$ and $BA$ will become $BE$ and so on). Also I have to change the September reference to December etc (but I've mastered this already). My current code works well, but requires manual updating:
Dim ColumnReference1
ColumnReference1 = "BD"
Dim ColumnReference2
ColumnReference2 = "BE"
Worksheets("Sheet1").Range("H48").Formula = "='C:September[Book2.xls]Sheet1'!$" & ColumnReference1 & "$1 + 'C:September[Book3.xls]Sheet1'!$" & ColumnReference2 & "$1"
My problem with this method is that I need to automate the updating of ColumnReference1 = "BD" to ColumnReference1 = "BH" to ColumnReference1 = "BL" and so on and so forth. It's handy that the reference shifts across 5 columns, which remains constant. Ideally, I'd like to tell the macro to look at "AZ" then add 5 columns.
View 9 Replies
View Related
Apr 27, 2014
I am creating a training document for work; the sheet I'm creating is a summary sheet which works out how many people answered questions correctly, incorrectly (bringing in from another sheet), number of questions answered and percentage of correct answers.
Looks like:
Correct: =COUNTIF(sheet1!E2:E36,"correct")
Incorrect: =COUNTIF(sheet1!E2:E36,"incorrect")
Number of questions answered: =SUM(C4+C5) (correct+incorrect)
Percentage right: =SUM(C4/C6) (correct/number answered)
I need to drag these 4 formulas down into another 400+rows, however in the correct and incorrect formulas I need it to keep the 2:36 but change the e to f, g, h, etc.
View 14 Replies
View Related
Feb 24, 2009
For Next Loop increment. simple For-Next Loop:
View 2 Replies
View Related
May 31, 2007
I want to test a condition and if false skip the rest of my for block and hit the next for:
For something = 1 to 100
if test = "false" ?NEXT FOR?
code
code
code
code
Next
I can set a lable at the Next and use a goto but that seems kinda kludgy.
View 10 Replies
View Related
Jan 9, 2012
What would be a good way to increment each of my ranges for example I need x to copy Range B11:to G20 and paste it to C10 for the second loop. Each Variable has different increments.
I'm horrible with Visual Basic.
Sub Macro5()
'
' Macro5 Macro
'
Dim i As Range, j As Range, k As Range
Dim x As Range, y As Range
Dim Num As Integer
[Code]....
View 1 Replies
View Related
Apr 15, 2013
I have a number of strings in adjacent columns in the same row, like so.
Coulmn1 Column2 "blank" Column3
If the cell is blank I want to hide the respective column .
I'm guessing the code will roughly take the form of the code below with an if statement nested in a for statement.
Sub (test)
With Range("BD22")
For i = 1 To 10
if range (i,22) = "" then hide
else don't hide
.Offset(1, 0).Formula
Next i
End With
View 1 Replies
View Related
Sep 11, 2007
I am experimenting one way to solve one problem, but to do this I have to Name some cells with some values, thing that normaly in VBA is symple but I have never done before in coding. May be my problem is because what I am doing is not valid with the vlookup but if you can take a look to my file. The problem is that when I run the Macros the Named Cells are totaly out of place...
Sub Lookup()
dercell_unit = Range("C65500").End(xlUp).Row
Range("B" & dercell_unit, "E2").Select
Set Rango = Range("B" & dercell_unit, "E2")
For i = 2 To dercell_unit
Names.Add "VALrsa", "=$C" & i
Names.Add "RESOLdds", "=$D" & i
Cells(i, 7) = Application.VLookup(Cells(i, 2), Rango, 4, False)
Next i
End Sub
View 3 Replies
View Related
Jan 10, 2007
I am using this code to create onsheet user form and populate a spreadsheet db
'Step 1 : store the information in every second row in DBsheet
Set rngDataOut = Worksheets("Database"). Range("A65536").End(xlUp).Offset(2, 0)
'Step 2 : Post the current results
rngDataOut.Range("A1") = Now()
rngDataOut.Range("B1") = Range("B1")
rngDataOut.Range("C1") = Range("B2")
rngDataOut.Range("D1") = Range("B3")
rngDataOut.Range("E1") = Range("B4")
'Step 3 : Clear current Selection
Range("B1") = ""
Range("B2") = ""
Range("B3") = ""
Range("B4") = ""
my question is how could I modify it with for-next loop because the form will be with more than 50 entries.
View 2 Replies
View Related
Oct 11, 2011
Make a loop where I can increment the row number and execute the command where I set the formula?
View 1 Replies
View Related
May 22, 2014
I'm trying to use the following loop to increment the column width of a range of columns thus:
Code:
NewWidth = 0
WidthIncrement = 0.5
For MyCount = 1 To 26
NewWidth = NewWidth + WidthIncrement
Cells(1, MyCount).EntireColumn.ColumnWidth = NewWidth
Next
If I set WidthIncrement to be an integer value, the code works correctly. If, however, I make it something like 0.1, all the column widths that the loop acts on are set to 0. I suspect it's something to do with how I'm declaring the NewWidth and WidthIncrement variables - they're currently set to Double. I know that I can have decimal values for column widths (e.g. 8.43)
View 5 Replies
View Related
Jan 31, 2013
What I am trying to do is during a loop operation, which subtotals variable ranges, I want to store the locations of the cells that it puts the sum function into. I.E. if based on criteria it determines that range E4:E12 is summed into E13, I need to save E13 to use in a formula once I'm out of the loop. I don't know how many instances it will find and there's the possibility in the future that not only will the number of instances increase, but the location could always be different as well. Any way to do this without a million lines of code.
View 7 Replies
View Related
Jun 30, 2014
Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:
Code:
Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")
[Code] ......
I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:
Code:
.Range("K1") = .Range("sheet_name2.Value!A14").Value
View 2 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Feb 10, 2014
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
View 1 Replies
View Related
Nov 25, 2007
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
View 9 Replies
View Related
Mar 2, 2009
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
View 9 Replies
View Related
May 22, 2014
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
View 1 Replies
View Related
May 20, 2014
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
View 2 Replies
View Related
Apr 24, 2013
I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148
DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121
abdi abdi 5390 monterey rd #6 sanjose,CA95111
Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148
I need the result to be in a way like -
3430 Chemin de riviere
San Jose
CA
95148
3838 Glengrove way
San Jose
CA
95121
5390 monterey rd#6
San Jose
CA
95111
3212 Gateland CT
San Jose
CA
95148
I have around 12000 records with the same format.
View 9 Replies
View Related
Feb 25, 2014
On a worksheet called "Contact Info" column A starting in row 2 I have a list of names (variable length). In Columns B2-D I need the email address, work phone number, and cell phone number.
View 5 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 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
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
Oct 20, 2009
I want to take the date value of a cell, add one day to it and paste it to another sheet's cell. For example if Sheets("x").range("i4").value is 12/12/1999. I want to add a day to it (13/12/1999) and then paste it to Sheets("y").range("d3").value
View 4 Replies
View Related