Nested Loop To Divide Adjacent Row By Column

Jan 18, 2010

I 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.

View 14 Replies


ADVERTISEMENT

If Column A = 0 Then Adjacent Cell In Column B Divide By .3

Feb 27, 2009

Im wondering if this can be done in excel..

2 columns

if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%

this is the way I was trying to do this...

=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)

I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?

View 9 Replies View Related

Concatenate Column With Adjacent Column In Loop

Sep 27, 2007

I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.

View 9 Replies View Related

Nested Loop. Inner Loop Not Running

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

Nested For Each Next Loop

Feb 17, 2007

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: ...

View 9 Replies View Related

Creating Nested For Loop

Feb 4, 2013

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.

View 2 Replies View Related

Nested For Next Loop With VLookup

May 25, 2014

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].....

View 3 Replies View Related

Nested Control Loop

May 21, 2009

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.

View 9 Replies View Related

Speed Up Nested Loop

Jul 20, 2006

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")......................

View 2 Replies View Related

Nested Loop Hangs Excel?

Jul 4, 2014

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.

View 8 Replies View Related

Nested Loops. Start & End Loop

Dec 5, 2006

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

View 3 Replies View Related

Nested Loop To Concatenate 2 Columns Of Data

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

Nested For Next Loop W/ Multipule Arrays Sequence

Oct 30, 2009

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

View 9 Replies View Related

Nested Next Loop: Evaluate The If Statement Below Over A Range Of Cells

Aug 22, 2008

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.................

View 9 Replies View Related

Nested Loop Not Working; Pulls Data From Multiple Files Into One Table

Aug 8, 2006

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 ........................

View 5 Replies View Related

Add Two Columns Together And Then Divide The Sum By Another Column

May 11, 2009

I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:

4+4/636 = 79.5

d2+f2 (divided by) h2 = 79.5

View 3 Replies View Related

Divide Value In Column A By Data In B:AO Using VBA

Aug 15, 2013

I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).

There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.

View 1 Replies View Related

Divide Without Using Temp Column

Feb 16, 2007

In A1 I have the value 60 I want it in 1:00:00 format

What I do is, I divide that by A1/24*60 and the copy that and paste-special-value on A1

Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?

View 9 Replies View Related

Divide Cell By The Number Of Populated Cells In A Column

Apr 11, 2014

I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.

test sum skip blank cells.xlsx‎

View 2 Replies View Related

Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria

Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A
B
C

1
Cat 1
January 1, 2014
John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.

06/05/2014
D.O.001

[Code] ........

View 6 Replies View Related

Formula To Total Values In Column Matching Text In Adjacent Column

Dec 20, 2013

Formula(s) to do as explained in the attached example.

Example_formula.xlsx

View 9 Replies View Related

Formula To Match Text In Column And Retrieve Adjacent Column Value?

Mar 5, 2014

I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.

If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)

Capture.JPG

View 6 Replies View Related

Copy Cells From Column To Adjacent Column If Bold Or Empty

May 20, 2008

I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.

The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.

View 5 Replies View Related

Sum Of Values In Column If Text In Same Row Of Adjacent Column Matches?

Apr 10, 2014

I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:

A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana

B1 - 3
B2 - 2
B3 - 1
B4 - 1

I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.

View 1 Replies View Related

Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies View Related

Find Value In Column And Return Value From Adjacent Column

Jan 30, 2008

I have are two worksheets, "Sheet 1" which is a bill of lading form and "Sheet 2," in which columns A and B contain Part Numbers and Weights respectively.

What I'm wanting to have happen is when a user inputs a part number into Sheet 1 (say in cell B6), it calls a UDF that looks up the part number on sheet two column A and returns the corresponding weight from column B (to say cell C6 on sheet 1).

View 7 Replies View Related

Nested Vlookup The First 4 Digits In Column

Feb 12, 2009

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'

View 4 Replies View Related

Nested IF To Enter Information Into Column 'A'

Jan 18, 2010

I am trying to use a nested IF to enter info into col A. It is based on 4 pieces of info, I can get the first 3 to work but am struggling with the last part. The problem is that I am looking at the same cell for the first 3 parts but the last part is looking at another cell reference. A nested IF may not be the way to go. An example of the spreadsheet is as follows:

A B C D AB
1 12/1/10 Comp 1 Surveyer 1 18/1/10
Omit 11/1/10 Comp 2 Surveryer 1 Omit
0 10/1/10 Comp 1 Surveyer 2
Comp 3 Surveyer 1

The formula I am currently using in column A is:
=IF(AB3="Omit","Omit",IF(AB3,1,"0"))
This works for the first 3 pieces of information but however does put a 0 in col A if there is no date in col AB, but this what I expect as it's what I've asked for. I am missing the last piece of information.

But what I want to put in column A is:
If there is a date in column AB - enter 1
If there is the word 'Omit' in col AB - enter Omit
If there is no date or the word Omit in col AB - enter 0 and finally
If there is no date in col B - leave blank (if there is no date in col B, col AB will always be blank)

View 5 Replies View Related

Nested IF(SEARCH The String In Column)

Dec 17, 2008

Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines):

=IF(SEARCH("Appeal",A1),"Appeal",IF(SEARCH("Grievance",A1),"Grievance",IF(SEARCH("Complaint",A1),"Co mplaint","")))

I've tried entering it as an array, but that also fails. This seems like a pretty simple problem ,but I've spent several hours trying to make the formula work so I'm obviously doing something wrong.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved