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


How To Check If Range Contains Certain Value

Is there a way to test if let's say range a1:a10 contains a value of cell b1.
I would be thankful to get the VBA code to do that

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Check A Numbers In A Range To See If Any Of Them Falls Into A Particular Range
make this formula more concise and shorter, it was design to check a numbers in a range to see if any of them falls into a particular range.

=IF(SUM((COUNTIF(Fund,">11999")-(COUNTIF(Fund,">12999"))),(COUNTIF(Fund,">21099")-(COUNTIF(Fund,">28729"))),(COUNTIF(Fund,">28730")-(COUNTIF(Fund,">33999"))),(COUNTIF(Fund,">58999")-(COUNTIF(Fund,">59999"))),(COUNTIF(Fund,">82000")-(COUNTIF(Fund,">84999"))),(COUNTIF(FUND1,">11999")-(COUNTIF(FUND1,">12999"))),(COUNTIF(FUND1,">21099")-(COUNTIF(FUND1,">28729"))),(COUNTIF(FUND1,">28730")-(COUNTIF(FUND1,">33999"))),(COUNTIF(FUND1,">58999")-(COUNTIF(FUND1,">59999"))),(COUNTIF(FUND1,">82000")-(COUNTIF(FUND1,">84999"))))>0,"ATTACHMENT E IS REQUIRED","")

and if the any of the number fall into the range it will print the message, "Attachment is Required"

View Replies!   View Related
Range Check..?
Is there a formula that I can use to check that all the depths are in a range of 1 to 5. If one depth is say 6 then this must be flagged up

View Replies!   View Related
Check For Time Range
I have attached a sample for better understanding. First of all, I need to check for the highest value in Col B and lowest value in Col C in the range from Time 0900 to 1100 only. I need to use the check time function (which i have no ideal how) rather than selecting the number of rows to check.

View Replies!   View Related
VBA To Check Named Range On Each Tab
I have a spreadsheet in which I have named columns. All columns have associated columns in different tabs. Using a toggle button I want to hide or unhide these associated columns on all tabs at the same time. Here is what I have so far:

Private Sub ToggleButton1_Click()
Application.ScreenUpdating = False
Dim ws As Worksheet
With ToggleButton1
If ToggleButton1.Value = True Then

My problem is I want to have a single string to hide/unhide the following Named columns on all sheets -- (Sheet 1 (COLU), Sheet 2 (DRCOLU), Sheet 3 (F1CCOLC), Sheet 4 (MSCOLC), Sheet 5 (PSCOLC) and Sheet 6 (POCOLC)

I was hoping to use something like -- Range("COLC, DRCOLU, F1CCOLC, MSCOLC, PSCOLC, POCOLC").EntireColumn.Hidden = True

I receive an error when I try to use it because only one of the names is used for each sheet. It needs to first verify the name exists on the sheet and if so, perform the Hide/Unhide function and if not move to the next name. When all names have been used, move to the next worksheet and perform the same action until all worksheet have been gone through.

View Replies!   View Related
Check If A Value Is A Named Range
I need some code that will check the valueof a combobox to see if it is a named range. i'm not sure where to start really? i know i probably need to use "ListNames" in there somewhere?

View Replies!   View Related
How To Check If Range Exists
Hi all, this might sound really easy but I have a Named Range in excel 2003.

Now I'd like to write a VBA code to check if this range exists before carrying on futher computations.

View Replies!   View Related
Check For Presence Of Value In Range
If I have a named range ("colors") which contains a bunch of different colors, and in a separate cell, I want to test whether a particular color is present in that range, how would I do that? For example:


=if([range contains "yellow"],1,0)

this formula would return 0.

=if([range contains "green"],1,0)

this formula would return 1.

I'm having trouble with the part in []'s.

View Replies!   View Related
Check Range For Any Data
If my cell range in Sheet 2, K2 to K25 is empty/blank, then I want to fill that blank range with the same number/Value. It can be a 0 or a 1 or any other Value as required. But an Error 13. Type Mismatch message appears. Where is the error if I'm trying to fill the range with a Value if blank ?

Sub Macro_2()
If Sheets("Sheet2").Range("K2:K25").Value = "" Then
Sheets("Sheet2").Range("K2:K25").Value = "1"
End If
End Sub

View Replies!   View Related
Formula To Check For A Value In A Range
is there a way to check for a value in a range, and return the value if it is not in the range, else return nothing?

H1 =85

B | C | D
25 | 50 | 75
40 | 30 | 10
12 | 9 | 66

None of the following IF statements will work:


View Replies!   View Related
Check Range; If Equal To X, Y, Or Z Then Keep Else Delete
I am trying to search a column for specific values.... if the cells in the column equal X, Y, or Z then I want to keep them. Anything else, should be deleted....and not just the cell the entire row that it is in.

So I'd like it to search column E for a number of different values, if equal keep the data. If not (say E6 is not any of those values) then delete that row (all of row 6)

View Replies!   View Related
How Can I Check A 4 Column Range For A Particular Number
I would like to check a 4 column range to see if a particular number has been added to a range of number.

this is the formula that I wrote but it does not test beyond the 2nd column.


Does anyone have an different way of check for this?

View Replies!   View Related
Check If All Cells In Range Are Empty
I have an if statement as follows:

If IsEmpty(Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)))


