Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Single Element Of An Array Is Equal To A Particular Value


How can you test that no single element of an array is equal to a particular value?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sqaure Every Element In A Dynamic Array
I am trying to sqaure every element in a dynamic array and display the result . I donot understand how can I select the value in the cell using VBA?

Dim Y as variant, d() as double, i as long, j as long, rows as double, cols as double
Set Y = Application.InputBox("select the matrix: ", Type:=8)
Rows = UBound(Y)
Cols = UBound(Y, 2)
ReDim d(1 To Rows, 1 To Cols)
for i = cols
d(1,i) = ______==>
How do I select the value of element in that particular cell and how do I sqaure it?
I know
cells(rowindex, columnindex)
is used to select a particular cell but If I have a large array it would be difficult to go cell by cell and sqaure it.


View Replies!   View Related
Calculate Each Element In A Multidimensional Array
I need to multiply matrix variable by a constant (each matrix entry has to be multiplied by the constant).

Sub Matrix()
Dim X As Variant, Y As Variant
Dim a As Integer

a = 2
X = [1, 1, 1; 2, 2, 2; 3, 3, 3]

Y = X * a ' Here it writes that type is mismached

End Sub

I read that in cell functions it is possible to do such calculations.

View Replies!   View Related
Pass Variable Into Array As Element
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?

1 click - a1 = ..
2 click - a2 = ..
....
n click - an = ..

If not possible - which way to search a solution?

View Replies!   View Related
Assigning An Array Element To Select A Different Cell -Help
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....

View Replies!   View Related
Multiply Each Element In A 6x6 Array By A Similar 6x6 Array
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.

Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padro em TestA com _
'resultado na matriz em TestA correspondente matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............

View Replies!   View Related
Check Whether Transpose Ranges Are Equal In Single Cell
How a single-cell formula to check that 2 transpose arrays are equal.

For example, A1:A5 are {1,2,3,4,5}

AND

B3:B8 are {1,2,3,4,5}

Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.

View Replies!   View Related
Set Array Equal To Part Of Another
Is there a way to set an array equal to part of another 2D array. If say I have an array with 5 columns. Is there a way to break that up in to two arrays one with 3 and one with 2. Even getting it to 5 1D arrays would work. I bring it in as 1 array but I need the information in different places. This is actually only the output. So if there is a way to send only part of the array out that would work to. I know you set the range smaller than array with range=array, but that still doesn't get me where I need to be. This is obviously without simply going through a loop. I could set it to 5 singles with a single nested loop I know. I was looking for a non-loop way.

View Replies!   View Related
Set All Values In Array Equal Same Value
how to set all values in an array to be the same value. Currently I am using code in a For/Next loop like the following to do this:

Sub testing()

Dim ArrayToAllHaveSameValues(1 To 30) As String, i As Integer

For i = 1 To 30
ArrayToAllHaveSameValues(i) = "Value"
Next i

End Sub

View Replies!   View Related
Get A Single Cell To Look At An Array
What Im trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. Ive attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}

But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.

View Replies!   View Related
Sumproduct Of 2 Criteria In A Single Array
I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand. Here is my formulae: =SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10)

ABCD
1ValueQtyProduct
2299.944PXT
3186.53PXT
4711.071PCA
5561.862PCT
6608.961PXT
7520.026PCT
8427.682PCA
9397.341PCA
10387.664PCT

View Replies!   View Related
Table Of Calculations Down To A Single Array Formula
I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.

The table data is very simple, and can be in just two columns:

1, value
2, value
3, value
4, value
5, value

Where 1 thru 5 are time periods, and values are various numbers.

I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be

=Gammadist(5,x,y,TRUE) (x,y values not important here)

and the formula for the second value would be:

=Gammadist(4,x,y,TRUE)

The only thing that changes is the period number.

So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)

I can get this far:

={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}

But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.

View Replies!   View Related
Conditional Format Array From Single Cell
This has been kicking my can all morning! Should be simple. I'm trying to conditionally format an array from the value of a single cell.

