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


Validate Range Against Numerous Conditions

I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.

I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")

I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".

Can the function be used in this way or could someone suggest another way of handling this?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Numerous If Conditions? GPA Conversion
I am trying to create a spreadsheet that will take all my grades from the semester (I'm a college student) and compute my GPA. I managed to get a weighted average for each class, but the grade is on a scale of 1-100. I need to convert that to a letter grade, and then a 4.0 scale. How can I set it up so that it will return "A" if 90<Grade<100, "B" if 80<Grade<90, etc.? I'm apologize if this is something simple, but I've been trying to find the right function for a long time.

View Replies!   View Related
Validate For Numeric Range & Alphanumeric Range
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 Replies!   View Related
Validate Cell Value Against Range Or Array
I would like to use VBA to validate the data in an Excel worksheet against either a named range or an array of valid data.

The valid data comes from an accounting system. It is a list of customer numbers. I am using ODBC and SQL to get the data into an array and then into a named range. This is working fine.

I want to validate the customer number column in my source Excel worksheet against either the array or the named range.

There are a variable number of lines in the source data.

I have a VBA procedure which starts at cell A2 and then works down column A until it reaches a blank cell.

I would like to use a VBA formulae which does something like:

IF active cell offset (0,3).Value in (named range or array)
Then set cell colour for active cell offset (0,3) = Green
Else set cell colour for active cell offset (0,3) = Red
End If

Or something like that.

Can this be done using VBA? Or do I need to create a new column in my source data and use VBA to place a VLOOKUP based on a named range into this new column?

View Replies!   View Related
Vba Userform Validate That Number Is Not In Range In Sheet
I am creating a userform, and in this form I have two text boxes and four radio buttons. For one of the text boxes, the user is to insert a number. I have to make sure this number is not already in a range in the workseet. How can I make the userform not accept numbers in a text box that are already in the range in the worksheet?

View Replies!   View Related
Validate Cells For Numeric Range Of Consecutive Numbers
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100"


The above is a valid list. Below would be an invalid list:


This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)

View Replies!   View Related
Validate Cell Entries To Named Range List
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.EnableEvents = True
End With
End If
End If
End Sub

View Replies!   View Related
Numerous Number Comparisons
I am having a little difficulty getting my vba to correspond to what i want it to do. I have columns that can vary with how many rows they have weekly. The all start at P2, R2, V2.

These columns always have the same number of rows as each other, but the number of rows varies.