i did a select to make sure it was selecting the whole range I want and it works fine:

Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)).Select
Inside my range I can have cells with 0s in them and cells with nothing in them. What I would like my if statement to do is return true ONLY when ALL cells have nothing in them. At the moment, even if I have 0's in some cells, it's returning false.

View Replies!   View Related
Check For Valid Named Range
I have this formula =COUNTIF(WallA,D35) which works great unless the named range is deleted. Is there a way to check to see if the named range is valid in formula?

View Replies!   View Related
Check If Any Cells In Range Are Numeric
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 Replies!   View Related
Check Range Of Cells For Data
I am using the following code to check data is entered in certain cells...

View Replies!   View Related
Check A Range Of Cells For Empties
Upon deactivation of a worksheet I am trying to check a range of cells("B8:M8") for empties. If any cells are empty I want to generate a MsgBox that asks the user to remedy the situation. It is not necessary to tell them which cell is empty but it would be nice. Can anyone help me format this code?

View Replies!   View Related
Check For Blank Cells In Range
I have X amount of cells (formulasheet) , these are used to input data, which is then summarized into a sheet on the same file. I'm trying to find a code that forces the user to fill out all the fields. If the user do not, the "submit button" will not proceed, at least not work. Almost like a forum registrating, where it is required to fill it all some mandatory fields in order to proceed.

The cells datatypes are mixed, some are Integer and some are String.

Is there anyway that are code can be written "if the cell(s) is FALSE (nothing in the cell) then display message.

View Replies!   View Related
Check For TextBox Value Match In Range
I am trying to have a macro to take a value from a text box and see if it is matches a value that is in a specified range. If it comes back true then it shows an error message. This code is working for text values, but if it is numeric it will not find find it.

Sub SearchForMatch()
'Procedure level declarations
Dim rngCell As Range
Dim bMatch As Boolean
' Loop through each cell in the nominated range
For Each rngCell In Range("B4:B50")
'If there us a matching value then set your
'boolean flag to true and exit the loop
If rngCell.Value = TextBox1.Value Then
bMatch = True
Exit For
End If

'If a match was found then alert the user
If bMatch Then MsgBox ("Name already exsists.")
End Sub

View Replies!   View Related
Check Range & Show Blank Instead Of 0
See attached spreadsheet for example of the sheet I am using.

Problem 1.

Columns C and D show me figures when the number in col A is > 2500 or < 550 when these criteria are not met a 0 is displayed in the cell, is it possible to alter my formula so that nothing (a blank cell) is displayed rather than a 0 - I am wondering if there is something like null which I can use?


Problem 2.

In column E (normal days) I want to display the value of A if it is > 550 but < 2500 - is there a way of doing this in a single formula? Again I would just like a blank to be displayed if the value of A does not meet the above criteria.

View Replies!   View Related
Constantly Check Range For Values
At the moment I have 2 columns, A1:A5 and B1:B5. Normally A1:A5 and B1:B5 are all 0's. Every 5 minutes numbers will show up in column B and I do a procedure outside excel(feedback of DDE's) and they all go back to 0. Now sometimes the cells in A1:A5 are not all 0 after the numbers show up in B1:B5. I can reset A1:A5 to 0 with a macro button. I already built that macro, lets call that macro 'Mike'. So now I have to press that macro button every time when B1:B5 are showing zero's after the procedure and A1:A5 are not showing 0's. Is there a way a macro can constantly check if A1:A5 is non zero and B1:B5 is zero that the macro Mike is called?

View Replies!   View Related
Check Instances Of A Range Of Values In A Column
I have a column that I want to check if a column contains any instances of particular values. If any are present I want it to return a 1, if not then return a zero.

View Replies!   View Related
VBA Macro Code To Check Spelling In Range
Is there any way to use formula or VBA to highlight cells which contain the correct spelling?

For example i have columns A filled with words in each cell but need to highlight which ones have the correct spelling, as there are more with incorrect so i need to visually see the correct spelling.

