Using VLOOKUP to list values that contain a smaller value

[ave10]

I have account codes that look like this #######-###-##-######. After the first (-) ###, is the department code.
In sheet "acct_codes" all of the full account codes are listed and in sheet "dept_list" all of the departments are listed.

I have a sheet called "lookup", in cell B2 of that sheet the user can enter a department code in cell B2 or double-click a department code in the "dept_list" sheet and it'll populate cell B2. And click a "search" button. Clicking that search button will trigger a macro taking the user to a separate sheet called "deptlookup". I would like for all of the account codes that have the department code being searched for, to be listed in Column A of the "deptlookup" sheet. So, if the user enters '001' in cell B2 and clicks search button, it will take them to the sheet 'deptlookup' and in column A it will list all of the account codes that have the department code '001' inside of it. I tried using a VLOOKUP for it but I believe the logic in my formula is off:

=IF(lookup!B2=MID(acct_codes!A:A,9,3),VLOOKUP(acct_codes!A:A=MID(acct_codes!A:A,9,3),acct_codes!A:A,1,FALSE), "")

If anyone can help me with this VLOOKUP formula or knows of an easier way to do this, I would be very grateful for any help. Thanks!

 

[Joe4]

When I double click the cells on the A column, the cursor just clicks inside the cell to edit it

Mine does that too, but still updates the first available cell in column C on the JE sheet (check it to see if this is the fact).
Note that it does not physically "navigate" to the sheet (you don't need to go to that sheet to update a cell on that sheet).

 

[ave10]

I just checked it and unfortunately it does not do that. I'd really like it to double-click and populate the first available cell in column C of the JE sheet as well as navigate back to the JE sheet.

Do you know of any adjustments I could make? Thanks again!

 

[Joe4]

This works for me:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim destRng As Range

    If Target.Column = 1 Then
        Set destRng = Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        destRng.Value = Target.Value
        Sheets("JE").Activate
        destRng.Select
    End If

End Sub

 

[ave10]

It looks like it's about to double-click but it gives me an error: "Run-time error '1004': Selection method of Range class failed"

It then highlights the code like this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim destRng As Range


    If Target.Column = 1 Then
        Set destRng = Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        destRng.Value = Target.Value
        Sheets("JE").Activate
[B]        destRng.Select [/B]'This  seems to be the issue 
    End If


End Sub

Thanks!

 

[Joe4]

Does it update the value though?
Is there anything special about this range (merged, protection, hidden rows, etc)?

It works fine for me, so I am guessing that there might be something "special" about your format that we don't know about.

 

[ave10]

The only thing special about this range is that the first two rows are hidden.

The values derived in Column A (where I'm double-clicking) are derived from a different sheet and the formula is:
=IF(ROWS($A$4:A4)>A$3,"",INDEX(acct_codes!$A$2:$A$20681,SMALL(IF(ISNUMBER(SEARCH("-"&A$2&"-",acct_codes!$A$2:$A$20681)),ROW(acct_codes!$A$2:$A$20681)-ROW(acct_codes!$A$2)+1),ROWS($A$4:A4))))

Other than that, the value is not updated on the JE sheet and there is no other protection or merging.

Even though, with the first two rows unhidden, I am still unable to double-click the values in column A.

In my previous response, the destRng.Select was highlighted as the issue but its back to just clicking inside the cell to modify the formual rather than populating column C of the JE sheet as well as going back to the main sheet, JE.

Do you know of any modifications I could make? Thanks again!

 

[Joe4]

I added a Message Box to the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim destRng As Range


    If Target.Column = 1 Then
        Set destRng = Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
[COLOR=#ff0000]        MsgBox destRng.Address[/COLOR]
        destRng.Value = Target.Value
        Sheets("JE").Activate
[B]        destRng.Select [/B]'This  seems to be the issue 
    End If


End Sub

Can you tell me what the Message Box returns?

 

[ave10]

Thanks for that. So, when I double-click on the first value it returns $C$466, the second value shows $C$467, and so on. The values are being posted in the JE sheet but outside of the "form" I have established. the range I need these values to be populated in is C7:C446. That mus be why, right?

 

[Joe4]

It sounds like you already have something in cells C466.
Formula, perhaps?

 

[ave10]

Actually, when double-clicking the values in column A in the deptlookup sheet, the values start populating cell C448. There are values and no formulas in all of column C in sheet JE.