IF Than And Find

Nov 14, 2008

Need to find a solution but cant find it any where What im trying to do is. If Cell A3 = 80 or less then use that number in cell A4 if it equals more then 80 then use 80 in cell A4 and put the rest in Cell A5. So if A3 =74 then cell A4 will show 74 and cell A5 will have 0 If A3 = 85 then cell A4 will show 80 and Cell A5 will show 5

View 2 Replies


ADVERTISEMENT

IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value

May 23, 2006

I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)

View 4 Replies View Related

Find Wont Find Existing Text When Columns/Rows Hidden

Oct 8, 2006

Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.

The error happens at this point: ...

View 3 Replies View Related

Find Format: Find Method And Combine It With A Countif Or Loop

Feb 20, 2007

I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.

Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub

View 4 Replies View Related

Find Formula (find Jobs With Split Names)

Aug 5, 2009

DSee the attached.

The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.

BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).

I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.

View 5 Replies View Related

Find, Meet Condition, Sum Range, Deduce, Find Next

Sep 23, 2007

i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)


columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .

View 9 Replies View Related

Find In VBA Code: Find Exact Match

Oct 9, 2006

I'm using the below VBA

Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With

Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :

Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham

Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

How do i make it search for an exact match?

View 4 Replies View Related

Find (Using Find) Using Variables With Or Without Quotes

Sep 2, 2009

In the code below Dim "x" is a string, the string may or may not contain quotes ( "" ). I might search for a string like this:

The big brown fox
or like this:
The "big" brown fox

I just found out the string can not be found when it contains quotes as in my second example.

View 4 Replies View Related

Find The Formula That Is Used To Find These Numbers

Dec 26, 2009

The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.

71461272871082
53820056426572
61070709150530
75833373805331
55466908456008
49264716615511
12387558353260
20013928484175
67975657084961
38006283229553
68973249414920
65179634653276
66971872613128
59646203411273
69785611400836
58284602307740
64723009925464
57930863495088
58816593932906
62736335606616
52119154778580
60043359437317
61921553996320
51507747216899
13197956063818
64210347411256
65399671193442
55656585589500
55148886441637
30850639587521
69081133766387
54139417804940
53408393698534
62310293191095
30512556590168
14514168265124
89078208134056
85177364363577
68714858216023
17584065849687
52691521917548
9840524785846
59268687494601
89374963791119
45989738961270
65040033345415
15061518946940
55392084949214
45103824265242
84181944909073

View 9 Replies View Related

Find Dates With Find Statement

Mar 6, 2007

I have dates in column A (Source Dates) and Column C (Target Dates). All data is formatted as Dates. I want to find which dates in column A have a matching date in column C. When using the find statement within a For Each loop I can not find a date match unless I format the target dates as General. How can I use the Find Statement using dates without formating the target dates as General?

Option Explicit

Dim SourceDate As Range
Dim TargetDate As Range

Sub FindTargetDate()
Columns("C:C").Select
Selection.NumberFormat = "General"
For Each SourceDate In Range("A1:A32")
'MsgBox SourceDate
Set TargetDate = Sheet1.Range("C1:C7").Find(SourceDate, LookIn:=xlValues)

View 7 Replies View Related

Find Method To Find Nth Occurence

Jul 17, 2007

Private Sub cmdShowdata_Click()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim cel As Range
Dim rng As Range
Dim c As Range
Dim i As Long
Application. ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open("C:Documents and SettingsDesktopStaff Recoed 2")
Set Source = wbSource.Sheets(1).Columns(1)
With Tgt
.Activate
'clear old data
Range(.Cells(3, 2), .Cells(200, 5)).ClearContents
' Loop through names in column A
For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then...................

The above vba command which is extract the data from the Other workbooks. It looks for the "Staff 001", "Staff 002"...these parameters to transfer the data to the worksheet. But, the "Staff 001" data must appear twice in each workbooks. If i use the above command, i only can extract the FIRST "Staff 001" average data. But SECOND "Staff 001" average data cannot extract. I know it may be use FindNext method to do this but i am not sure how to write it

View 5 Replies View Related

Find Cannot Find Existing Text / Value

Mar 5, 2008