View Replies!   View Related
Check If Value Exists In Range For Worksheet Filtering
I'm looking to use the value from a series of dropdowns (made via data validation lists).

Cell AM5 is a dropdown of named ranges made form a seperate sheet
Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.

I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.

View Replies!   View Related
Countif? Check If Several Variable Appeared In The Range
I have attached the jpeg on my question. (attached the excel file). basically, on my selected range, it has number from 0 to 6 in the five columns. how can i set the formula (countif? or which other better formula) to detect if 4,1 and 1 exist in the range?

View Replies!   View Related
Check Non Contiguous Range To See If All Cells Are Filled
Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?

View Replies!   View Related
Check For Empty Cells In Non Contiguous Range
The application reads in a file, whcih can have various formats. To check which format it's in, I plan to look for certain empty fields/ cells. I can successfully detect a group of empty cells by explicitly testing each one, but when I put them all in a range and test that, the check fails. So far I've reduced the problem to the following example code.

Sub check_clear()
If IsEmpty(Range("a1")) Then
Range("g2") = "A1 empty"
End If
If IsEmpty(Range("b1")) Then
Range("g3") = "B1 empty"
End If
If IsEmpty(Range("c1")) Then
Range("g4") = "C1 empty"
End If
If IsEmpty(Range("d1")) Then
Range("g5") = "D1 empty"
End If..............

The result is that each individual cell check results in the relevant "XX empty" message. However, the test that the range of multiple cells is empty never produces a result. I'd really like to understand the underlying reason - as well as find out how to perform an isEmpty test on a range. I'm looking more for guidance and insight than a canned solution

View Replies!   View Related
Check If Named Range Exists Before Delete
I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried: If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete


If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete

Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.

View Replies!   View Related
Check For Letter In Range & Count Numbers
I need to compare for each trip whether there is a trip with Es and no Es in it and mark every line in column E with tag "Mixed Use". In the data below trip 2 has mixed use. I have attached a sample file.

Trip Employee EntertainmentMiles

Trip 1Employee 1E827
Employee 2E0
Employee 3E0
Employee 4E0
Trip 2Employee 1758
Employee 20
Employee 30
Employee 40
Employee 50
Employee 60
Employee 70
Employee 80
Employee 9E0
Employee 10E0

View Replies!   View Related
Macro To Check If Range Of Cells Values Are Balnk.
I am looking for a macro that will check 2 things....then do something.
The first is if a range of cell values in are blank. lets say

Next is if a particular cell contains a given value ,
Sheets("sheet1").Range("E5") should equal "Year 1" if true.

If both these are true then I want the macro to copy a range of cells from
Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").

I tried to use IsEmpty but it seems to return a "False" if a range of cells
are being tested.

View Replies!   View Related
Check For Values In Range & Compare 2 Cells On Different Worksheets
In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;

Where 0.05 selected it computes for cell C63:


then cell D63


and so on

Then if 0.01 selected it would compute for Cell C63


then cell D63


View Replies!   View Related
Copy Range On CheckBox Check & Clear On Uncheck
I have a check box that when checked needs to take information from multiple cells and copy into multiple cells and then when uncheck remove the data from the cells.

I have attached a sample.

View Replies!   View Related
Partial String Check In Cell Against Range For Return
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.

This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...

Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.

View Replies!   View Related
Code To Check For Text In A Range Prior To Email Sheet
I am using the code below tied to a button on the worksheet to email a sheet and would like it to check that there has been text entered intothe cells below prior to emailing the sheet. If the cells have no text entered i would like a message box to pop up stating that all fields need to be completed.

Range of cells:


Email code I am using:

View Replies!   View Related
Conditional Formatting Look At The Cell Contents And Check If The Value Exists In A Range
I am trying to set up a conditional formatting which will look at the cell contents and check if the value exists in a range.

The range to compare will be over multiple columns and multiple rows.

View Replies!   View Related
When Typing To A Cell Check If This Number Exist In A Range Of Cells (in Sheet 2)
I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my *** club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..

View Replies!   View Related
Macro To Force Format And Check Barcode Check Digit
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts

8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"

these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is

View Replies!   View Related
"if Any" Function Or Similar: Check A Range Of Cells (B4:B35) And See If Any Of The Contents Are Less Than A Specified Cell (M1) And Then Show A Message
I need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.

View Replies!   View Related
Formula To Check Column A For Date Range And Count Column B
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.
Here is what I have but it is counting the dates in col A not the order numbers in B?

View Replies!   View Related
Check Each Cell In Range For Value & Put Text In Offset Cell
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.

Sub RatingsFix1SP()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
For i = 0 To 3
For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 6) = 0.15
End If
Next rngCell
Next i
End Sub

