Isnumeric Function - Getting Range To Extend By 1 If Not Numeric

Apr 29, 2014

So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.

Right now the initial code is:

Code:
Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells

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

Basically I tried to amend this as follows:

Code:
Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim rs, cellcount, rangecount As Long
Dim cll As Range
ups = 0

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

Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.

View 1 Replies


ADVERTISEMENT

Extend Range To Longest Column

Aug 7, 2007

I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String

If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT

My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.

View 9 Replies View Related

Extend Range Of Table Automatically To Fit Data

Jul 14, 2014

I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.

My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.

Could a OFFSET formula be employed in any way?

I have attached an example file.

The macro is:

[Code] .....

ExpandTableExample.xlsm‎

View 1 Replies View Related

IsNumeric Command To Look In Each Cell

Nov 4, 2008

I wrote this code to find values greater then 150000 and replace it with the value in the previous cell in column 2 staring at row 2 till the last cell that contains a value.

I used the IsNumeric command to look in each cell. I ran the code and it changed all the numbers that were above 150000. Then before it finished the error came up. It is a Aplication-Defiend or Object-Defiend error. why this is happening? Also is there any way that I can use the IsNumeric command in the While condition line?

View 5 Replies View Related

Excel 2007 :: Formula Range Auto Extend Doesn't Work?

Jul 31, 2014

I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.

I check that I need to turn on the auto extend check box in option, I check and it is already on.

View 3 Replies View Related

MID Function :: Numeric, Alphanumeric Or Alpha

Mar 2, 2010

A2 cell data = 11 digits alpha-numeric number.

* begin with either "0" or "1"

I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, or Alpha) and compare it with the cells under "'PSCCR Queue - Manager" TAB from C4 thru C7.

Below formula is only looking at the first value when comparing.

=IF(A2="","",IF(ISNUMBER(MATCH(MID(A2,6,2),'PSCCR Queue - Manager'!$C$4:$C$7,0)),"ITS","Non ITS"))

View 9 Replies View Related

Addition Of Alpha Numeric Using Function COUNTIF

Mar 18, 2014

I am trying to create a shift roster for my team. My team work in general shift (GS), second shift (SS), third shift (TS). There are situations where some team members come in any of these shift but had to leave midway due to feeling well, so that should be counted as half day. This half day scenario may be in any case GS/SS/TS. I am using COUNTIF to get the sum of GS/SS/TS for each employee which is giving a whole number. I also want to incorporate 3 different variations of half day as Half Day GS (HG), Half Day SS (HS), Half Day TS (HT). My expectation is that for each Half Day only 0.5 should be added in a appropriate shift, but COUNTIF is always counting this as 1. How I can achieve this using countif or any other Excel function? Sample sheet is attached.

View 3 Replies View Related

Changing Cells From Alpha To Numeric And Now My If Function Won't Work

Mar 18, 2009

I had an "If" formula that would grab my numeric data from one cell and dump it into the corresponding cell depending on my letter code. I had to change my code vales from apha to numeric. I enclosed the attached spreadsheet and highlighted the cells in yellow that are in my formula that don't work any longer. I was hoping someone may have a solution.

View 5 Replies View Related

Excel 2011 :: Giving Text Fields Numeric Value For Use In Function?

Feb 15, 2014

I am creating a spreadsheet for results of a survey. I am trying to give numeric values to text fields so that I can place the cell values later into a SUM function. The text fields are entered into the spreadsheet via a drop down list in each cell I created by utilizing the validate button. The text field contains choices like The text field contains choices like “1 Very poor,” or “4 Very much.” The source for the list is on a separate sheet.

EX: If cell C5 equals the choice “1 Very poor,” and cell C8 equals “4 Very much” I want the cells to have numeric values of 1 and 4 respectively so that I can utilize an equation like =SUM(6-C5+6-C8) later in the spreadsheet to calculate aggregate scores.

I am using a 2011 for Mac version

View 5 Replies View Related

Validate For Numeric Range & Alphanumeric Range

Dec 20, 2007

Can anyone help me to get a validation to check that what is entered in a cell, lets say A1,
has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.

View 9 Replies View Related

Getting Last Numeric Value In A Range

Jul 9, 2008

I am using this formula: =INDEX(A5:Z5,MATCH(9.99999999999999E+307,A5:Z5)).

