Do Until Loop Or Equivalent Required

Apr 9, 2009

I need a loop function (i guess a do while or do until code) so whenever the word 'Non-Current' appears in colum A enter a 1 in colum E until the word 'Total Non-C' is reached at which point the loop must end.
Such as:
A B C D E
NON-CURRENT 6 4 5 1*
ABSA 4 5 2 1*
BARCLAYS 3 2 8 1*
NED 0 8 6 1*
TOTAL NON-C 4 6 7 0

View 3 Replies


ADVERTISEMENT

Run-time Error 424: Object Required In For Each Loop

Oct 3, 2006

I am running regression statistics for several different columns of data. In order to help me analyze the data, I am organizing the output on a specified worksheet called "Best Subset." I have stored the column names of the data used in different regression calls in a range array called Reg_Labels. The Reg_Labels array may contain one, two, or sixteen column labels in one specific location (where each column label is stored in a separate cell). All possible column names are stored in the Labels array (each name is stored in a separate location). I basically need to check to see which column names were present in a particular array location in Reg_Labels. When a column name from a cell in Reg_Labels(I-1) matches a column name from Labels(K), I am placing an "X" on the designated spreadsheet to indicate which variable(s) has been used.

My For..Each Loop will not work for some odd reason, and I need to figure out why. I keep getting an error that an object is required. I need each cell in Reg_Labels(I-1) compared with Labels(K), and I thought the For..each loop would be the easiest way to do it.

I just don't understand what's going on if C is a range object and Reg_Labels(I-1) is a range object. Wouldn't this loop supposedly cycle through every cell in Reg_Labels(I-1)?

Have I messed up some sort of object reference? ....

View 6 Replies View Related

Modify The Formula Entries In The Following Code To Cycle Or Loop Through All Checks Required

Feb 22, 2009

I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.

View 2 Replies View Related

IFERROR Equivalent

May 19, 2009

I've got a long formula here. If the resulting expression is equal to "0-0" I want it to go blank as if it was an error, and if it isn't, I want it to show the resulting expression as normal.

View 11 Replies View Related

VBA Equivalent Of Weekday()

Dec 19, 2008

I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.

View 8 Replies View Related

Eomonth() Equivalent In VBA?

Aug 27, 2008

if there is an eomonth() equivalent in VBA?

View 9 Replies View Related

Finding Out Whether 4 Lengths Are Equivalent

Aug 18, 2009

I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.

I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.

View 3 Replies View Related

Equivalent Of IN Function From Pascal?

May 28, 2012

I've been learnig VB applying it to automating some excel workbooks. What I'd like to know is if there's an equivalent funtion in VB to the IN function in Pascal. The In function in Pascal used to work like this:

If I used the KeyPress (Function in Pascal), I could evaluate which key was pressed and compare it to an array of options. For Example:

HTML Code:
if Chr(KeyPressed) IN ["0".."9",".","+","-","*","/"] then

Expression = Expression + Chr(KeyPressed)

Instead of Comparing each option separadtly. Now my question is whether or not there's an equivalent function in VB.

View 6 Replies View Related

Equivalent Code For A Formula

Apr 3, 2009

I have two worksheets: "Report" & "Hou Jobs". In Report worksheet, I need a code equivalent to following formula so that it returns value in cell D5.

If the values in cells B5 & B8 change, the value should update itself.

=VLOOKUP(B5,'HOU Jobs'!A4:B667,2,FALSE)

View 9 Replies View Related

Length Equivalent For Numbers

Apr 18, 2007

When I tried len(nYear) where nYear is a number like 2006, I don't get the number of characters in the number. I know that's confusing strings with numbers, but...

Is there a VBA function that returns the number of numbers deep in an integer; ex. if nYear = 2006, the function would return 4?

I could easily do a < or > line of code, but I'm just curious if there's another way to do it.

View 9 Replies View Related

Convert Degrees Into Cosine Equivalent?

Jul 6, 2011

