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

 

[JamesW]

Hi,

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Value = "" Then
        rng = Target.Address
        Target.Offset(0, 1).Value = Sheets("Sheet2").Range(rng).Value
    End If
End Sub

 

[Dazzawm]

Thanks, this is obviously a macro where I need to run it when I have completed my work is it? Is there a formula that will make the changes as I do them and press enter on that cell?

 

[Dazzawm]

I also run a test with that macro and it didn't seem to do anything. Thanks.

 

[GTO]

Thanks, this is obviously a macro where I need to run it when I have completed my work is it? Is there a formula that will make the changes as I do them and press enter on that cell?

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" cellSpacing=0 cellPadding=0 width=124 border=0 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 class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 11pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=14 height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=47>A</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=63>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Hello</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Goodbye</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Please</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">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

When you say "it looks at A1 on Sheet2", are you actually meaning that it should go look through Column A in Sheet2 and if it finds "Hello", then grab the adjacent cell's value?

 

[Dazzawm]

Thats right, yes. If I type in 'hello' anywhere on sheet 1 it needs to look for hello on sheet 2 and change it to whatever is in column 'B' next to it when I press enter.

 

[GTO]

This would go in Sheet1's module. PLease note that I used the sheets' default codenames; change to suit.

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.Offset(, 1).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

 

[Dazzawm]

How does this work then? It seems to do nothing, either when I enter on a cell or run the macro after making changes.

 

[GTO]

Where are you storing the code? It needs to be in Sheet1's module, as it is event code.

 

[Dazzawm]

Well that sort of works now. But what is happening when I type in 'hello' on sheet 1 it is putting goodbye next to it. What I want it to do is replace the hello that I type in with goodbye.