View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Don Guillett Don Guillett is offline
external usenet poster
 
Posts: 10,124
Default Code using A sheet to find a number

If, as I understand it, you are trying to match the value in cell b3 to a
cell on sheet2 in col A and use the value in the next column, then it seems
to me that a simple vlookup FORMULA would do this for you without code. Or,
am I still not understanding?

=vlookup(sheet1!b3,sheet2!a3:b1000,2,0)

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
No Just one instance of the Value


"Don Guillett" wrote:

OK. Now, Is there MORE than one instance of the value to look for. Or,
Just
ONE....

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
Its looking on sheet1 B3
would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it
is
then
use the Value in Column B next to the match

"Don Guillett" wrote:

What you have now is looking for one instance of the value in cell b3.
This
would be much easier with a select case statement. My question is:
Are you looking for all three of these? And, are there more than one
instance? Maybe you want.

Sub findem()
myarray = Array("a", "b", "c")
For Each c In myarray
x = Columns(4).Find(c)
Select Case UCase(x)
Case "A": y = 1
Case "B": y = 2
Case "C": y = 3
Case Else
End Select
MsgBox y
Next
End Sub


--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
Can someone help please
I would like for it to look at sheet2 and see if "V014989" Is in the
column
A Row 2:65535



Private Sub StoreAccountNumbers()
On Error Resume Next
With ActiveSheet
If .Range("B3").Value = "V014989" Then
.Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@")

ElseIf .Range("B3").Value = "V014990" Then
.Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@")
End If
End With
With ActiveSheet
If .Range("B3").Value = "V014991" Then
.Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@")
ElseIf .Range("B3").Value "V014991" Then
.Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@")
End If
If Err.Number < 0 Then
MsgBox Err.Number & " -- " & Err.Description
End If
On Error GoTo 0
End With
'PromptForSave
'SaveWorkbookToFolder
End Sub