I am trying to create VBA function that will compare search a worksheet row by row for a value in column c. If column c matches any of the array values, i would like the cell value from column d to be entered in another array. I'm familiar with adding values to an array, but i'm having trouble with the code to compare the values. the array is a string and the cell values are text.
View 3 RepliesI am trying to compare the values in an array with the values in a range of cells. It worked fine until I moved the code into a sub. The specific problem that I am having is that is pulling cell values from "shippablegoodspriorday" and it is supposed to be pulling the cell values from "shippablegoods".
module1
Call compareSheets(priorShippable, Sheets("notfound"), Sheets("variance"), Sheets("shippablegoods"), [A:A], 11)
module2
Sub compareSheets(goods() As Variant, WS As Worksheet, WS2 As Worksheet, WS3 As Worksheet, col As Range, offset As Integer)
With WS3.............
How to compare to a string of objects
1. TH0102
2. KJ1403
3. KJ1404
4. TC0910
5. TC0905
Example :
if column data A2 (TC0905) match to any of the objects 1 to 5 , it will return me TRUE. if(A2=(1,2,3,4,5),TRUE)
I have 2 array variables in a block of code which I need to compare to check if the items in the arrays are exactly the same. The arrays are exactly the same size and I can see the contents of the arrays in the watch window.
When I try to run and IF statement as in: IF VARIABLE1 = VARIABLE2 THEN - I get a compile error saying "type mismatch" on the equal sign.
I have a set of old and new values that I need to compare.
We can assume that values in column X are the master values and values in Column Y are subordinate to column X
For example
Yesterday's values
x y
abc 1
def 2
ghi 3
Today's values
x y
abc 1
def 4
jkl 3
So first i need to find out that GHI is no longer available, and second that the amount for DEF has changed from 2 to 4. Hopefully this makes sense if not let me know and I can provide a sample spreadsheet or the like.
=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
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I have two worksheets.
Column A on both sheets have an ID number which is unique to clients. Sheet one will only have a client ID once but sheet two may have the same ID multiple times
I need to compare both sheets Column A and if it finds a match, then paste the cell value on column E sheet two, into column K on sheet 1.
sheet 1:
A ..........................................K
Client ID............................. name
Sheet 2:
A ...........................................E
Client ID............................. name
if I have a range, say 33-35, I want to put a value into a cell and compare it to that range. It will set another cell to a certain value, based on the range.
i.e. if (35 > A1 > 33)
A2 = B
elseif (37 > A1 > 35)
A2 = X
I'd like to compare two cells, and if the first cell is either >150, <161, OR =165 AND the second cell is ="E" to output a 1, and if false, output a zero.
I have =IF(AND(AND(Sheet1!B3>150, Sheet1!B3<161, Sheet1!B3=165 ),Sheet1!E2="P"),"1","0")
But i know that is incorrect because Sheet1!B3 can not be greater than 150 AND less than 161 AND = 165. I can't figure out how to make it >150 AND < 161 OR = 161.
Here is what I want to do:
If AND(NOT(ISBLANK(A1);A10) then B1= "NOT OK"
else
If AND(NOT(ISBLANK(A1);A1=0) then B1="OK"
End if
How to do this for a range A1:A10 in vba??
I have a report that contains 76 columns and any number of rows. I have seen between 100 and 23,500 rows. The data in each cell of each column must contain a precise number of characters. What I am trying to do is create a macro that will LEN() each cell and compare the value to row 1 of the cell's column. Row 1 contains the value of the required number of characters, row 2 contains headers and the data starts on row 3.
This is what I have so far:
Sub LENtorow1()
Dim cel As Range
Dim row As Integer
[Code] .....
It is this portion that is causing me troubles: Range(cel(ActiveCell.Column & row)). If I use a static value it works fine but when I try to make it recognize the first row of the current cell's column there is a break down. I have tried several variations, the one listed here is my latest attempt.
I am wondering if you can write me a VBA code for comparing the value inside a cell.
Code:
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) then
..............
Else
[Code]....
I have a spreadsheet and everyday it would change in length.
But I would like a macro that compares the value of each cell in column B against the value in corresponding cell in column D then moves to the next roll - so eg B2 against D2,B3 against D3......B50 against D50 until all rows are compared.
And when it finds a different makes the background colours go red !
I have a bit of a code but I think I need to loop to compare all populated cells !
I have data all in one column that I need to compare.It's basically the data between the 2nd and 3rd commas being compared to the data after the 2nd colon. I have over 860,000 entries in the database that this data is extracted from so I can't go through it one-by-one.
12EDM1,1,1638413100250,Match_Location_Number:1:1638413100250
VIK1,1,1638413100251,Match_Location_Number:1:1638413100251
WHI1,1,1638413100252,Match_Location_Number:1:1638413100252
HPR1,1,1638413100253,Match_Location_Number:1:1638413100253
I have a column of dates formated DD/MM/YYYY in column H in one sheet. I need a formula that if a cell in H is more less than 30 days from today's date to display the value from an adjacent cell.
example:
Data source table *Serial #ID #TempExp date9384-63838QDYHEISAV4
YES01/18/201010384-78618PDQA7O7FUO
yes11/18/200911384-78708QKM5XA12BK
yes12/25/2009 Excel tables to the web >> Excel Jeanie HTML 4
I need a formula that will, if the exp date is less than 30 days from today, place the serial # in a cell on a different sheet.
i am attempting to recreate a 1 armed bandit in excel and was wonderin the following.
Is it possible for excel to look at a cell and determine how many of a certain character there are within it?
i.e. if a cell contains 112 can excel work out that there are two 1s and one 2.
I am hoping i can get excel to determine whether the random output is a jackpot or not i.e 3 of the same number.
I want to compare a cell with a number of other cells to check if their value is the same. If it is the same an IF function will perform some action.
For example I want to check if A23 is equal to B23,B24,B25.....B50
Something like IF (A23= B23 to B50,"hi","bye")
The problem is that I dont know how to write "B23 to B50" in a format Excel will understand.
sample macro code to compare 2 different columns from 2 different sheets. At the end there' my code
Here's the columnA in sheet1
ColumnA
ColumnB
red
orange
[Code]....
Basically I am looking for Excel VBA code to compare 4 cell values from 1 sheet to 4 columns on another sheet, but the 4 values on other sheet must be in same rows. I am but a VBA beginner and I have been trying for days with no luck. I want it to go something like this:
IF A5 (on sheet1) = 40 AND B5 < (A value in column M on sheet2) AND C5 < (a value in column E on sheet 2 but in the same row as the previous) AND D5 < (a value in column F on sheet 2, also in same row as previous 2) THEN display model from column C on sheet 2 also from same column into sheet 1 G5
for example (see attachment) on sheet1 if you enter 40 into A5, 3846 into B5, 1492 into C5 and 1999 into D5 Then "MODEL19" (from sheet2) should show up in G5. I had put in a 'button' but it is not needed if there is a better/easier way to do this.
I have a list of Expense Codes in Column A. They look like this
0010-0020-8200-70.
Where
0010: Represents a Location
0020: Represnts the Business Type
8200-70: Represents Expense Type
I also have 3 tables on the spread sheet, which explains what these codes mean.
I need a macro that will look at the numbers in colum A and return the three types of expenses so they dont have to be looked up manually.
I am trying to write a macro and i am having trouble hgetting it right. I have large amount of data in columns and what I would like to do is the following.
1. First I need to find the first blank cell in that row.
2. After finding the blank cell, I would like to compare the value in 3rd column from the blank cell( For example if the last blank cell is Row1,ColumnR then I need to Compare Row1,ColumnO) with Column E ( Is always Column E).
3. Based on above example if Row1,ColumnO>Row1,ColumnE, then goto next row else Row1,ColumnR value should be Row1,ColumnE and I would like to get Row1,ColumnS and Row1,ColumnT vaues to be same as last 2 columns from the blank cell which was determined first (i.e., from above example Row1,ColumnS and Row1,ColumnT vaues to be same as Row1,ColumnP and Row1,ColumnQ).
4.I would like to perform the above procedure for all the rows in the worksheet and the blank cell may be anywhere in the column for that particular row.
I don't know whetehr it is possible to write a macro to perform above procedure or i need to do that manually which i hate as i have large amount of data.
I am modifiying a macro I once used to compare cell data from a worksheet to a worksheet in the same workbook. But I am having trouble getting it to work bewtween two workbooks.
View 2 Replies View RelatedI have a vb script used to compare a 3x3 matrix cell. My idea is to compare each cell by each cell. My 1st input is xx1.txt shown below
123
456
789
My 2nd input is xx2.txt shown below
1893
456
7810
I am expecting to get the below output with 89 and 10 highlighted in red because these are the difference between the 2 inputs and the rest of the numbers highlighted in green
1893
456
7810..................
I need a formula for: If cell c is greater than 0 and cell c is<> cell b return value of cell c in cell d; else value of cell b in cell d.
View 4 Replies View RelatedI have a set of values in the 2nd column (Col B). I have shown in Column C (I've added some notes to the right).
mon........7..........(first value, so nothing here)
tue........8..........na, or ERR.......................... 8 IS the highest value in B, so no math to do yet
wed.......10..........na, or ERR.......................... 10 is greater than 8…the new highest value in Col B
thu.........8...........(20.00)............................. The value, 8, is 20% less than the Highest value so far in Col B
fri..........6..........(40.00)............................. The value, 6, is 40% less than the Highest value so far in Col B.....
Namely, any value in Col B that is LESS than the highest, greatest, largest value that has preceeded it in chronological order will reflect by what % it is lower. (Eg, 8 is 20% less than 10). When a NEW higher value appears in Col B, there would be no answer in C (or simply "ERROR", or n.a.), and this new, largest value would be what subsequent values in Col B are compared to.
I'm having trouble with the "sister" formula I need to add. I've tried the function SMALL, but apparently not in the right format.... Here is some data again, with the answers I'm seeking & notes in Col C:
mon....6....(first data pt, so nothing here)
tue.....8.....33.33 (this is 2/6ths higher than 6)
wed...10....66.66 (this is 4/6th higher than 6)
thurs...8....33.33 (same as tuesday)
fri.......6.....00.00 (Equal to the lowest data pt, ie 6)
sat.....5.....(finally a vlaue less than 6, so: "ERR")
sun.....7.....40.00 (7 is 2/5th higher than 5)
mon....15....200.00 (10/5ths higher than 5)
tue.....4......(a new lowest value; "ERR")
wed....8......100.00 (4/4ths greater than 4)
That is, any value in Col B that is GREATER than the lowest, SMALLEST value that has preceeded it - in chronological order - will reflect the % by which it is greater. When a new lowest value appears in Col B, a simple text "ERR" would appear, and THAT new low value will be the new "target" the subsequent values in B are compared to. (In the example, once 5 appears, we stop looking at 6...)
I got this cell A1 - it contain one letter Cell B1 contains several. How to compare A1 with B1 and see if B1 contains the letter in A1? If then report Yes, if not No.
View 5 Replies View RelatedI have a routine that loops through a block of records containing two time fields. One is the same for every record (let's call it the start time) the other, which varies, is the last time at which an event happened before or after the Start time (let's call it the Event time). so a pseudo block of data may look like this;
A B
15:00:00 15:01:27
15:00:00 15:00:05
15:00:00 14:59:27
15:00:00 14:59:01
15:00:00 14:48:45
I need to identify the record that has the closest time in column B to that in column A (in the example above that would be B2 (15:00:05). I then save that record off and eliminate the rest for that time block. The whole subroutine then repeats for the next block of data and so on down the list, top to bottom ( times are in descending order). Note also that times will never span the 24-hour point - they all occur betrween 12:00 and 22:00 on any given day.......
I have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
Category____ Code
2200 ________22002
2323________ 232347
So, the loop should do the following:Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
Next trim one digit from the right from every string in the “Code” column.
Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
Write “ok” next to it
Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.