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 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'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 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 RelatedI 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 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'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 ........................
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!
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]....
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.
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.
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 RelatedI 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]....
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?
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]....
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
My objective is to replace a text string with a numeric variable. The string has text values such as US, Canada, Germany, France etc, There are about 15 such words in total. Each row will have a different one of the 15 text strings.
Each text string has a corresponding number, which I want to use in various formulas throughout the spreadsheet. The numbers are located on a separate worksheet (“Match Rate”), within the same workbook. I obtained the numbers from a formatted report, which I simply pasted into the second worksheet. Due to the complexity of the report, I can’t reformat the numbers to allow use of the VLOOKUP function.
I created the following function, but unfortunately it doesn’t work. Can you help, please?
I’d like to stay with a VBA solution, since a simple if/then sequence such as this is an excellent introduction to the powerful world of VBA... (it just doesn’t work, yet). I suspect the problem is in the calls to the Match Rate Worksheet.
Ok. Here's what I'm trying to do. I have a drop down menu of certain providers located on cell F5. Each one charges different rates per day located on another sheet. Here's the kicker two of those providers charge different rates depending on the amount of days. Those two are UASC and CMA. For the First 5 days they charge one rate and after the 5 days they charge an increased rate.
I'm trying to have one cell located on the first sheet display this information with all the proper calculations. The below formula gives me a false value. I'm not even sure if it works. I know a little bit of excel through some intro courses in college but I'm not sure if a formula like this would work. I know laugh it up.... : ) but I'm not too saavy with formulas....
I have a total of 30 nested formulas that (ofcourse) dont work in excel.
Here is an example of the first 7 nested IFS
(1) IF(MID(A7,8,9)="GIR",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(2) IF(MID(A7,8,9)="NL",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(3) IF(MID(A7,8,9)="BE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),
(4) IF(MID(A7,8,9)="AT",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(5) IF(MID(A7,8,9)="DE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(6) IF(MID(A7,8,9)="PH",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
(7) IF(MID(A7,8,9)="AUBE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
I tried this code but it is not working.
Function LookupRetrieve(pVal As String) As Long
If pVal = "(MID(A7,8,9)="GIR" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="NL" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="BE" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="AT" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="DE" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="PH" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
ElseIf pVal = "(MID(A7,8,9)="AU" Then
LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!
C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))
Else
LookupRetrieve = "isingcode missing"
End If
End Function
The formula is a vlookup formula (Index/Match but with multiple criterias such as the use of Mid).
I tried Concatenate but the formula is too long and has a lot of characters and VBA seems better.
I'm building a macro and need to know how to add a nested if-then statement. I need to create a formula in a new column whose value extends to the last row of the database. My base formula is sumproduct(--((A2:A:last row which I need to calculate&B1:B same thing)=A2&A2))
View 9 Replies View RelatedI am trying to make this work. VLOOKUP (B2,"lookup tables'!$A$4:$A$11, if(D2'="ST" 'lookup tables!$C$4:$E$11.
I am attaching the file : MDCC Reservations.xlsx
I am trying to get the Daily room charge.
I am trying to write a formula that will satisfy the following:
C1 and G1 are number values.
IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.
Level RatingA RatingB RatingC Low Medium High
2 x x x x x x
3 x x x x x x
4 x x x x x x
5 x x x x x x
I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.
Im needing to achieve what an 11 argument nested if would manage, ie: ...
View 2 Replies View Related