i am trying to do a search and i came across this. In the excel file attached you will see 2 column. Now if you take copy and paste "sterling rd" which is standing alone into the crtl F search field. It wont find it.

I have played with the format (made both general), i have CLEAN, Proper the text and tried that didnt work either.

I dont know what else to try. I am working with access, with a much larger list, however the linking to excel wont work and i know there is some difference font or format that i am missing.

View 5 Replies View Related

Find Value On Row & Find Last Used Column Before Found Value

Mar 17, 2008

Dim ColNo As Byte
Dim LastCol As Byte

ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range("sheet1!b1").End(xlToRight).Column

But the below code errors?

Dim ColNo As Byte
Dim LastCol As Byte

ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range(Cells(1, ColNo)).End(xlToRight).Column

View 3 Replies View Related

Find A Group Of Unique Rows- Find Non-zero Value In A Column - Fill Column With That Value?

May 27, 2014

There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.

First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.

Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.

The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.

View 3 Replies View Related

Macro To Find A Word In A Sentence By Using The "FIND" Method

Dec 14, 2008

giving me a macro to find a word in a sentence by using the "FIND" method.
For example I have a sentence say "I am happy" in which I have to search whether any of 2 words say "Happy" or "Sad" is there or not. If the sentence contains any of these words, then that row has to be highlighted.

View 9 Replies View Related

Find To Find All Occurences

Nov 17, 2006

I can't seem to get the findnext to work. I can find the first instance but none after that. Here's my code.

Dim B As Range
Dim SheetNumb As Integer
SheetNumb = 1
Do While SheetNumb <= Sheets.Count
Sheets(SheetNumb).Select
If ActiveSheet.Name = "Page1" Then
Exit Do
End If
Set B = Sheets(SheetNumb).Cells.Find(" Mechanical", SearchOrder:=xlByColumns, LookIn:=xlValues)
If Not B Is Nothing Then
B.Activate
FindAddress = B.Address
Do
B = Sheets(SheetNumb).Cells.FindNext(B)
Loop While Not B Is Nothing And B.Address <> FindAddress
End If
SheetNumb = SheetNumb + 1
Loop

View 8 Replies View Related

Macro To Find Value From One Cell In Sheet And Find In Another Sheet

Mar 27, 2014

I am trying to look up a numerical value in Sheet 1 Cell:F7 and find that value in Sheet 2 Column B, then offset 29 columns in the row where the value was found. The number will not have any duplicates within Sheet 2.

View 8 Replies View Related

Find And Find Again - Object Variable Or With Block Variable Not Set

Jun 5, 2007

After doing a search, I need to ask an Excel 2003 question. I'm creating a tool to open three sparate workbooks. One is the driver and I need to use it to pull data from the second and then to place that data along with additional data into the third.

My driver data may consist of a single value or multiple values separated by semi-colons in a cell. A single value works fine. The first value in a multi-value condition works fine. the second find, however, gives me an object error. I'm using the " split" verb to separate the values. Here's my code. Have you any idea why the second find is throwing up this error when the first find works correctly?

varData = Split(strRef, ";", -1)
For J = 0 To UBound(varData)

varSrchVlu = Trim(varData(J))
' do the ARIS Exrtact matching
Workbooks(strManualFile).Activate
Workbooks(strARISExtract).Activate ' Activate ARIS Extract
Sheets("Processes").Cells(2, 1).Activate
Workbooks(strARISExtract).Sheets("Processes").Range("A2").Select
Workbooks(strARISExtract).Sheets("Processes").Columns("A:A").Select

View 3 Replies View Related

Find Replace :: Find All Occurences Of And Or AND And Replace With And

Oct 26, 2009

Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.

Example

Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or

View 9 Replies View Related

Find Odd One Out

Mar 11, 2014

I've basically got Two Lists of Names. One list has 7365 Names and one has 7366. I'm missing 1 name in one list. The list consists of many different surnames, some multiple times over. I basically need to find the ONE Name in the 2nd List. I've put one list into Column A and one list into Column B. The Extra Name is in Column B.

At the moment, I am sifting through the list name by name but I've got multiple lists to go through and this is just one. Some of the lists have almost 20,000 names in so this is quite a small one but thought if I could fix this then I could apply it to the others.

View 2 Replies View Related

Find Last Row..

Oct 26, 2008