All the cells in the range A5:Z5 contain SUM formulas for adjoining cells in rows 1 to 4. The above INDEX formula doesn't work in this situation because many of the formulas in row 5 return a 0 value because there is no data in rows 1 to 4. If I delete the formula from all these cells, the INDEX function works OK, but I'd rather not do this.

Is it possible to amend this formula so that it returns that last numeric value >0?

View 9 Replies View Related

Find Value Between Numeric Range

Oct 12, 2007

I have been looking for code samples using Find to search for values within or outside a given value range. I haven't come across any that are obvious to me so is it possible?

I am using a piece of code that jindon here on Ozgrid has so kindly been helping me with. The code below searches for a certain value and returns that value when found along with other values. I am trying to adapt this same code to work on other searches I perform.

An example I am looking at is finding any values in column "K" which are either less than 700 or greater than 1300. How would I alter this code to perform a search like that?

Option Explicit
Sub test7()
Dim r As Range, ff As String, txt As String
With Sheets("Sheet1")
Set r = .Columns("av").Find("D00025", , xlValues, xlWhole)
If Not r Is Nothing Then
ff = r.Address
Do

View 9 Replies View Related

Clear Cells In Range That Are Not Numeric

Mar 28, 2007

I am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this.

I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.

View 9 Replies View Related

Check If Any Cells In Range Are Numeric

Nov 4, 2008

If I have the following cells and values

A1 = 0
A2 = 0
A3 = ""
A4 = 0

I want a vba if statement that makes A5 = "Numeric.

If I have:
A1 = 0
A2 = 0
A3 = A
A4 = 0

I want A5 to = "Non Numeric"

Similarly if:
A1 = ""
A2 = ""
A3 = ""
A4 = ""

I want a5 to = "Non Numeric"

Is there a way to do this without looping through each cell in the range?

View 9 Replies View Related

Determine Values In A Range Are Numeric

Apr 13, 2007

I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.

View 7 Replies View Related

Copy Values Outside Of Numeric Range

Sep 11, 2007

I have around thirty columns I manually search using AutoFilter to find values outside certain ranges. The ranges are different for every column and I copy/paste the out of range values onto another sheet. I am wanting a marco to do this for me.

Here is what I have worked up so far. I thought Select Case would be the easiest to adapt for each column by just changing the values.

Sub ColumnCase()

Sheets("10AL3-27").Select
Range("K1").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste

Column K is the first of the columns I sort. The code above seems to be trying to paste the entire column rather than only the cells with values outside the range.

What would be the best way to select the cells which are out of the particular range?

View 9 Replies View Related

Count Cells Between Numeric Range

Dec 29, 2007

I am trying to total the number of cells in a column that are 0 or greater but less than 365. I use countif for for a specific digit but cannot compute a conditional range using it. I need to count the number of positive digits btwn 0 and 365. The column is a computation of the difference btwn today and past and future dates. A plus digit means we missed sevice and better be ready for an angry customer.

View 3 Replies View Related

Color Cells Within Numeric Range

Jan 10, 2008

Trying to make an excel macro that changes the background of a cell dependant if the value is between one number and another or equal to another number.

Cell values =
a1 = 250
a2 = 475
a3 = 715

vba Example:

Case Is > 200 and < 400
colchoice = 4
Case Is >450 and < 550
colchoice = 5
Case is >600 and <700 or = 715
colchoice = 6
If i run the macro the cell background should be
a1 =4
a2 =5
a3 =6

View 4 Replies View Related

Count Numbers Within Numeric Range

Jan 15, 2008

I have several cells that are either positive or negative values and the values are expressed in percentages. I'd like to know how many of the cells are over/under certain percentages. For instance, how many of the cells are over negative or positive 2%? I've tried the COUNTIF function using 2%, 4% and so on, but I always get back the same cell count regardless of the criteria I use.

View 7 Replies View Related

Clear Numeric Cells In Range

Jan 19, 2008

I am trying to code a command button to clear a range of cells if any of them contain a numeric value. For example. If any cells of cell range A1:C10 contain a numeric value then they would be cleared. Not all the cells in the range but only thouse contaiing numeric values. I have tried various methods with not-so-good results.

View 9 Replies View Related

Sum Based On Numbers Being Within Numeric Range

Mar 31, 2008

I'm trying to add up some values based on a criteria in another column. I have numbers in column A ranging from 0 - 100 and in column B have totals for those numbers. What i am trying to do is Add up the totals in column B based on the following criteria's in Column A.

