Using currentcell.offset to copy a cell from two variables

[thesnoopster2]

Here is what I am trying to do.

I have a spreadsheet with 2 worksheets. I have a variable cell and a display cell on Worksheet A and two other cells with information on Worksheet B that I need copied over to Worksheet A based on what is entered for the variable on Worksheet A.

I want to be able to enter a variable in Worksheet A, cell B25. I then want the code to look up the variable I just entered at B25 on Worksheet B, column A. Once the code finds the variable, then I want the code to copy what is in column B and enter it into Worksheet A cell C25.

In other terms, I want to enter "Bob" in Worsheet A, B25. On Worksheet B, I have 50 different names of people in column A and additional info directly to the right of their names in column B. When I enter "Bob", I want the code to find "Bob" on the Worksheet B and enter "is worthless" on Worksheet A, cell C25.

I have tried and got the code to work with several If/then statements and I accomplished the same using a select case arguement as well using something along the lines of "if B25="Bob" then C25=worksheet B("B2")" assuming Bob is in cell A2. My concern is the info on Worksheet B is fluid. I need to be able to sort and add additional lines at any given time. My data set is going to be between 500 and 800 rows. Any time I add a row and re-sort the data set, I want the code to find "Bob" in column A and then display what is in column B in Worksheet A, C25. I can continue to use the if/then statements, but they are static and if I add a new row near the top, I would have to re-number all the if/then statments in the code.

I need help to write a currentcell.offset that would look up a variable in a range, and then copy what it finds directly to the right of it over to a display cell. If anyone has any other ideas, let me know.

 

[Peter_SSs]

That didn't seem to work. Is there a way to make VLookup work from inside a macro? That function works great for me initially, but will not allow me to edit without destroying the function.

I don't understand this bit. The macro I suggested simply puts a value into a cell in column B of worksheet A. You can edit the result as much as you like. There is no 'function' to destroy.

If I can find a macro to run that function, I would be able to edit the result and then just re-run the macro as needed. Is there a way to do this?

If you have edited the result, why would you want to run the macro again and bring in the the unedited result again? (Clearly I don't understand this either)

This is working, sorta. Everything works good, but how do I direct the outcome? It is finding the info I need, but it isn't putting it where I need it.

To put it cleanly, my variable is in Worksheet A, cell H351. My array is on Worksheet B, A5:B1000, and I need the result on Worksheet A, cell C390

The above formula results in the outcome to be in cell I351

So, my suggested variation is:

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myInput <SPAN style="color:#00007F">As</SPAN> Range, myOutput <SPAN style="color:#00007F">As</SPAN> Range, aFound <SPAN style="color:#00007F">As</SPAN> Range<br>     <br>    <SPAN style="color:#00007F">Set</SPAN> myInput = Range("H351")<br>    <SPAN style="color:#00007F">Set</SPAN> myOutput = Range("C390")<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, myInput) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> myInput.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            myOutput.ClearContents<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> aFound = Sheets("Worksheet B").Columns("A").Find _<br>                (What:=myInput.Value, LookIn:=xlValues, LookAt:=xlWhole, _<br>                MatchCase:=False, SearchFormat:=False)<br>            <SPAN style="color:#00007F">If</SPAN> aFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                myOutput.Value = "Not found"<br>            <SPAN style="color:#00007F">Else</SPAN><br>                myOutput.Value = aFound.Offset(, 1).Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[thesnoopster2]

that actually works exactly as needed. One last thing, I thought I could ammend what you had to include the additional columns I also need to be transfered over. I figured to just change the values to the vlookup and I could get the additional info. I have expanded my array to be larger and added some variables. I changed the column numbers in the vlookup function from 2 to 3 and to 4 and to 5 in order to get the additional info from the adjorning colums as well, but that didn't seem to work. How else can I get the info from the adjorning columns?

Sub My_VLOOKUP()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Dim rLookupVal As Range, rOutput As Range, rOutput2 As Range, rOutput3 As Range, rOutput4 As Range, rOutput5 As Range, rOutput6 As Range, rArray As Range, ws1 As Worksheet, ws2 As Worksheet<o></o>
<o></o>
Set ws1 = Sheets("Sheet1") 'Sheet with Lookup value<o></o>
Set ws2 = Sheets("Sheet2") 'Sheet with Lookup array<o></o>
Set rLookup = ws1.Range("H351") 'Cell with lookup value<o></o>
Set rOutput = ws1.Range("C390") 'Cell to output the lookup result<o></o>
Set rOutput2 = ws1.Range("C438") 'Cell to output the lookup result<o></o>
Set rOutput3 = ws1.Range("C486") 'Cell to output the lookup result<o></o>
Set rOutput4 = ws1.Range("C534") 'Cell to output the lookup result<o></o>
Set rOutput5 = ws1.Range("C582") 'Cell to output the lookup result<o></o>
Set rOutput6 = ws1.Range("C630") 'Cell to output the lookup result<o></o>
<o></o>
Set rArray = ws2.Range("A5:H1000") 'Cells with lookup array<o></o>
<o></o>
<o></o>
If Not IsEmpty(rLookup) Then<o></o>
If WorksheetFunction.CountIf(ws2.Columns(rArray(1).Column), rLookup) > 0 Then<o></o>
rOutput = WorksheetFunction.VLookup(rLookup, rArray, 2, 0)<o></o>
rOutput2 = WorksheetFunction.VLookup(rLookup, rArray, 3, 0)<o></o>
rOutput3 = WorksheetFunction.VLookup(rLookup, rArray, 4, 0)<o></o>
rOutput4 = WorksheetFunction.VLookup(rLookup, rArray, 5, 0)<o></o>
rOutput5 = WorksheetFunction.VLookup(rLookup, rArray, 6, 0)<o></o>
rOutput6 = WorksheetFunction.VLookup(rLookup, rArray, 7, 0)<o></o>
Else<o></o>
MsgBox "No match found"<o></o>
End If<o></o>
Else<o></o>
MsgBox "Lookup cell " & rLookup.Address & " is empty"<o></o>
End If<o></o>

 

[AlphaFrog]

Try this...

Sub My_VLOOKUPS()

    Dim rLookup As Range, rArray As Range, lMatchedRow As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("Sheet1")          'Sheet with Lookup value
    Set rLookup = ws1.Range("H351")     'Cell with lookup value
    
    Set ws2 = Sheets("Sheet2")          'Sheet with Lookup array
    Set rArray = ws2.Range("A5:A1000")  'Cells to look in for a match
    
    If Not IsEmpty(rLookup) Then
        If WorksheetFunction.CountIf(rArray, rLookup) > 0 Then
            lMatchedRow = WorksheetFunction.Match(rLookup, rArray, 0) + rArray(1).Row - 1
            ws1.Range("C390") = ws2.Range("B" & lMatchedRow)
            ws1.Range("C438") = ws2.Range("C" & lMatchedRow)
            ws1.Range("C486") = ws2.Range("D" & lMatchedRow)
            ws1.Range("C534") = ws2.Range("E" & lMatchedRow)
            ws1.Range("C582") = ws2.Range("F" & lMatchedRow)
            ws1.Range("C630") = ws2.Range("G" & lMatchedRow)
        Else
            MsgBox "No match found"
        End If
    Else
        MsgBox "Lookup cell " & rLookup.Address & " is empty"
    End If

End Sub