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 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 develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
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'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 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.
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.
I'm trying to build this formula and I'm only half way through it and it already isn't working.
Column D - can be 1, 2, 3, or 4
Column E - can be any number
If column D is 1 or 3, then the VLOOKUP should look up column E and point to the answer in column 4 on another worksheet (Companies in this case). If column D is 2 or 5, then the VLOOKUP should look up column E and point to the answer in column 5 on another worksheet (Companies in this case). This is what I have so far, just trying to get it to recognize if it is a 1 or 2:
=IF(OR(D2=1,VLOOKUP(E2,Companies!A:G,4)),IF(D2=2,VLOOKUP(E2,Companies!A:G,5)))
What is making this not work? I'm getting a false. But before I added the OR, it worked for the D2=1.
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 ........................
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
ABC PTY LTD 5671
ABC PTY LTD 5683
ABC PTY LTD 5692
ABC PTY LTD 5610
ABC PTY LTD 5611
Tried the below formula
=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")
Hi guys, hopefully you can shed some light onto this small problem I am having.
The current formula I'm trying to use is:
=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))
This isn't producing the desired results.
What I am trying to do in words is this:
IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail....
I have a new worksheet:
Employee No. Name
Under the Name colum (B) i want a formula that uses the employee number (A) to look up the name. The problem i am having is that i need to look the name up from 3 other worksheets.
On worksheet 1 i have fridays work and 2 i have saturdays work and 3 sundays!! I only want a name to appear if they worked any one (or more) of these days. If they didn't work i would like the name to remain blank.
On all worksheets employee number and name arein colums A and B.
I do have another worksheet that has name and number only in though. worksheet 4!
In the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.
************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Numbernone*NumberDescriptionRef2Description**none**3Ref**1one44***2two55***3three66***4four77***5five88***6six9Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have the following formula:
=IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)<(VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)-((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03))),'CORE Data 3 Month #2'!A3,IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)>(VLOOKUP('CORE Data 3 Month #2'!$A:$K,6,FALSE))+((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03)),'CORE Data 3 Month #2'!C3,'CORE Data 3 Month #2'!B3))
The strangest thing is happen. The part of formula underlined and the part bolded are identifical. For some reason, excel is unable to find the value in the underlined portion but is able to find the value in the bolded portion. As a result, I'm getting a #N/A result. how this can be corrected?
I have attached a small sample of some data I am working on (the total is about 6000 lines overall spread over 30 worksheets), but I am stuck trying to get a nested vlookup to work.
What I have
A list of codes contained in 'A' and values in 'B'. I have grouped together the codes in colum 'A' starting with the same 4 digits, and gave them a named range. Columns G and H show all the possible range names. 'K' is a list of all the seperate codes (I know it is the same as 'A', but this is just an example to get a formula working)
What I would like formula column L
to lookup the first 4 digits in column 'K', use that value to lookup the range name in 'G & H', then using the FULL code in K, look for that in the corresponding name range and return the value from 'B'
I have filled the following formula in many cells of a spreadsheet.
=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))
I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedExcel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
I have the following vlookup working exactly as I want it to in excel: =IF(ISNA(VLOOKUP(LEFT(C2,10)& "*", NAME,2,FALSE)),"",(VLOOKUP(LEFT(C2,10)& "*",NAME,2,FALSE))) -NAME is a defined section on another worksheet
The problem I have is when I come to use this within a vba macro I'm writing the wildcard section automatically gets spaces added so it goes from "*" to " * " and excel doesn't like it! The code I'm using to write it into the cell is simply:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)),"",(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)))"
how to stop the spaces being added?
My workbook contains four sheet: input, letter 1, letter 2, data
Data contains customer records, column A is numbers 1-500
Input sheet is a customer form containing details for 1 customer. The customer details are pulled from the data tab using vlookups based on a value in cell a1 on the input tab.
Letters 1 & 2 are letters to a customer. They again use vlookups to populate various fields on the letters with the customer information on the input sheet.
What I need:
A macro to populate the input form by working down the records on the data sheet. when the input form is populated with customer information it is to export the letters as a pdf and then move on to the next row of customer details.
I have the export to pdf macro, i just cannot get the input form to loop through the customer records..