i am trying to finish a spreadsheet off using cos functions. I want to convert 26 degrees into 0.8988. when i use my hand calculator i type 26 in then press cos and it converts it to 0.8988 which is correct, but when i try to use the excel cos function it gives 0.646919322 which is in-correct.

View 5 Replies View Related

Vba SUMIF Equivalent Within Userform Code?

Nov 16, 2013

I have a worksheet (named Deduction Worksheet) that is a running record of all part removals. A removal is denoted by a row entry that contains removal particulars. The first column (Column A) in each row contains a unique number for each part. Column K shows how many part units were removed in that removal:

Column A
... Columns ... B to J ...
Column K
Unq001
various

[Code]....

In the code above 'crng' is the criteria range, 'sValue' the criteria and 'srng' the sum range (which follows the normal SUMIF function within a worksheet). Both the 'crng" and 'sValue' values contain text, / and numbers.

note that other elements on the userform code look at data on other worksheets (not sure if this will effect this).

View 5 Replies View Related

SumIf: Change The Criteria To Be The Equivalent Of A1 OR B2

Jun 9, 2009

I've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet.
AB
1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)

All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.

View 3 Replies View Related

Finding Equivalent Of Maximum Value In Another Sheet

Jan 26, 2010

I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").

Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.

View 9 Replies View Related

Text Equivalent Of SUMIFS Function

Mar 8, 2013

When I need to return numerical data with multiple criteria, I use the SUMIFS and it works fine. But when I need to return a text string, the function is not called for. What function can I use to make this happen? I've tried SUMPRODUCT, which I've had success with in the past, but it's not working this time. Maybe I'm laying it out wrong, but this is how I currently have it:

=SUMPRODUCT(column of text to be returned,((column of criteria1)=criteria1)*((column of criteria2)=criteria2))

Do I need to fix this formula or use something else?

View 2 Replies View Related

VBA Equivalent For Shift-Arrow Keys

Nov 7, 2007

Does anyone know the VBA equivalent to using the Shift-Arrow keys on the keyboard? I am wanting a macro to do this for me. Specifically I am wanting to use Shift-Arrow Right.

View 9 Replies View Related

Function That Is The Equivalent Of WORKDAY But For Hours Instead

Mar 14, 2007

I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

View 6 Replies View Related

Equivalent Of A Double Conditional In The Function SUMIF?

Mar 9, 2014

I need the equivalent of a double conditional in the Function SUMIF.

In my example (a test file is attached), I have used hypothetical stock trades as a test case.

Column B has the type of order (Buy or Sell).

Column C has the stock trading symbol.

Column F has the net amount of the transaction on that line.

I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.

So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.

I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.

So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.

If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.

I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.

View 9 Replies View Related

SQL Query Or Equivalent Filter Based On Condition?

Apr 16, 2013

There is a way to use SQL sentences to do queries in a table in Sheet1 (A to F) to put the output in Sheet2 the values in columns A, B and E from table in Sheet1 when values in column B are equal to 5?

I mean, is possible to do this within the same workbook? or a equivalent VBA code?

View 8 Replies View Related

Converting Feet/inches To Decimal Equivalent

Feb 18, 2006

I am new to Excel but not programming and I am looking for a recommendation for the following. I have a spreadsheet that simply takes the length and width of an area and computes the square feet and yardage and other sundry items. I am entering the feet/inches as follows:

Example: 11.3 (equals 11/ft 3/inches)

The correct decimal conversion should be 11.25 but, obviously, it does not know that the number to the right of the decimal point is an indicator of inches. (ex.: .5=.42, .7=.58, .9=.75, .11=.92)

I have approached this from the stand point of an IF condition, finding the position of the "." and grabbing everything to the right (+1) but I understand that the limitation is 7 nested IFs.

Can someone get me kick-started on what the best approach would be to get my entry to convert to the true decimal equivalent? Currently, I am simply doing the conversion from memory but I would rather automate this sometimes errant approach.

