I am successfully using VBA to check a cell on one sheet against a cell on another sheet.
How easy would it be to check if this is the same OR less than 2 or more than 3? I have done part of this using nested formulas but cant find anything to help me with the syntax in VBA
The code I am using is
If W1.Cells(a, 3).Value = W2.Cells(b, 3).Value Then
I am trying to apply conditional formatting to multiple columns based on a percentage varience of the value in the first cell of each column. I'm sure this is not a new concept however I have been unable to find any advice when searching. I have attached an example of my problem as a picture speaks a thousand lines of code.
View 8 Replies View RelatedI have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I use this formula to calculate if a move is out of tolerance =IF(ABS(B1)>=1,"Y","N") This will work on most of the stuff we load. I would like to modify it to if b1 = "Crude" then use =IF(ABS(B1)>=.5,"Y","N"), or if B1 = "Resid" then use IF(ABS(B1)>=2.0,"Y","N"). Is it possible to include the 3 tolerance bands in one formula?
View 9 Replies View RelatedI have two numerical columns of different length. One presents the signal and another one the background. Most of the background numbers are found in the signal column but they are not exactly the same and not located in the same rows. The numerical values are the same within some tolerance that I can specify. What I am trying to do it to remove all the background values from the signal column.
View 8 Replies View RelatedI have a large amount of data pertaining to many different runs in which I am given the mass to charge ratio of a compound. My goal is to identify these compounds, and to ease this process I am trying to find a formula that would “line up” near identical masses while providing a blank if no mass was found in that run. For example, if I have:
Run1
185.041
196.073
200.454
667.175
Run2
185.040
196.074
398.452
667.175
Run 3…etc
I would like to have it arranged:
Run1Run2
185.41185.040
196.73169.074
200.4540.00000
0.00000398.452
667.175667.175
It would be important to have some sort of tolerance setting, as the readings are not always exactly the same. Also, I’d normally have about six runs of data to line up.
I am trying to replicate a method , it reads: 'any data point that changes by more than or less than 10% from the previous value, and does not remain at the new level for more than 6 days, should be removed.'
Thats a bit vague. The process is going to involve if statements, etc. I've attached an excel spreadsheet with some solutions I've tried. Neither of which work. The aim is to get the same answers as the highlighted column. Columns B and C replicate the method answer, but do not state the process. The letters L represent values less than 25. Its the letters D I'm trying to eradicate.
Below is a short segment of my excel spreadsheet:
A B
1020.00242-89.84
1020 -88.11
1021 -85.3
1021.494-80.41
1021.49 -86.98
1030.04 -89.4
1030.042 -88.26
1030.94 -79.98
1030.933-81.5
1030.961-85.87
1040.0418-88.77
1040.391-87.3
1040.291-82.94
1040.016-84.12
1049.82 -84.7
What I need to do is write a macro that will find duplicates in Column A, within a changeable tolerance, say 0.1 (10%).
After finding all duplicates within a tolerance in A, I need to make another "Master" worksheet with the Duplicates from A, and their counterpart in B. So if A1 and A4 where within 10% of each other, the "Master" worksheet would contain:
A1 B1
A4 B4
using the values, giving:
1020.00242-89.84
1021.494-80.41
I tried using SUMPRODUCT and some other functions but just can't seem to put my finger on this one. I'm sure it's not hard and am overlooking something.
I'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
I need to be able to get a true/false from a tolerance percent.
Here is an example of what I am trying to do
cell a2 is Nitrogen
cell b2 is (Known gas%) 2.4800%
cell c2 is (unknown gas%) 2.4963%
cell d2 is =b2-c2 and I get the answer no trouble there. what I need is to take the answer in cell d2 and set a plus/minus 2% tolerance in cell f2 and get a true/false comparison.
a lookup and match of two values.
For example, in Sheet1 I have a unique transaction reference number and a £ value.
eg. 60231568 £38.05
In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...
60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q
The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.
I have an unknown number of rows that represent parts and 6 columns that represent part dimensions. I need to find and output matching pairs of parts that are within certain dimensional tolerance ranges.
For example:
Dimension 1 Dimension 2 to Dimension 6
Part 1
Part 2
to Part X
-I would need the code to check if Dimension 1 of Parts 1 & 2 were within a certain range and if Dimension 2 of Parts 1 & 2 were within a range etc. then (if all of them were true) output them as a pair. Otherwise, it would continue to search for paired parts. Once a pair is found, those parts can't be used again. How can I do this?
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
View 6 Replies View RelatedIt's a worksheet for testing milk tanks. The issue is as follows:
Column F = Chart Error/This can be either a positive or negative whole number
Column G = Tolerance/This is always a whole positive number
Problem is I want Column H = Status to say "Pass" or "Fail" based on meeting the Column G number. But it's only the number itself (not whether it's Positive or Negative) that matters in determining the P/F status.
Examples:
Column F= -20
Column G= 15
Column H= Fails
or
F= 20
G= 15
H= Fails
The formulas I've been coming up with will show one or the other as Failing but not both + or - numbers if they fail to meet the G Column tolerance number.
******** ******************** ************************************************************************>Microsoft Excel - test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH2I2J2K2L2H3I3J3K3L3=
HIJKLMNOPQRSTUVWXYZ1Cat*ID*#ELECTMTC*HoursInv.*Material*$Mutiplier096450965509656097510975209757098891023610673106751067610842108772108429655#N/A1.5813310317206365573396459655#N/A1402310317206365573Buchanan Survey*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is a partial of the sheet that I am working on.
The current Formula in Column L is manually entered for each line. What I would like to know is if there is a way to have a macro that would create this formula?
What we would need it to do is check out the Cat ID #'s in cells H - J and based upon the numbers in there, look up the $ amount (right now they are in columns N-Z). The amount in column M would be manually input and then the formula in column L would be
the value in M multiplied by the sum of the values that correspond with the ID #'s in H-J plus 10.
I have a range of cells which contains one of two text values (Ok and Not Ok). I would like to check my range of cells for these values. If this range have one cell that contains the text "Not Ok" I would like the formula to say "Not OK". If all cells contain "Ok" I would like the formula to say "Ok".
View 8 Replies View RelatedI have multiple colums that I want to compare (A=F), (B=G)........
I want to see the difference of the two as shown in Blue column (example file is attached.)
Skærmbillede 2012-12-13 kl. 18.37.15.jpg
I have a number of worksheets, each sheet is set up like a data entry form, at the bottom of which is a command button that takes them to the next worksheet based on a value in a cell. what i want to do is before it takes them to the next worksheet it checks a selection of cells, if any of which are blank it shows an error message and wont allow them to continue, better still the cell that is blank it high lights with a red border.
View 2 Replies View RelatedI am using the followng code to check data is entered in a cell ...
View 6 Replies View RelatedI would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.
Function FirstCheck(Count1, Count2)
Select Case FirstCheck
Case Count1 = "", Count2 = ""
FirstCheck = ""
Case Else
FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2))
End Select
End Function
I am trying to get a statement to check 7 different cells to see if they contact the same value, i go into a little depth
if a person is late to work then, a text value of 'LT' for late is placed in the cell, if they are late the next day then another LT will be used and so on.
from this then how many mins they are late is loaded into the next cell so
i have so far (in english) =IF Cell Mon,Cell Tue,Cell Weds, Cell Thurs, Cell Fri, Cell Sat, Cell Sun = 'LT' then SUM all mins late
but excel is saying there are to many arguements, any one any ideas can provide sample spreadsheet if needed
I have the following code that i want to use to run trough a few checkboxes to check the value and then wright something in a cell. i have written the the checkbox name to a variable and then want to use it in the if command but gives me a type mismach.
Code:
Dim CheckBox As String
For i = 1 To 204
For c = 3 To 227
CheckBox = "Sheet3.CheckBox" & i & ".Value"
If CheckBox = True Then
Range("J" & c) = "Done"
Else
Range("J" & c) = ""
End If
Next
Next
I want to do a check with one cell that has a list of a maximum of 4 numbers seperated by a comma and space, with 4 cells that have one of these numbers in each of them. The numbers wont be in the same order, and they wont be repeated.
Example
A3 = 4, 12, 19, 34
A4 = 12
A5 = 19
A6 = 4
A7 = 34
I only need to highlight if they dont match. So result could either be a tick or cell changes colour.
how to check if cells contain only whole numbers (cells are in text format)?
I do not know how the code is for the activecell checking
Example Range("A1:A10")
ColumnA
12345
12345
555,10
12345
1.20
A6666
12345
12345
12345
As soon the code hits a cell containing something other then numbers then Msgbox "Error"
Sub Check()
i = 1
Do Until Cells(i, 1).Text = ""
Cells(i, 1).Select
If Not Activecell.Text only numbers then
MsgBox "Error"
End If
i = i + 1
Loop
End Sub
Is there a way to create cells to be like a check box. I am creating a survey in which there are 5 options and what I would like to do is make the cells so that that when they click into the cell it puts an X in it or maybe highlights that cell.
View 9 Replies View RelatedI'm trying to scan a column of data, example column H, for empty cells, if it's empty then skip it. This doesn't work, because cell.Value = " ":
If cell.Value 0 Then
'Extra coding
Else
End If
Sub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ErrHan:
Exit Sub
End Sub
I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.
In a column "V", when the user enter "Y", i need to copy the entire row to another sheet("Written-off"), its working fine.Sometimes the first 2 columns of that entire row where the user enter "Y" may be null, so using my code, copy first entire row where user put "Y" and paste to another sheet("Written-off"), then user also enter "Y" to wherever in "V" column, this time the entire row should overwrite the last row in the "Written-off" sheet.
I found the reason is first column of the last row was null, so when check the first cell, if it is empty then the next entire row should be copied that area.
Is there anyway to check first 2 cells of the last row in a sheet in null or not?
If Right(Left(ActiveCell.Address, 2), 1) = "V" Then
myColumn = "V"
If Intersect(Target, Columns(myColumn)) Is Nothing Then Exit Sub
On Error GoTo last
If UCase(Target.Value) = "Y" Then
Target.EntireRow.Copy Sheets("Written-Off").Range("A" & Rows.Count).End(xlUp)(2)
Application.CutCopyMode = False
Sheets(Sh.Name).Select
Else
Exit Sub
End If
I have two cells which refer to employee number: A1 and A2
(employee numbers are 001, 002, 003, etc)
I want Cell A3 to check if either A1 or A2 = 001
If both cells is 001, I want an "error message" because that is not allowed, and if one of them = 001 then I want the value from Cell A4 to be but in Cell A3.
So in my head the forumla should go like:
=If(A1 Or A2 = 001;A4;"-";)
I have completed a formulation and included in a macro but I dont know about coding to check for specific cell string values before macro will take off. I want macro to check that if specific cell text are not matched, use MsgBox to display the wrong versus correct cell string text, then vbOK to exit macro. Also, if Range(B2:G2,J2) are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.
cell B =REQ
cell C =SS
cell D =Current Stock
cell E =PO
cell F =Sales Order
cell G =In-coming
cell L =unit cost
If these cell texts (U or L case will do) are in their specific cells, macro will proceed. If either one or some of these cell texts are not matched, display MsgBox of the wrong and correct cell texts. Then button OK to stop macro run. If Range(B2:G2,J2) are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.