I compare my R2>P2, and if it is return the number in R2. If not, i am looking to take P2 and round down to the nearest .95. (ex: R2 is at 3.45. The value that should be put in in V2 is 2.95. Since the values all change as well, it needs to compare: .96-1.95, 1.96-2.95, etc. all the way up to 15,000.

I know it can be done with if's, whiles, or from i to x, but my experience is in, not vba. And, since i am new at this, i can write the code, but don't know how to make it correspond to a new excel filename weekly.

View Replies!   View Related
Referencing Numerous Cells/columns
Let's see who's got the logic.

What I have:

Column C containing either 1 or 0.5 or ""

Column E starting at E9 which may contain "deal" or a few other things

Column I starting at I9 which may contain "here", "away", or "no"

Cell J4 which will contain a number........

View Replies!   View Related
Organize Numerous Numbers By Fractions
I am currently trying to organize numerous numbers by fractions, and it just will not set up right.

1 1/2
2 X 3/4
1 X 1/2
1 1/2 X 3/4

What would be the best code used, or method, to ensure that these numbers organize correctly?

View Replies!   View Related
Pivot Table Output To Numerous Worksheets
I believe in the past that I have told Excel to send the output of the pivot table to different worksheets. For example if I have three regions- the pivot table would send the output to three worksheets and name the worksheets with the three regions.

View Replies!   View Related
Mail Merge Of Information In One Cell To Numerous Columns
I have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.

View Replies!   View Related
Sending Lotus NOTS Email - Numerous Times
I am trying to send 7 different emails in this sub, the sendnotes sub is not recognizing my GOLOOP value

As you can see I stole this code from this site. You all are terrific!

Sub SendFiles()
Filename = ActiveCell.Value

GoLoop = "1"

GoLoop = "2"

GoLoop = "3"

GoLoop = "4"

View Replies!   View Related
Move Numerous Data To Any Favorite Place In Other Sheet Quickly ?
Let`s assume that I have numerous "Serial Number" in "Sheet1" :

Now I want to create some cards in "Sheet2" that each card have a serial number that related serial number there is in "Sheet1" : Need easy solution for doing it while when I drag one card to copying it then related serial number in sheet1 would be appear in required field. I mean first card has first serial number of sheet1 and second card has second serial number of sheet 1 ; but I want to doing it quickly with dragging.

View Replies!   View Related
Counting One Range With Conditions In Another Range
I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.

View Replies!   View Related
Delete Range With Conditions
I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.

I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.

Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.

View Replies!   View Related
Counting Within A Range With Conditions
I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.

I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.

View Replies!   View Related
Average Range By Conditions
I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:

Function m_avg(mRange As Range)

m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"

End Function

the above doesnt work and gives me #VALUE!.

View Replies!   View Related
Internal Range Conditions Formula
Please take a look at this sample spreadsheet.


I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.

View Replies!   View Related
Copy Range Based On Certain Conditions
I have this macro to copy a range to another worksheet:

Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y
' las cajas ATR
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))
Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True
End Sub

The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.

View Replies!   View Related
Sum The Range Of Data If Two Conditions Are Satisfied
My data is as follows

June 8£3.20 Loc1
June 8£3.20 Loc1
June 8£3.20 Loc2
July 8£3.20 Loc2
July 8£3.20 Loc1
July 38£15.20Loc1
July 4£1.60 Loc2
Aug 38£15.20Loc2
Aug 4£1.60 Loc1
Aug 8£3.20 Loc1

Here is what I want to do

- Avoide filtering and manually calculating data for the total expense in the month

I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for

I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???

View Replies!   View Related
Finding Row Number Within Range With Conditions
In the screen shot I'm trying to find the row number where a particular price of an order has been reached. In this case, for the first order, my execution price is 1.8859, my stop loss is 1.8834 and take profit is 1.8884. I need to look and the future prices to determine which event had occured first (either the take profit or the stop loss). I though by using row numbers I would compare and which ever is smallest would mean that it occured first - the profit/loss is then calculated.

The other caveat is that an exact match may not always be available - for example, the second trade is stoped out because the highest price for the 12:35 timeframe exceeds the value I'm looking for. Still it would have triggered a stop loss.

******** ******************** ************************************************************************>Microsoft Excel - Misc.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6I6J6M6H7I7J7M7H8I8J8M8I9J9M9I10J10M10I11J11M11I12J12M12=

View Replies!   View Related
How To Add Range From Multiple Sheets With Conditions
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter.


gives me a #NAME? error.

Example Data on each spreadsheet:

SU 004*02
SU 004*04
SU 004*04
SU 004*02
SU 004*04

View Replies!   View Related
Nested If Conditions From Multiple Range (sheets)
I want to auto fill certain columns in sheet A from sheet B & sheet C based on conditions

For Eg:

Sheet A:


Position Name

Personnel Name

Personnel No.




I want to auto fill in Sheet A for the columns:

Personnel Name
Personnel No.


If the employee is active in Sheet B then fill in Sheet A to the related position No.


If the employee is mapped to org. (Yes) in Sheet C then fill in Sheet A to the related position No.

i have written the below formula but not working properly:


View Replies!   View Related
Return Range Address Across Different Columns Based On Conditions
I am trying to return a cell address range based on values in different columns. Attached is an example.

In Column A1 I have "Range" and below that a set of numbers from 1 to 31.

In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.

What I want to do is as follows:-

I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.

I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.

View Replies!   View Related
Fill Range With Data Meeting Conditions Based On Cell Value
refer to attached worksheet.

I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.

View Replies!   View Related
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

View Replies!   View Related
Fill Multi Columned ListBox Based On Range Meeting 2 Conditions
I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View Replies!   View Related
Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

View Replies!   View Related
Formatting Text- Workbook With Numerous Worksheets That Contain Text
I have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?

View Replies!   View Related
Validate A URL
We have an internal web site that has files I need to download daily. The filenames have date strings in them. I've setup some formulas to make the url based on the NEXT dated file I need to download.

And I don't have direct access to the drive the files are stored on, I can only get them through this web site.

Right now, I have individual macros for each file I need. They'll follow the url and download the file if it's there, or return a message to me if it's not. But there are several different files. I have to run each macro one at a time, at different intervals during the day until they get downloaded.

Is it possible to make a macro loop through all the URLs (I have them stored on a sheet, called "FileDownloader" in Range G2:G10) and check if the URL's are valid (without actually attempting to download the file). I can then make some kind of dashboard to tell me when the files are ready for download.

View Replies!   View Related
Validate All Cells Are The Same
How can I validate that all values in a range of cell are the same, excluding
empty cells?

View Replies!   View Related
How To Validate A Formulae
I have a database on one sheet and a 2 count if formulae recording information on the next to be exact one formulae counts the number of monthly values and the other count yearly values. I want the sum of these formulaes to be equal or less than 25. and to show an error if the sum of these is mor than 25.

View Replies!   View Related
Validate The Data
I have a spreadshhet which has data by month, year, week and bi-weekly. I would like everything to be monthly. How can I put a formula which will look up the cell and see if it monthly it will the value of the cell beside it, if it is weekly it will take the cell value and multiply by 2 and so on.

View Replies!   View Related
Validate Empty Cell
The code below automatically displays the validation when I select the cell. But I would like the cell validation only display when the cell is empty. If the cell has data in it, then do nothing, but if the cell is empty, then display the validation.

If Not (Nothing Is Application.Intersect(Target, Range("B9"))) Then
SendKeys "%{down}"
End If

View Replies!   View Related
Validate List - Restriction
I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.

Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer
Dim intLastRow As Integer, intLastCol As Integer
Dim strAdEvent As String, strAdEventNm As String
Dim strAllAdEvent As String

Do Until intRow > intLastRow
strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value
strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value
If intRow = 2 Then..........................

View Replies!   View Related
Validate InputBox Entry
validation up to the point that the user can click cancel and exit, also when nothing is entered a msgbox appears and for the 3rd inputbox a value greater than 0 must be entered.

Sub Trajectory() 'Trajectory macro
t0 = InputBox("Enter a value for the initial time(t0)")
'If t0 = "" Then MsgBox ("You must enter a value for t0!")
'Exit Sub
tf = InputBox("Enter a value for the final time(tf)")
'If tf = "" Then MsgBox ("You must enter a value for tf!")
'Exit Sub
Dt = InputBox("Enter a value for the time increment(Dt)")
'If Dt = "" Then MsgBox ("You must enter a value for Dt!")
'Exit Sub
'If Dt = 0 Then MsgBox ("You must have a valid increment")
x0 = Val( Range("F4"))
v0 = Val(Range("F5"))
g = Val(Range("F6"))
y0 = Val(Range("F7"))
q0 = Val(Range(" F8"))
Selection.Formula = FILL_TABLE
End Sub

View Replies!   View Related
Validate Workbook When Open
I have a button that opens a workbook. What i need is if that workbook is already open then continue with the code. I know how to do this workbook.activate and error messages but I want to avoid activating the workbook. I was hoping there was a way to use something like on message resume next like when using error messages.

View Replies!   View Related
Code To Validate String Only
Data validation failed me here, so I resorted to VB to esnure only letters are entered in a certain cell. The code below is a mess and I need a hand to repair it. I can't figure out how to declare i . .

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a59")) Is Nothing Then
If Target.Count > 1 Then Exit Sub

View Replies!   View Related
Validate Cell Values?
I would like to simply validate the value entered to a cell against cells of a number of columns. I’d like it to return a vlue to be able to report it in another cell as below. (had to cmma separate the columns, can't get them aligned in this editor)

A, B, C, D, Q, R
1, b, 1, 2, 1, existing


A, B, C, D, Q, R
1, b, 1, 2, 5, not existing

So the user enters a value in cell Q to check against the other columns and return a value to R. Additionally, the cells to be validated (A - D) may contain more than one character, i.e. cell A could = 1b2.

View Replies!   View Related
Validate Cells To Only Accept
I have to validate a cell to only accept the letter a, d ,f ,g h,. I know this is probaly very simple but i havent touched excel since last november and have completely forgotton the most simple of things!

View Replies!   View Related
Validate A Value Entered Through The Below Prompt
What is the best option to validate a value entered through the below prompt?

View Replies!   View Related
Validate X Only Has All Text Or All Numbers, Not Both
I'm looking to make a code that validates that a string is only alphabetic characters.

In addition, i want an additional code that validates other strings are only numeric. I tried using the VBA formula isnumeric, but it seems strings with e and d are still acceptable. I would guess the e is for exponential, but not sure about the d.

View Replies!   View Related
Duplicate Entries, Validate Only One
In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.

But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.

I'm using this, to pick up the data
(A2:A10) Criteria
(O2:O10) value picked up

on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)

How can I count only one of the values picked up from sheet B?

View Replies!   View Related
Textbox - Validate For Numbers Only
I have a VBA user form that asks for user's input. The form has 3 textbox. I would like to check for the entry & make sure that it's numeric & not null. If it's not numeric or null, I'd like to display a warning message & highlight the textbox & ask for entry again.

Here's what I have but it's not really working. The warning message will come up but the next textbox is highlighted:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsNumeric(Me.TextBox1.Value) Then
MsgBox "Please Enter Only Numeric Values"
End If

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsNumeric(Me.TextBox2.Value) Then
MsgBox "Please Enter Only Numeric Values"
End If

View Replies!   View Related
Validate Textbox For Date
I have a userform with 5 textboxes. Each textbox looks for certain kinds of user code has trouble in re-locating the cursor to the SAME textbox after rejecting the user entry. And BTW, ideally the text box would be highlited in this instance.

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim okstop As Boolean
Dim yesno_continue As Boolean
Dim mytext As String
okstop = False
TextBox2.SelStart = 0
mytext = TextBox2.Value
If Not IsDate(mytext) And mytext <> "" Then
TextBox2.Value = ""
yesno_continue = MsgBox("Please enter a date...try again?", vbYesNo)
okstop = True
End If
Loop Until (yesno_continue = vbNo) Or (okstop = True)
End Sub

View Replies!   View Related
Validate TextBox Before Moving To Next
I have a TextBox on a UserForm and I want to validate the user's input as soon as he moves focus from the TextBox but before he selects OK. I have the validation function, but what is the event

View Replies!   View Related
Validate User Input
If anything else is entered into the input box a message box (MSGbox) with a vbCritical button will appear with the following prompt "You entry (insert entry data here) is inccorect." I don't know the code for "insert entry data here".

View Replies!   View Related
Copying .xls File Numerous Times And Rename Each File From .xls Spread Sheet
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.

I got this script from an site and tried it. It runs but I don't see any spread sheets.

strComputer = "."
Set objWMIService = GetObject ("winmgmts:\" & strComputer & "

View Replies!   View Related
[Tough]VLOOKUP/Other To Validate Then Grab Value!
I have created an image of what i want and hopefully this will explain better.

This is very hard but it is something really important for the company i work for and have an idea on how its done but not enough knowledge to implement yet.

View Replies!   View Related
Validate Email Address Entry
How it is possible to control the entry for the E-mail address?
I mean if there is no '@' or '.' and more that I dont know, maybe you know better and faced to such problem

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