View 9 Replies View Related

Find Top Equivalent Values In Excel Range Without Sorting?

Mar 29, 2014

I know the formula =large but my problem is the formula for the equivalent value for it. Note: In my xcel file attached you can see there are multiple items with the same total numbers and also blanks

View 3 Replies View Related

USD Equivalent In Column Based On The Exchange Rate At The Time

May 24, 2007

Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C

And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.

In Sheet 2 I have a table of the historical exchange rates like this:

DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089

View 9 Replies View Related

Change A Date To Its Equivalent In Terms Of Quarters System

Jan 23, 2009

I have the formula where i can change a date to its equivalent in terms of quarters system with a date:

=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),"Winter","Spring","Summer","Fall")&YEAR(A2)

This is a school year configuration.


ex.
A2 = 10/1/2005: with the formula up there it turns into Fall 2005
i want to be able to add any number of years and the formula will still come up with the quarters system

also i would like A2 to be stationary and create a list of quarters for each year i add on

ex.
A2= 10/1/2005 B2=Fall 2005
B3=Winter 2006
B3=Spring 2006
B5=Summer 2006
B6=Fall 2006
etc.

If this is all possible lastly I would like to negate summer quarters

View 9 Replies View Related

Conditional Formula To Convert Number With Positive Value To Its Negative Equivalent

Aug 27, 2012

I am looking for a conditional formula that can be applied to a cell so that when I enter a number with a positive value, it:

1) Converts it to its negative equivalent value.
2) Displays in the value in the cell with parentheses around the number to indicate that the value is negative.

I am trying to do this without having to reference another cell to provide the entry or display the outcome.

View 1 Replies View Related

Worksheet/formula That Automatically Look For The Equivalent Score In A Given Transmutation Table

Mar 25, 2003

how can i make a worksheet/formula that automatically look for the equivalent score in a given transmutation table.

example: i have an exam of 10 items, what will be my formula to so that it will automatically look for the equivalent score under the transmutation table of 10 items

View 9 Replies View Related

Loop Within A Loop (repeat The Loop Values)

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

Cursor Home (equivalent Of CTRL (Home) To Move)

Jul 31, 2009

Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success

View 3 Replies View Related

Using VBA To Get System Time In GMT Equivalent Instead Of Local Time

Feb 19, 2009

if there is a simple way to get the GMT equivalent in vba code. I am being a date/timestamp but realized that timezone differences may complicate matters, therefore I would like to "standardize" the timestamps into their GMT equivalent rather then using the computer's local time. I had been using

View 3 Replies View Related

"MAXIF" Function, Or Equivalent?

Dec 1, 2008

I have a spreadsheet that I'm working on that compiles survey data from an online survey. I have averages, high scores, low scores, etc. figuring off of my data to product charts and graphs for a client. I am attempting to find the high and low scores for individual surveys (there are 1054 surveys total right now). I know of max and min, but I really need something that would function like a "maxif" (which I realize does not exist).

Here's the problem:

In column A, I have a list of insurance provider names per survey (so, 1054 entries total). I then have some columns in between that show scores on questions from the survey, and then column AB contains my averages for each individual survey. Column AD has a list of the insurance provider names in alphabetical order (there are 167 unique provider names used in the 1054 surveys). Column AF is my high score column and lines up with column AD (so, I am looking to have 167 high scores in total as I want the high score per each provider, not survey). Let's pretend A2:A10 say "Anthem Insurance." I want to create a formula that would basically say something like:

Count what rows in column A = "Anthem Insurance," then match those cells to AB (so, it would figure since A2:A10 are the targeted cells, then I also want to correspond to AB2:AB10) and give me the high score (MAX) for that range of cells. Is there a way to do this? Otherwise, I have to manually go through all 1054 rows and see what range of cells equal a certain insurance provider name to get those 167 high scores. I can keep doing this if I have to, but this data changes every single week and it eats up a lot of time.

View 4 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

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







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