=IF(A1<80, A3:A24,RED,0)
=IF(A1>80,A3:A24,GREEN,0)
=IF(A1>120,A3:A24,BLUE,0)

View Replies!   View Related
Single Cell Array Or Sumproduct Formula
To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.

View Replies!   View Related
Function To Return Single Dimensioned Array
I am trying to write a function that returns a single dimension array from inputed data. I want this to work for any data, i.e. a single cell, an array in vba or a range. I am using the "for each" staement. However, when I pass an array in the "for each" returns the same range, rather than the elements of the range. I've played around a bit, and the "for each" does what I want on an range if I am not passing the range to a function. Is it because I am passing the range into a function as a variant? Here is the code for the function.

Public Function CreateSingleDimensionArray(ByVal dataToConvert) As Variant

Dim vHolder As Variant
Dim vArray As Variant
Dim lElementCount As Long

lElementCount = 0
For Each vHolder In dataToConvert
lElementCount = lElementCount + 1
Redim vArray(1 To lElementCount)
vArray(lElementCount) = vHolder
Next vHolder
CreateSingleDimensionArray = vArray

End Function

View Replies!   View Related
Convert Byte Array Containing Floating Point To Single
I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?


View Replies!   View Related
Lookup Single Value In One Array, And Return Multiple Values
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.

View Replies!   View Related
Write Array To Text File On Single Line
In VBA, is it possible to write to a text file, on 1 single line (without carriage return or line feed) the values of a dynamic array? For instance, I have the array:

Dim my(n) As Double 'n is changeable
my(0) = 0
my(1) = 1
my(2) = 2
.....

I want to write to a text file, like this: Write #filename, my(0), my(1), my(2) '..... until my(n) but n is changing

I want them on 1 line, because I use the other direction (up - down) for other things).

View Replies!   View Related
Open Text File & Use Single Array For FieldInfo Argument
I have below code apearing couple times in my macro and I'd like to make a variable.

Plese see belowe

Workbooks.OpenText Filename:= _
"S:ReportsMy ReportsGuidefilename20080430", Origin:=xlMSDOS, _ ' filename should be another variable as it referes to different files
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), _
TrailingMinusNumbers:=True

View Replies!   View Related
Values Which Are Equal Not Treated Equal
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.

View Replies!   View Related
For Statement With A Next Element
I'm trying to do, I already have a macro that takes certain cells in sheets in a workbook and copies to them to individual workbooks that are open.

So for example, I have a workbook with sheets A, B, and C.
and then I have a macro that'll copy sheet A to an open workbook D and B to an open workbook E, and so on.

Problems I'm running into = if the workbook isn't open, it crashes, I'd like to have an if then statement where if workbook is not open, it stops?

Additionally, I'd like a for statement because there will be 10 or more sheets in total, so would be nice to have a for statement that changes the variables so For i = 1 to 10 where 1 would be D and 2 would be E and so on so it automatically changes the workbook names as necessary.

View Replies!   View Related
Count Number Of Element
For example, I have 2 columns:
john------2
rick-------1
andrew---3
john------5

I want on the third and fourth column the result like this:
john------7
rick-------1
andrew---3

I want to count automatically the number of elements taken by john, rick and andrew.

View Replies!   View Related
Return A 4th Element From 3 Common Ones
My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.

What functions will allow me to get the item number information I want from Report 2 into Report 1?

I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.

View Replies!   View Related
Formula For Parsing If An Element Is True
My rows consist of the following cells, an author(s), an article name, a pubilcation name, a volume number, and a page number. There are thousands.

My first column has multiple names and need to separate the names into separate cells. Some names have a comma or semi-column separating them, some have the word "and" separating them. Most of the names are listed as last name, first name. (Some are not and I will have to deal wtih that later.)

Row 1:
Eisenhofer, Jay W., Jordan, Kirk S. Tucker, Marc B."Business Faces Harsh Sentencing Penalties",Delaware Business Review, October 7-13, 1991951

Row 2:
Embley, Craig M., Turezyn, Andrew J. and Welch, Edward P."Recent Developments in Delaware Corporate Law",S.A.S.M.& F., May, 19901443

