Macro Or Formula Required

[Dazzawm]

I have a listed of numbers/text on sheet 2 in column A and B. When I type a number anywhere on sheet 1 and press enter I would like it to look at sheet 2 and put the corresponding number/text from column B.

i.e

<TABLE style="WIDTH: 93pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=124 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 11pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=14> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=47>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=63>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Hello</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Goodbye</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Please</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>Thankyou</TD></TR></TBODY></TABLE>

So when I type in Hello anywhere on sheet 1 it looks at A1 on sheet 2 and puts goodbye in for me, and when I type in Please and press enter it puts Thankyou in for me and so on...

Thankyou

Excel 2007

 

[GTO]

Just take out the destiantion's Offset:

                'CHANGE
                'Target.Offset(, 1).Value = rngFound.Offset(, 1).Value
                'TO
                Target.Value = rngFound.Offset(, 1).Value

 

[Dazzawm]

Thats it, thanks for all your help.

 

[Dazzawm]

This macro/formula works perfect. I just need it to do one other thing. At the moment if I enter a number that isn't on sheet 2 it makes the cell blank. What I want it to do is leave the number in that cell if it is not on sheet 2 or if there is not a number in column 'B' next to it.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFound As Range
 
    If Target.Count = 1 Then
        If Not Target.Value = vbNullString Then
 
            Set rngFound = RangeFound(SearchRange:=Sheet2.Range("A:A"), _
                                      FindWhat:=Target.Value, _
                                      LookAtWholeOrPart:=xlWhole, _
                                      SearchRowCol:=xlByColumns, _
                                      SearchUpDn:=xlNext)
 
            If Not rngFound Is Nothing Then
                Application.EnableEvents = False
                Target.Value = rngFound.Offset(, 1).Value
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub
 
Function RangeFound(SearchRange As Range, _
                    Optional FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
 
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange(1)
    End If
 
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

 

[GTO]

I am not seeing how. If rngFound is not Set, it does nothing. After Set rngFound... put in:

Debug.Print rngFound.Address

See what cell it found and maybe it will make sense as to why.

 

[Dazzawm]

Well if i enter for example abc12345 and it looks for it on sheet 2 and there is nothing next to it in column 'B' it returns blank rather than leaving the original number i typed in what i want it to do.

 

[GTO]

This macro/formula works perfect. I just need it to do one other thing. At the moment if I enter a number that isn't on sheet 2 it makes the cell blank. What I want it to do is leave the number in that cell if it is not on sheet 2 or if there is not a number in column 'B' next to it...

Okay, I misunderstood the above.

Here's just the Sub, the Function remains the same.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFound As Range
    
    If Target.Count = 1 Then
        If Not Target.Value = vbNullString Then
            Set rngFound = RangeFound(SearchRange:=Sheet2.Range("A:A"), _
                                      FindWhat:=Target.Value, _
                                      LookAtWholeOrPart:=xlWhole, _
                                      SearchRowCol:=xlByColumns, _
                                      SearchUpDn:=xlNext)
            If Not rngFound Is Nothing Then
                If Not rngFound.Offset(, 1).Value = vbNullString Then
                    Application.EnableEvents = False
                    Target.Value = rngFound.Value
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
End Sub

 

[Dazzawm]

Thanks, I wasn't that clear in what I required.

 

[Dazzawm]

Since the change it keeps the number that I type in when I press enter instead of replacing with the number that is adjacent to it in column 'B'. So to recap if I enter a number on sheet 1 i want it to look and see if that number is on sheet 2 and replace it with the number that is next to it in column 'B' when I place enter. If the number I enter on sheet 1 is either not on sheet 2 or there is no number next to it in column 'B' I want it to leave that number in the cell when I press enter.

 

[Dazzawm]

If its easier if there is a number in 'A' with nothing next to it in 'B' i will delete it from the sheet, so when I enter a number on sheet 1 and it is not on sheet 2 it keeps the number I enter, and only changes it when there is a number next to what I enter in 'B'.

Does that mean I revert to the original code?

 

[GTO]

Try this, as apparently I suffered brain damage yesterday...
Change: Target.Value = rngFound.Value
To: Target.Value = rngFound.Offset(,1).Value