<7, >7 & <=30, >30 & <=60, >60.

I have a summary page to display the individual results in separate cells.

View 5 Replies View Related

Mark Numbers Between Numeric Range, Above Or Below

May 21, 2008

how to put together a function that will provide for me 1 of 3 results, ( lower than, higher than or similar to). What I would like to do is have a function to have the results for column B, be either "lower than", "higher than" or "similar to" based on column A. Column A will have a range of %'s from -100% to 100%. What I would like happen is the following results for cloumn B, based on column A. If Column A is:

Between -5% and 5% - Then cloumn B = "similar to"
>5% - Then cloumn B = "higher than"
>-5% - Then cloumn B = "lower than"

View 3 Replies View Related

Extracting A Numeric A Specific Numeric String From A Description Cell

Oct 22, 2009

I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .

is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???

View 9 Replies View Related

Compare Alpha And Numeric Values Within A Range

May 4, 2006

I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

View 9 Replies View Related

Return Value Based On Cell Being Within Numeric Range

Apr 19, 2009

I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".

Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.

I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.

View 4 Replies View Related

Lookup Based On Numeric Range Scope

Nov 19, 2009

I have a spreadsheet and Im trying to create several different staffing scenarios
Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. Im trying to have excel give me the staffing value if the current number in cell B5 is a certain value so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?

View 3 Replies View Related

Pass Random Numbers Between Numeric Range

Aug 29, 2007

i modify the result but the problem is that when i try it the result alway have a higher number i cannot have a low number...

i want to have the reult from 1 to 1899

here is the

Private Sub CommandButton1_Click()
Randomize
TextBox1.Value = Int(Rnd * 1)
TextBox2.Value = Int(Rnd * 9) + 1
TextBox3.Value = Int(Rnd * 9) + 1
TextBox4.Value = Int(Rnd * 9) + 1
End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub TextBox3_Change()

End Sub

View 5 Replies View Related

Rate Numbers Based On Numeric Range

Apr 8, 2008

In the attached excel file in column C, beginning on row 4, are numbers. These numbers will automatically update on my bloomberg terminal.

(1) I would like to then in column E to have a rating given to those numbers based on the below scale. The way the scale works is that if its 0.6 its a 10. If its anything slightly above 0.6 exactly, its a 9. A 10 is also a 0.5 and any number in between 0.5 and 0.6. The scale is below.

(2) Given that the formula that is being updated is a bloomberg excel formula (in the attached document I pasted it as values), will an excel formula be able to read the updated number in the bloomberg formula? If not, in column D is there a way to have it automatically paste D as values (and update each time column C changes or workbook is saved), and then have column E read off of those numbers.
Scale:

10 = 0.5 to 0.6
9 = 0.6 to 0.7
8 = 0.7 to 0.8
7= 0.8 to 0.9
6 = 0.9 to 1.0
5= 1.0 to 1.1
4= 1.1 to 1.2
3= 1.2 to 1.3
2= 1.3 to 1.4
1= 1.4 to 1.5
0 = anything greater than 1.5

View 5 Replies View Related

Delete Rows With Values Between Numeric Range

Apr 16, 2008

I want to delete all the rows with values between -1 and 1 in column 'I'. I currently set up a macro to do the formatting and conditional stuff that highlights all values above 1 and below -1 - these are the values I need to conduct my analysis on, but I can't figure out how to delete the rows in between. The worksheet has approximately 5,000 rows (and growing) and this would help me clean up the data significantly.

View 7 Replies View Related

Color Cells Within Numeric Range/Scope

May 27, 2008

I'm trying to do some sort of a league table

I have something like this with data in around 1000rows there is text in collums A B D and E.

A B C D E
1
2 100
3 60
4 30
5 20

and i would like to do this:
if value in cell x in collum C is over 80 then background color in Ax,Bx,Cx,Dx,Ex is yellow
if value in cell x in collum C is between 60-80 then background color in Ax,Bx,Cx,Dx,Ex is brown
if value in cell x in collum C is between 40-60 then background color in Ax,Bx,Cx,Dx,Ex is blue
if value in cell x in collum C is between 0-40 then background color in Ax,Bx,Cx,Dx,Ex is red

Obviously i cant use conditional formating because i have more then 3 conditions.

Does anybody know what code to use for VBA?

View 6 Replies View Related







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