I'm sure is a multiple part process. Is there a formula for placing anything separated by a comma into a new cell, or placing a tab between the names?

Ultimately this data will be placed in database from which to search for a name and article, etc.

View Replies!   View Related
Dynamic Table_array Element In Vlookup
how to get the table_array element of vlookup to either obtain details from a worksheet cell, or to enclose a variable (specifically part of the filepath) within the table_array formula. I have a template that gets saved and used in various directories, and I want Vlookup to to dynamically lookup information from a specific file that is also contained within the directory, without having to find and replace the directory name in the vlookup formulas.

View Replies!   View Related
Create X Element List From X Words
A friend of mine is playing a computer game, and he has to create 50 different potions to obtain her next goal. She would like to know if it's possible with excel to generate a list of potions based on 7 different elements. Here are the 7 elements : Black orchid, Cactus, Lotus, Pitcher plant, Rose, Tiger lily

Every elements could be repeated to generate one potion.

Therefore, the following combinations are valid :

Cactus - Cactus - Cactus
Cactus - Rose - Cactus
Cactus - Cactus - Rose
Cactus - Rose- Rose


As long as there are no more than 3 elements, it's ok. I'm not looking for the precise coding, I just need to be pushed in the right direction...

View Replies!   View Related
A Function For Calculate The Cells Takes To Appear Each Element
I want to obtain from some elements the number of cells it takes to appear:
We have for example A,B,C, and D,
and they appear in the next order:

1A
2C
3D
4A
5D
6B
7C
8A
9A

What I want to know is how much last in appear each element.

1A1
2C2
3D3
4A3
5D2
6B6
7C5
8A4
9A1

For example, the first A last one in appear, but the next element C last two in appear. In the forth line again cames the A, then are three cells. The C was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two A, then in the cell nine takes one cell in appear again.

View Replies!   View Related
Type Mismatch Error In Returning Range Element
For some reason get a type mismatch error on when I try to determine the rth member of summation range. I have highlighted the relevant part of the code in bold. It is strange as I can obtain the address.


If ((All_nurse_names(r, 1) = nurse_name) And (All_status(r, 1) = status)) Then
Debug.Print "test " & r & " " & summation_range(r).Address & " " & val(summation_range(r))

total_hours_in_shift = total_hours_in_shift + (summation_range(r))
End If

View Replies!   View Related
Pass Element Of User-Defined Data Type
I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.

So the definition is:

Dim Array(1 To 10) As UserType

and access to elements is

Array(5).Element1

The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.

I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.

View Replies!   View Related
Find Multiple Instances Of Single Criterion In Row & Return To A Single Col
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Scenario:
Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

View Replies!   View Related
Countif- Where Column A Is Equal To A Specific Number And Column B Is Equal To Another Number
I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.

both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.

View Replies!   View Related
Converting 3x10 Array To A 1X30 Array To Run A Match Formula
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View Replies!   View Related
String Array Values To Array Of User-Defined Types
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:

View Replies!   View Related
Filter An Array (the Longer One) Using The Shorter Array As The Criteria
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method

IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.

View Replies!   View Related
Equal To Or Less Than
I have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution.

I need the following to go into one formula:

1. =< 24 needs to show the number 24
2. =< 30 but > 24 needs to show 30
3. =< 32 but > 30 needs to show 32
4. =< 36 but > 32 needs to show 36
5. =< 40 but > 36 needs to show 40
6. =< 48 but > 40 needs to show 48
7. =< 50 but > 48 needs to show 50
8. =< 60 but > 50 needs to show 60
9. => 60 needs to show 60

I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8
3x10, 4x10, 5x10
3x12, 4x12, 5x12

So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8.

I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.

View Replies!   View Related
Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)
If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H



View Replies!   View Related
First Array Remain Same,second Array Always 11 Cell Added
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3
etc

can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can


View Replies!   View Related
Sum Array In VBA And Clearing An Array
1) after I have made an filled an array with data.
Dim orders(100000 To 200000) As Double
I want to then sum the orders above and below a number, lets say
orders>orders(150000)
and
orders

