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]

Please explain to me the exact structure of column C on your JE sheet.

I thought from this section of code here:

    For j = 7 To 447
        If Worksheets("JE").Range("C" & j).Value = "" Then
             Worksheets("JE").Range("C" & j).Value = ActiveCell.Value
             Worksheets("JE").Activate
             Exit For
        End If
     Next j

That you were looking for the first available cell in column C of that sheet. But perhaps you have blocks or "sections" of data.
Can you explain what cells of column C are currently populated, and where exactly you would like the value to be pasted to upon double-clicking?

 

[ave10]

Upon double-clicking a value in column A of the deptlookup sheet, I'd like the value that is being double-clicked to populate the next available row in column C of sheet JE, as well as navigating to sheet JE.

The column C range that is used is C7:C446 and the cells in column C are completely blank. I'm not totally sure what you mean regarding blocks or sections of data but, certain data are located in different sheets. For example, account codes (which are made to populate column C of sheet JE are located in their own sheets called acct_code. Reference codes that will populate column F of sheet JE are located in sheets called Ref1, Ref2, Ref3, etc. These are associated with the last 6 digits of the account codes. So, these might be the "sections" of data you could be referring to.

Although, those different sheets shouldn't really matter, I don't think in this case. For some reason, when I double click the account codes (in sheet deptlookup) that are generated from the search I do, it clicks into the cell to edit the formula but, it does populate cells in the C column in sheet JE, but just at the very bottom of the form I created, starting at cell C448, oddly.

 

[Joe4]

This section of code that I posted:

        Set destRng = Worksheets("JE").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
[COLOR=#ff0000]        MsgBox destRng.Address[/COLOR]

Tells it to go to the last populated cell in column C on the JE sheet, and then move down one row.
So, if it if selecting cell C448, that means that it thinks that cells C447 has something in it (it is NOT blank).

If you manually go to the JE sheet, and go down to cell C1000, and press CTRL and the Up arrow simultaneously, what cell does it go to?

 

[ave10]

Yes, so cell C447 does have text in it. Row 447 is the last row of the "form" that is set up in sheet JE. The cells I need the account codes to populate once double-clicked is the range C7:C446.

When I go to cell C1000 and press both CTRL+upArrow it takes me to cell C447. Again, that is the last row of my "form" think of it as a "Totals" row. In fact, it is indeed, a totals row. The account codes will not go in this row. The last row the account codes can populate is 446. The range in which the account codes can be populated is range C7:C446. So, ideally I'd like the code to start from the top, to start from row 7 and if the next row is available, populate that row with the account code, if not, go down to the next row, and so on. Is that possible?

 

[Joe4]

OK, that makes sense (and that is what I was trying to find out from you - what was in column C).

Make this modification:

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

    Dim nextRow as Long
    Dim destRng As Range

    If Target.Column = 1 Then
        nextRow = Worksheets("JE").Range("C447").End(xlUp).Row + 1
        If nextRow < 7 Then nextRow = 7
        Set destRng = Worksheets("JE").Cells(nextRow, "C")
        destRng.Value = Target.Value
        Sheets("JE").Activate
        destRng.Select
    End If

End Sub

 

[ave10]

Great that worked. Sorry, I should've mentioned the form and total row in sheet JE.

When I double-click on the cells in column A of the deptlookup sheet, it still enters into the cell to edit the formula. How can I avoid this while also navigating back to the main form in sheet JE upon double-clicking a value in column A?

I personally don't mind if when I click in the cells in column A if it enters the cell and edits the formula but, other people are going to use this workbook and I would rather, upon double-clicking the values, it take the value being double-clicked and just immediately navigates back to sheet JE.

 

[Joe4]

The last line in the IF statement should take care of that:

destRng.Select

This jumps to/selects the range that the value is being placed in on the JE sheet.