Macro

[ryan_law2000]

I need the following:

column B will have any number 1-1000 .
If B5 for example = 3 i want D5 to = "Sheet Info" Find that # (3) in column A select the cell right beside it in column B and paste it.
remember: it can be any number 1-1000 in any row(column B)

What macro would work for this?
I do now want to use a formula because a number may be manually entered, deleting the formula.

Any ideas?

 

[ryan_law2000]

instead of "Sheet Info" can it be Sheet4?

 

[ryan_law2000]

Does anyone know what the code would be for this? Would this be like a Vlookup code?

 

[Jonmo1]

That's a Vlookup..

Try

=VLOOKUP(B5,'Sheet Info'!$A:$B,2,FALSE)

instead of "Sheet Info" can it be Sheet4?

No, formulas can't use the codename for a sheet, only the tab name.
However, if the reason you ask is because the sheet might get renamed sometime in the future,
Don't worry, any formula(s) referring to a sheet that is renamed will automatically adjust themselves to the new sheet name.


Hope that helps.

 

[ryan_law2000]

I need this to be code in VBA. Numbers could be manually entered in that cell deleting the formula. Do you know the code that would be used?

 

[Jonmo1]

Try this in the sheet's module (not a standard module)

Right click the sheet's tab, View Code
Paste it there

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range, Fnd As Range
Application.EnableEvents = False
 
Set MyRange = Intersect(Target, Range("B:B"))
If Not MyRange Is Nothing Then
    For Each c In MyRange
        On Error Resume Next
        Set Fnd = Sheet4.Range("A:A").Find(c)
        On Error GoTo 0
        If Not Fnd Is Nothing Then
            c.Offset(0, 2).Value = Fnd.Offset(0, 1).Value
        End If
        Set Fnd = Nothing
    Next c
End If
 
Application.EnableEvents = True
End Sub