To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.

View Replies!   View Related
Check All Check Boxes With CheckBox
I am creating a userform with 10 checkboxes. The first 9 checkboxes are user options. I want the 10th check box to be a "Select All" option i.e. if the 10th checkbox is checked all the other 9 options are deemed to have been selected.

The way I want the display to work is that if the 10th checkbox is ticked all other checkboxes are cleared. Also if the 10th checkbox is ticked and any of the other check boxes is selected then the 10th checkbox should be selected.

I have tried coding this up but the checkboxes don't seem to operate as desired. I placed some code on the click event for the last option button to set the vlaue for all other buttons to false. This works but the 10th check box doesn't get ticked itself. When I try to code up the other bit I get similar issues.

View Replies!   View Related
Userform Listbox: Check Wether Range Have Negative Values Or Not If Yes Load All Negative Values In The Listbox1 By Clicking Checkbox
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

View Replies!   View Related
Uncheck Check Box + Check A Option Button Using Command Button
When I press a command button I want to:

Uncheck a checkbox made with Control Toolbox

Check a particular Option Button within a group box made with the Forms Toolbar.

EDIT: Clarification, I want both things to happen when I click one button.

View Replies!   View Related
Check Boxes Macro: Macro That Will Check If The Boxes Are Checked And If Not Ignore Them And If So Run A Certain Macro
I have numerous hyperlinks in cells on a worksheet, next to these I have checkboxes. I am looking for a macro that will check if the boxes are checked and if not ignore them and if so run a certain macro.

View Replies!   View Related
Check If A1=A2, Then If A3=A4, Then If A5=A6
I have a sheet with 2329 rows. Row 1 & 2 should be the same number, then rows 3 & 4 should be the same, then 5 & 6, etc (see example below). This should result in an even number of rows, so either 2328 or 2330. So somewhere in this sheet, I either have a number repeated twice instead of just once, or a number that's not repeated at all. Is there an easy way to highlight where the error is without having to manually scan all 2329 cells?


View Replies!   View Related
Check Box Or To Not Check Box
I am trying to create a basic spreadsheet in MSExcel used to collect standard info (Name, address blah blah blah). The people who will complete this will need to classify the people they are listing into groups (Group A, Group B etc. 5 groups total) and each group will be listed on a separate tab of the workbook (maybe this isn't the best way?).

As they enter the people they will need to decide if the person they are adding is to receive the welcome kit. Each company is limited to 50 welcome kits. My thought is to use a check box for them to indicate yes or no on the welcome kit.

Is there a way to display the number of welcome kits that are remaining on whatever tab they happen to be on?

Is there a way to give a clear indicator next to each name that 'yes' has been selected (colored box, text?).

View Replies!   View Related
Check For A Value
Is there a VBA function using which i can check for the existence if a particular value on an Excel ?

View Replies!   View Related
Check Color And Use
I want to use "If" function to check "color" :

If (Color is yellow,sum(a1:B5),sum(d1:d8))

View Replies!   View Related
Check A Datetime Is In GMT Or BST
i have a column
"Received On" contains the datetime of a mail received , it is in BST, sometimes it should be in GMT depends on the date or year, i'm not sure.
So what my query is, in another column, i have to check if the datetime in "Received On' is in BST, convert this datetime to GMT, otherwise if this time is in GMT, i don't need to add anything just copy that datetime alone.

eg: ie in ColumnA is the Datetime value

if ColumnA=BST then
ColumnB=ColumnA+1/24 (ie convert BST to GMT)
else if ColumnA=GMT then
ColumnB=ColumnA (ie not need to change, becz already columnA is in GMT)

View Replies!   View Related
Check If ID Is New Then Sum Per Category
I have 4 columns:

id (A), number (B), category (C) and month (D)

I think this could be done using VBA or formula's,
I understand how to SUM up based on criteria, but I
haven't a clue where to start to see if something was
present/ not present in the previous month and category and vice versa

If I decide a month to look at in E1, say February.. then i need
to calculate 4 things:

1) was an id in February and not in January (for this category) if so then sum up (B) per category (C) for February

2) was an id in January and not in February (for this category) if so then sum up (B) per category (C) for January

3)was an id in February and not in January (not in any cateogry in jan) if so then sum up (B) per category (C) for February

4)was an id in January and not in February (not in any category in feb) if so then sum up (B) per category (C) for January

I need to do this for each category (C)

My data would be something like this (I cant download addon from this PC, i have it at home)


so if I chose to put February in E1,

I would need the results something like follows:

category__new to category__left category__n__left totally

View Replies!   View Related
Copyright 2005-08, All rights reserved