I have a worksheet that has data in column C. The data is separated by blank rows.
How can I find the last row in that column with data. Following function stops at the first blank cell at the end of first block of data.
.End(xlDown).Row

Is there a way to make the code look at rest of the column and find the last row with data?

View 2 Replies View Related

Find Max Value Then Get Another Value From Same Row

Dec 3, 2011

I have a workbook with a sheet for each employee

In those sheets (col AG, rows 3 to 80) I need to find the max value

That's OK I can do this using for example =MAX(Employee0!$AG$3:$AG$80)

What I'm totally lost on is then being able to retrieve the value in column AE of the same row in which the Max value is located.

View 2 Replies View Related

VBA Find

Aug 17, 2007

I am trying to use the following code as part of a macro i am writing, however it doesn't seem to like the curr refernce in the find code. I have set it up this way as it need to look it other sheets for the value which is contained in cell B4 of the summary sheet.

curr = Sheets("Summary").Range("B4")

Selection.Find(What:=curr, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate

View 9 Replies View Related

Mid And Find The Value

May 22, 2009

the value is not found it gives me "Value error". it picks up another line which I don't want ie.. From 200903 - To:200904.

=MID(K9,FIND(":",K9)+2,FIND("-",K9)-2-FIND(":",K9))
or
=+MID(L9,SEARCH(":",L9)+2,SEARCH("-",L9)-SEARCH(":",L9)-2

PROJECT NUMBER : 353089 - NORTHERN ALTA INSTITUTUE OF TECH
FROM : 200903 - To : 200904

Result:
353089
200904 (Don't want this to be picked up)
# Value (Instead of # Value error I want to have 0 show up)

View 9 Replies View Related

Find Within A Find

Nov 16, 2006

Is it possible to do a find while in another. Here's what I'm attempting to do.

1. I'm searching through a column for a project number.

2. If a project number is found it's location (C14) is passed to a variable (TopProjNum).

3. It then looks for the next project number and records it's location (C46) as well (BottomProjNum).

4. I then strip the column letter out and concantinate another letter for each.

5. C14 becomes G14 and C46 becomes G46.

6. I use that as a range to start another search while I'm still in the first search.

7. I'm trying to find the word " Mechanical" in the range (G14:G46).

I can't seem to make it work. I have attached my feeble attempt if you want to see the code.

View 3 Replies View Related

Find A Value

Apr 26, 2007

I have a spreadsheet that i need to determine where a value lies in a column and depending on the column io need to add a value to another sheet.

so if value 555 is in cloumn A on sheet Coach i need to put a I in the cell with this formula

and if value 234 is in column b I need to put a F in the cell on the separate sheet with this value

View 6 Replies View Related

Find The Last Non Zero Value In A Row

Jun 11, 2007

I'm trying to find the last non zero value in a row. My data looks like this:

# J F M A M
A 4 5 5 5 5
B 0 0 3 3 3
C 0 0 0 0 2
D 0 0 3 3 0

I want to compare the earliest value to the latest (e.g. for A, that would mean 4-5 = -1; for B that would mean 3-3=0, etc). I have a formula that finds the first non zero value in the row, but have been unsuccessful in writing one that finds the "last" non zero value in the row.

View 2 Replies View Related

Find Next Button

May 31, 2013

I'm running into a couple problems with a Userform I'm creating at work. I have implemented a Search button that searches by the roll number. The problem is that it stops on the first record found. I have some duplicated roll number on my spreadsheet that I would like to be shown when somebody clicks a "Find next" button.

I have attached my spreadsheet for an easier understanding : TEST.xls.

View 2 Replies View Related

Find Value In Another Worksheet Using VBA?

Dec 13, 2013

With reference to the attached workbook, I need to write a VBA code which will look up the value in cell T4 from worksheet "tdploader" on the "cashbalance" sheet and return the date in row 1 based on which column the value is found e.g. H1 in the attached workbook example. The date should be returned back to cell P4 on the "tdploader" sheet. I have tried to use the Find VBA formula to no avail.

View 1 Replies View Related

Find Max Value In The Column

Jun 30, 2006

In my application I need to find the max value in one column then sum the max value with "1" and show the result in a textbox, and doing this operation by pressing a command button.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved