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]

Could you just use a VLOOKUP formula in column C of Worksheet A?

 

[thesnoopster2]

Been there, tried that. Unfortunately that will not work either. After the info has been pasted into C25, sometimes I may need to edit that cell for one reason or another. C25 is not just a simple phrase or a few words. What I am actually trying to do here is copy large paragraphs from the 2nd sheet over to the first sheet based on the original variable in A25. Think of this as a report. Depending on the variable entered, I want a large amount of characters to be transfered over. I know in previous versions in Excel, it would only transfer over X amount, but now I have upgraded to Excel 2007 so now I am not held to that limit. If I try to edit the info brought over using the vlookup function, I wouldn't be able to edit it unless I did the vlookup and then ran an additional macro that would allow me to copy and then paste/special/values and then I could edit it, but then the formula would be gone.
I'm making a form for users to make their jobs easier and idiot proof. It is just this one thing I can't figure out.
I appreciate the vlookup idea though.

 

[Peter_SSs]

Try this Worksheet_Change event code in a copy of your workbook. To implement:

1. Right click the sheet name tab of 'Worksheet A' and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering values in column B of 'Worksheet A'

<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> BChanged <SPAN style="color:#00007F">As</SPAN> Range, cel <SPAN style="color:#00007F">As</SPAN> Range, aFound <SPAN style="color:#00007F">As</SPAN> Range<br>     <br>    <SPAN style="color:#00007F">Set</SPAN> BChanged = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> BChanged <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">With</SPAN> Sheets("Worksheet B")<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> BChanged<br>                <SPAN style="color:#00007F">If</SPAN> cel.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>                    cel.Offset(, 1).ClearContents<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">Set</SPAN> aFound = .Columns("A").Find(What:=cel.Value, _<br>                        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>                        cel.Offset(, 1).Value = "Not found"<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        cel.Offset(, 1).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>            <SPAN style="color:#00007F">Next</SPAN> cel<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</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 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. 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?

 

[AlphaFrog]

You can run a worksheet function like VLOOKUP from within a macro. This will VLOOKUP the value from the Active cell. It will look in Column A of Sheet2 and return the value from column B. The lookup return will be in the cell one column to the right of the Active cell.

Sub My_VLOOKUP()

If Not IsEmpty(ActiveCell) Then
    Err.Number = 0
    On Error Resume Next
    ActiveCell.Offset(, 1) = WorksheetFunction.VLookup(ActiveCell, Sheets("Sheet2").Range("A:B"), 2, 0)
    If Err.Number <> 0 Then ActiveCell.Offset(, 1) = "No match found"
End If

End Sub

 

[thesnoopster2]

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

 

[AlphaFrog]

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

Is it always going to be ...
Lookup: Worksheet A, cell H351
Array: Worksheet B, A5:B1000
Output: Worksheet A, cell C390

?

 

[thesnoopster2]

yes. The fields will all be static, they will not change.

However, like I said before, I cannot simply just put a VLookup in C390. I need to be able to edit the results from time to time once the macro looks up the info and puts it into C390. If I simply put the function in there and try to edit the field, I would be editting the formula, not the result of the formula.

Specifically, the infomation that is transferring from Worksheet B to Worksheet A is in paragraph form and can range from as few as 10 characters to 1,000 characters.

 

[AlphaFrog]

Maybe this macro will work...

Sub My_VLOOKUP()

    Dim rLookupVal As Range, rOutput As Range, rArray As Range, ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("Sheet1")          'Sheet with Lookup value
    Set ws2 = Sheets("Sheet2")          'Sheet with Lookup array
    Set rLookup = ws1.Range("H351")     'Cell with lookup value
    Set rOutput = ws1.Range("C390")     'Cell to output the lookup result
    Set rArray = ws2.Range("A5:B1000")  'Cells with lookup array


    If Not IsEmpty(rLookup) Then
        If WorksheetFunction.CountIf(ws2.Columns(rArray(1).Column), rLookup) > 0 Then
            rOutput = WorksheetFunction.VLookup(rLookup, rArray, 2, 0)
        Else
            MsgBox "No match found"
        End If
    Else
        MsgBox "Lookup cell " & rLookup.Address & " is empty"
    End If
End Sub