Index Match with Concatenate in VBA

[Click22]

Hi,

I am trying to create an index match function within VBA.

The reason for this is that I would like to grab the required information and then for it to not update when a worksheet is calculated.

All of the information is in the same workbook.

The below image is from a sheet called "Stock Codes"

The below image is from a sheet called "Full Sheet"

What I am trying to achieve is that when I enter a part number in D:D on "Full Sheet" it will bring the Total on Order amount from "Stock Codes" (S:S) into the Total on Order field on "Full Sheet" (K:K)

I would also like to get the Next Del Qty from "Stock Codes" (T:T) and Next Del Date from "Stock Codes" (U:U) to concatenate into the Next Delivery filed on "Full Sheet" (L:L)

I can do this using index match formulas but as mentioned above, I don't want these values to recalculate. I would like them to be taken at a point in time (when the part number is entered).

I already have this code, which time stamps cell C:C when D:D has info entered in "Full Sheet".

Any help would be very much appreciated.

 

[VBE313]

Assuming "Full Sheet" Last row in column D will be the part number to search for, then please check out the following code

Option Explicit
Sub MrExcelDotCom()
Dim cl As Object, lastRow As Long, strCells As String, partNumber As String, _
totalOnOrder As String, nextDelQty As String
With Sheets("Full Sheet")
    lastRow = .Cells(Rows.Count, 4).End(xlUp).Row
    strCells = "D" & lastRow
    partNumber = Range(strCells).Value
    With Sheets("Stock Codes")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        strCells = "A1:A" & lastRow
        For Each cl In Sheets("Stock Codes").Range(strCells)
            If cl.Value = partNumber Then
                totalOnOrder = cl.Offset(0, 18).Value
                nextDelQty = cl.Offset(0, 19).Value & " " & cl.Offset(0, 20).Value
            End If
        Next cl
    End With
    lastRow = .Cells(Rows.Count, 11).End(xlUp).Row
    .Cells(lastRow, 11).Offset(1, 0).Value = totalOnOrder
    .Cells(lastRow, 12).Offset(1, 0).Value = nextDelQty
    .Columns("K:L").AutoFit
End With
End Sub

 

[steve the fish]

Do you only ever have one row for each product in the sheet "Stock Codes"?

 

[Click22]

Do you only ever have one row for each product in the sheet "Stock Codes"?

Yes there is only 1 instance of each product in the sheet "Stock Codes".

 

[VBE313]

Yes there is only 1 instance of each product in the sheet "Stock Codes".

Does my code help?

 

[Click22]

Assuming "Full Sheet" Last row in column D will be the part number to search for, then please check out the following code

Option Explicit
Sub MrExcelDotCom()
Dim cl As Object, lastRow As Long, strCells As String, partNumber As String, _
totalOnOrder As String, nextDelQty As String
With Sheets("Full Sheet")
    lastRow = .Cells(Rows.Count, 4).End(xlUp).Row
    strCells = "D" & lastRow
    partNumber = Range(strCells).Value
    With Sheets("Stock Codes")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        strCells = "A1:A" & lastRow
        For Each cl In Sheets("Stock Codes").Range(strCells)
            If cl.Value = partNumber Then
                totalOnOrder = cl.Offset(0, 18).Value
                nextDelQty = cl.Offset(0, 19).Value & " " & cl.Offset(0, 20).Value
            End If
        Next cl
    End With
    lastRow = .Cells(Rows.Count, 11).End(xlUp).Row
    .Cells(lastRow, 11).Offset(1, 0).Value = totalOnOrder
    .Cells(lastRow, 12).Offset(1, 0).Value = nextDelQty
    .Columns("K:L").AutoFit
End With
End Sub

Hi, I have pasted this in but it does not seem to be bringing any information through, also no errors are showing either.

 

[Click22]

Assuming "Full Sheet" Last row in column D will be the part number to search for, then please check out the following code

Option Explicit
Sub MrExcelDotCom()
Dim cl As Object, lastRow As Long, strCells As String, partNumber As String, _
totalOnOrder As String, nextDelQty As String
With Sheets("Full Sheet")
    lastRow = .Cells(Rows.Count, 4).End(xlUp).Row
    strCells = "D" & lastRow
    partNumber = Range(strCells).Value
    With Sheets("Stock Codes")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        strCells = "A1:A" & lastRow
        For Each cl In Sheets("Stock Codes").Range(strCells)
            If cl.Value = partNumber Then
                totalOnOrder = cl.Offset(0, 18).Value
                nextDelQty = cl.Offset(0, 19).Value & " " & cl.Offset(0, 20).Value
            End If
        Next cl
    End With
    lastRow = .Cells(Rows.Count, 11).End(xlUp).Row
    .Cells(lastRow, 11).Offset(1, 0).Value = totalOnOrder
    .Cells(lastRow, 12).Offset(1, 0).Value = nextDelQty
    .Columns("K:L").AutoFit
End With
End Sub

The part numbers entered into column D on "Full Sheet" isn't the last row. It is a shared workbook and each user has certain rows they can enter the part number in.

 

[VBE313]

Are you using the code in the same example table and sheets that you referenced?

 

[VBE313]

The part numbers entered into column D on "Full Sheet" isn't the last row. It is a shared workbook and each user has certain rows they can enter the part number in.

So how do you want to search for the part? If i dont grab the last cell in the column D, the code will not know where to grab it.

 

[Click22]

So how do you want to search for the part? If i dont grab the last cell in the column D, the code will not know where to grab it.

I was hoping it would be able to use a similar way as the time stamp above, that when I entered a part number it would trigger the search.

I guess using the With Target statement?

Yes I had posted the code into the same example table I referenced.

Thanks