View Replies!   View Related
Looking Up Row Array Data In Column Array
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8

What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want

=sumif(A84:A89;"x";'X-ref'!T4:Y4)

but it doesn't work since the first range is an column-array and the second range is an row-array.

View Replies!   View Related
Not Equal To Operator
revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.

View Replies!   View Related
Several Criterias Equal Different #
I am having trouble creating a formula that will do what I need it to do. In C4 the I already have a formula that states if B4 is greater than 94.99% then it will equal 5. What I need it to do is if it is equal to or greater than 95% then =5. Also if it is equal to or less than 94.99% - 92% =3 and if less than 91.9% =1. Here is the worksheet I am using....

View Replies!   View Related
If Equal To Or Less Than Formula
I am trying to set a scorecard up where it will give points depending on the % actual. For example:

if less than 60%, then 0,
if equal to or greater than 60% but less than 75%, then 1
if equal to greater than 75% but less than 80%, then 2 and so on.

I have been playing around with the example below, but can't seem to get it working.

[php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]

Maybe it 's because I pickled my brains last night.

View Replies!   View Related
The Smallest Value That Is Greater Than Or Equal To
I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.

View Replies!   View Related
Sum IF (2 Equal Values)
I've created the following function that chooses the maximum value from a set of cells then inserts theappropriate row number (within a table) into a new cell.

=IF(J27=0,"?",IF(J27=J19,1,IF(J27=J20,2,IF(J27=J21,3,IF(J27=J22,4,IF(J27=J23,5,IF(J27=J24,6,IF(J27=J 25,7,IF(J27=J26,8)))))))))

It's working fine until I have 2 cells with the highest value. The above statement is entering the first cell that meets the criteria in the new cell but ignores the fact there maybe 2 (or more) of the same value.

How can I get both (or all) to be entered in the same cell? Is there a better way, maybe highlighting all the rows in the table that equal the max figure?

View Replies!   View Related
Advance Filter For Not Equal To Zero
Here is a copy of my code and I am Having a problem trying to get it to filter zero balances out. There are positive and negative numbers and I need to post them to a seperate sheet and exclude all zeros.

View Replies!   View Related
Randomize With No Equal Numbers
I'm trying to get a random sequence of numbers e.g. 1-20 so that no number is the same i.e. no number appears twice.

I used Int(20 * Rnd + 1) + 1 but this gives me same numbers. Any ideas how i can do that?



View Replies!   View Related
Greater Than And Less Than Or Equal To Formula
I need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.

I think it would be something like:

SUMIf(H40>"32",

View Replies!   View Related
Place The Name Before Equal Sign
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort?

Private Sub ConditionalRowDelete()
Dim NumRows, iLine As Integer
ActiveSheet.UsedRange.Select
NumRows = ActiveSheet.UsedRange.Rows.Count
Dim CircuitType As Range
Set CircuitType = Range("C" & iLine).Value
For iLine = NumRows To 2 Step -1
If Range("A" & iLine).Value > "6999" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
For iLine = NumRows To 2 Step -1
If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
End Sub

View Replies!   View Related
If Statement (equal To And Greater Than)
If A7 is equal to and greater than 95% than B7 is equal to "A". If A7 is equal to 91% but less than 94.99% than B7 is equal to "B"...

View Replies!   View Related
To Have Cell Equal To Autofilter
I have 2 worksheets, and I was wondering if I can have a cell from Sheet2 equal to the subtotal of the Autofilter criteria of a column in Sheet1.

I do have something working with macros but it takes a long time. This would make things a lot easier.

View Replies!   View Related
IF Formula Based Around A Value It Does Not Equal
Is it possible to do an IF formula based around a value it does not equal?

eg I have one column, that is perforning a SUMIF providing that a certain cell is equal to a particular constant.

=IF([cell]=[value],SUMIF([range],[criteria],[sum_range]))

In the next column I want to do it the other way round so that:

=IF([cell] DOESNOT=[value],SUMIF([range],[criteria],[sum_range]))

Is it just a simple symbol??

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved