Free Microsoft Excel 2013 Quick Reference

Find a value in hidden sheet

Hi there

Is there a code to find value in hidden excel sheet ? using Find Method


Hi:

I'm trying to write a macro to find a value in a column and get some data from it's row. The value can be repeated several times. I wrote the following macro, but it's working only to find the first value. After that I'm getting this error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Really I don't know what is going on. I'll appreciate any help.

Thanks

Armando
Sub Aux_10()

 Dim myrow, i As Integer
 Dim rng, cell1, cell2 As Range

 Windows("Thickness Data.xlsx").Activate
 Sheets("Sheet1").Select
 WorkOrder = Range("A20").Value
 cycles = Range("D18").Value
 Windows("Status.xls").Activate
 Sheets("Status").Select
 LastRow = Range("A40000").End(xlUp).Row
 Set cell2 = Range("A" & LastRow)
 i = 1
 NextRow = 1
 FirstRow = 22
 For i = 1 To cycles
 Set cell1 = Range("A" & NextRow + 1)
 myrow = Range(cell1, cell2).Find(WorkOrder).Row
 NextRow = myrow
 Range("B" & myrow).Copy
 Windows("Thickness Data.xlsx").Activate
 Sheets("Sheet1").Range("A" & FirstRow + i).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Next
 End Sub


Dear Friends,

Here is my problem,I want to find a value in all open workbooks and then select the workbook containg value.

Any help will be benificial to me.And I will be thankful.

Regards,

HI, i need a function that finds a value in a table range and returns
the next cell value.
For example

Got this list
A B C
1 RED 120
2 BLUE 10
3 BLACK 230
4 PINK 330
5 WHITE 500
6

If want to find black...so the function should return 230. Thanks

How would you search multiple hidden sheets named JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Using a textbox to return a value to find on these multiple hidden sheets. The code must find Orange, Oranges Orange BLossom by typing in "Ora".

It then moves to the first value found on the first worsheet found and mekes it the active cell, asks with a msg box to find the next value and so on. This code works but it only finds the value on the sheet THAT userform was designed for in the sheet code page:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim MyValue, MyFindNext, FirstFound, LastFound
MyValue = Me.TextBox1
If Len(MyValue) = 0 Then Exit Sub

StartSearch:

On Error GoTo err_Trap
Cells.Find(What:=MyValue, After:=[A1], LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
FirstFound = ActiveCell.Address
MyFindNext = vbYes
Do Until MyFindNext vbYes
MyFindNext = MsgBox("Would you like to find the next instance of '" & MyValue & "' ?", _
vbYesNo, "Find Next ?")
If MyFindNext = vbNo Then Exit Sub
LastFound = ActiveCell.Address
Cells.FindNext(After:=ActiveCell).Activate
If ActiveCell.Address = FirstFound Then
TryAgain = MsgBox("All instances of '" & MyValue & "' have been found on this sheet." _
& vbCr & vbCr & "Want to cycle through them again?", 4, "All Instances Found")
If TryAgain = vbYes Then
GoTo StartSearch
Else: Exit Sub
End If
End If
Loop
On Error GoTo 0
Exit Sub

err_Trap:
If Err.Number = 91 Then
MsgBox "Could not find '" & MyValue & "' anywhere in this sheet.", , "Search Results"
Else
MsgBox Err.Number & ": " & Err.Description
End If

End Sub

Code:
I really need help on this.  Thanks for all your help.
chazrab


hi i m a new user, and i from mexico so my english is not so good...i hope you understand me... i need to know how to find a value looking for in two different sheets...this is the formula i m making...

=if(A1=(vlookup(A1,sheet1!A:B,1,false)),vlookup(A1,sheet1!A:B,2,false),vlookup(A1,sheet2!A:B,2,false)

i am using Vlookup and getting data from another sheet, my main data source
is a file downloaded from net, in that file the feild having company name
usually change due to any suffix or prefix and then i cant get the data of
that company from v lookup bcz company name got changed, what i have to do
now is to build a formula which can lookup a value in another sheet contaning
any word or a part of the company name, like ( if i have to find the
corresponding value of a commpany name "Microsoft Corporation" and the
downloaded file for daily update have the name today as "Microsoft
Corporation Ltd." now the Vlookup formula cant get the value of that specific
row, if anything can help to find the vell value using like containg text and
return the corresponding value of that cell

i am using Vlookup and getting data from another sheet, my main data source
is a file downloaded from net, in that file the feild having company name
usually change due to any suffix or prefix and then i cant get the data of
that company from v lookup bcz company name got changed, what i have to do
now is to build a formula which can lookup a value in another sheet contaning
any word or a part of the company name, like ( if i have to find the
corresponding value of a commpany name "Microsoft Corporation" and the
downloaded file for daily update have the name today as "Microsoft
Corporation Ltd." now the Vlookup formula cant get the value of that specific
row, if anything can help to find the vell value using like containg text and
return the corresponding value of that cell

Dear all,

Assume I have this table like this:

Column A------------Column B
Row 1 Criterium 1

Row 4---------------20
Row 5-------------- 25

Row 7 Criterium 2

Row 10---------------30
Row 11-------------- 35

Now I want for example to have the value for Criterium 2, Max. Basically, I want to find a match in column A, and then define Excell to go one (or more) column(s) to the right and give me a value several rows lower. How can I find this?

Probably not the hardest one, but would appreciate some help.

Thanks and kind regards,

Juriaan.

I'm trying to find a suitable formula that looks at two cells (J3 and V3) in Sheet 1 (a person's employment grade (e.g. 18) and their performance percentage e.g. 92.5%), finds those values in a table on Sheet 2 and then places the corresponding value from that table in Sheet 1 at cell Z3.

Any help would be greatly appreciated.

i have two sheets where i need to take in values in sheet2 to display in sheet1. instead of copy pasting, i want to create a macro since its easier.

Sheet1:

A B C
1 KPI Card Paper
2 Income
3 Losses

Sheet2:

A B C
1 KPI Card Paper
2 Income 12 13
3 Losses 9 5

i need to display the numbers in Sheet1 but based on the following:

if i click on the second cell in column B to get the value for income, the macro is supposed to read the value in col B row 1, find a match in sheet2's range
A1:C1, go back to sheet1 and read value in A2, find a match in sheet2's range A1:A3. if it satisfies both conditions, the value displayed in sheet1 will be retrieved from B2 in sheet2.

any idea?

I need a formula to find a value (that is in sheet 1) from a range (sheet 2) but their not exact matches. For example, I need to find the value '0003' (sheet 1) in a range of numbers in sheet 2; however, the number that it needs to find is 'F500003' and I need the formula to return the full 'F500003' number. Any hints?

Hello,

I am having trouble trying to figure this one out. I'm using MyArr to search and find a value in one sheet, then copy that value and paste it another. That part works fine, but I need to get the values in the cells around it. I think the values of the multiple rows below and two columns to the right. Here is what I have currently:


	VB:
	
 Copy_To_Another_Sheet_1() 
    Dim FirstAddress As String 
    Dim MyArr As Variant 
    Dim Rng As Range 
    Dim Rcount As Long 
    Dim I As Long 
    Dim NewSh As Worksheet 
     
    With Application 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
     
    MyArr = Array("Remaining Scheduled Jobs") 
    Set NewSh = Sheets("Sheet2") 
    With Sheets("RTS_UF310").Range("C1:C100") 
        Rcount = 0 
        For I = LBound(MyArr) To UBound(MyArr) 
             
            Set Rng = .Find(What:=MyArr(I), _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not Rng Is Nothing Then 
                FirstAddress = Rng.Address 
                Do 
                    Rcount = Rcount + 1 
                    Rng.Copy NewSh.Range("A" & Rcount) 
                     
                    Set Rng = .FindNext(Rng) 
                Loop While Not Rng Is Nothing And Rng.Address  FirstAddress 
            End If 
        Next I 
    End With 
    With Application 
        .ScreenUpdating = True 
        .EnableEvents = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Sheet 1 is my results page, this data hardly ever changes.

On Sheet 4 - I copy and paste data here and the data will change.

I need (Sheet 1 column A) to look at (Sheet 4 column A), if a value listed in (Sheet 1 Column E) matches a value in (Sheet 4 column A) then I need a "1" placed in column Sheet 1 Column A. I have tried several formulas and have been unsuccessful.
If any one can assist me, I would surely appreciate it.
Thank you
If needed I can attached my workbook

hi,
anyone to solve my problem ?

I have an Abacus that contain :

Cell B1 to R1 contain (CABLE SIZE):

4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm, 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm

Cell A2 to A20 contain (POWER):
0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw, 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw

Cell B2 to R20 contain a number (Maximum Length).

How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a LENGTH?

PS, the LENGTH I have his not a value in the table, so I want to take the above one.

Regards

Hi, I've looked for help on this but everything seems to refer to straight lists, or arrays, not matrix like ranges. Sorry if it's obvious, but I am stumped!

I am trying to find a value within a table, then return the top cell in the column it's in. ie. In my example below (TestList.xls) I am looking to input a Site name into J3. I would like J6 to populate with the Date at the top of the column in the table that the site name exists in. eg. Inputing "AB1527" into J3 should populate J6 with "Tue - 05/15".

I have tried various combos of index and match but Match wants a straight list of cells for input, not a range. How do I do this???

TestList.xlsx

Hello! I have a small problem. I want to search a value in an array and
return the value true or false. I tryed with "if", "and", "not", but a
strange thing happends: when I press the function button to see if my formula
is wrong, it seems that the formula is corect, but when i insert the formula,
it says: #N/A, #VALUE!. the formula is
IF(A3<>Array1,IF(A3<>Array2,IF(A3<>Array3,"VALUE1,VALUE2)),VALUE3). A3 is the
refference; Array1,2,3 are the arrays where i want to find the value; and
Value1,2,3 are the results. Can anyone please help me!

I made a spreadsheet with 3 columns of numbers and 1 of dates. It is roughly
600 lines long, but when I try to "find" a value it comes back as not in
spreadsheet even though I see the value listed.

thanks

What formula I should use to find a value of a specific cell in a defined
rectangular database table by a reference to the row ID (text) and the Column
ID (number)?

Hello everybody,
I have a question concerning value matching. I am using excel 2007.
I have two sets of values. An array which contains measurements (numbers) and a list containing theoretical values (numbers).
I would like to find a way in excel:
- To compare each individual value of the array against all values from the list (this list is fixed).
- so that excel searchs the value in the list equal to the value of the array tested + or - minus a certain threshold (ex. + / - 0.02)
- And in turns to have this value from the list appearing in the cell where I am applying the formula.
I hope my situation/question is clear enough.
Thanks in advance,
Zadig

Hello,

I would like to look for a value in each column of data and count the match with another corresonding column. Then the total of the values that have matched (are the same), is displayed in the result. I am attaching a sheet example as its a little difficult to explain. If anyone could help it would be amazing.

Thank you,
Sans

Hello,

I am needing VBA code that will find a value (in my case a building name) then return what column it is in.

The bigger picture is that I am searching for a value in a row and column to then select a cell based on these two criteria.

Help with both of these tasks would be appreciated!

The code for the find row value is as follows:


	VB:
	
 Test() 
    Dim l As Long 
    l = Application.WorksheetFunction.Match("TEST", Range("A1:A100"), 0) 
    MsgBox "Found 'TEST' at row : " & l 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Justin

I have two tables:

First table M24:R37 has two columns, one column has single values per cell (K24 has A; K25 has B, etc.) second column has multiple values per cell (M24 has Z1, Z2, A1; M25 has B1, B1A, B1B; etc.)

Second table has one column of single values per cell (A1, B1, etc.) starting in cell A5. Second column is blank and would like to be populated with a corresponding value from column 1 in table one (M).

What I would like to do is to create a Vlookup type command that will look for a value in second table Column A from the first table M24:R37 and return value from first column (M) in that table. The issue I am having is with the values in column 2 of table one that have values separated by commas. I do not think excel is recognizing that it is a list of individual values.

A1
B1
B1A
B1B
B1C
etc.

A - Z1,Z2,A1,B5
B - B1,B1A,B1B,B1C
C - D1,D1A,D1B,D1C
D - C1,C1A,C1B
E - D5,D5A
etc. - etc.

Hi Guys, Using VBA, how do I find a value in a text string?

Lets say I have a text string consisting of "CASTLE HILL 2154 SYDNEY NSW AUSTRALIA", this can change, its position can change, sometimes it will not even exist.

How do I get just the 2154 from that string?

Thanks

Dan

Hi all,

I would like to find a value in a named range and return it in a cell
if this criteria exists....sheet1 A2 if named range value = A1 then
return value from 3rd column away from match same row

So if A1 Sheet1 contains "test" and named range (lets say "Find")
Sheet2 contains "test" (lets say in E2) then A2 Sheet1 should show the
value of E4 Sheet2.

Dont know whether that is clear but its been driving me nuts as i have
tried the Match, Index and Offset functions but without
success..........Can you Help?

Simon

--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=465487