Getting stuck converting CONCAT formula and extra

[doumob]

I am getting stuck converting a formula to use in a macro and finishing off the macro

=CONCAT(TEXT(VLOOKUP(B25,'Sheet2'!A2:C33,3,FALSE),"0000")&VLOOKUP(B25,'Sheet2'!A2:C33,2,0))"

Above is the formula I have got working so the cell B25 is populated with a 4 digit code (0 leaders) followed by letters

I cannot get this to work in a macro.

After that I am trying to get the same macro to add 1 to the value of the correct row of Sheet 2 C column for which the value of B25 matches.

e.g

B25 = gold
On Sheet 2 C1 = Gold and C3 = 0004 I would like C3 to equal 0005 after I run the macro.

 

[Fuji]

Perhaps

Sub test()
    Dim a, i&, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("sheet2").[a1].CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If a(i, 1) <> "" Then dic(a(i, 1)) = Join(Array(Format$(Val(a(i, 3)) + 1, "0000"), a(i, 2)), "")
    Next
    With Sheets("sheet1")  '<--- alter to suite
        With .Range("a2", .Range("a" & Rows.Count).End(xlUp))
            a = .Value
            For i = 1 To UBound(a, 1)
                a(i, 1) = dic(a(i, 1))
            Next
            .Columns(2).Value = a
        End With
    End With
End Sub

 

[doumob]

I have tried that, it gives a runtime error 13. Mismatch.

 

[Fuji]

Then the data layouts or data itself is different from what I thought, so bad luck.

 

[doumob]

Team	Team Code	Number
Red North	RN	0005
Red South	RS	0575
Gold North	GN	0035
Gold South	GS	0006
Yellow North	YN	0102

Sorry if I have not explained this suitably, and thank you for replying.

The sheet 2 table is like the one above

Then I have a cell "b25" on sheet 1 that I have made a data validation drop down list for to select a team from. Then I need a code to be written in another cell (say B13 on sheet 1), this input needs to be the corresponding team code and the Number to create a unique reference. So in the example above if B25 was Gold North then the macro (button) needs to input GN0035 into Cell B13, then the number in the table on sheet 2 needs to increase to 0036 so if the macro is run again the next code generated will be GN0036 and so on. If I then change B25 to Red South and run the macro the code will be RS0575.

 

[Alex Blakenburg]

Is the number in sheet 2 a number formatted as "0000" or Text ?
If its a number you can try something like this:

Sub GetMatch()
    Dim wsDest As Worksheet, wsSrc As Worksheet
    Dim rngSrc As Range
    Dim matchRow As Long, matchResult As String
    
    Set wsDest = Worksheets("Sheet1")
    Set wsSrc = Worksheets("Sheet2")
    
    Set rngSrc = wsSrc.Range("A1:C33")
    
    With Application
        matchRow = .IfError(.Match(wsDest.Range("B25"), rngSrc.Columns(1), 0), 0)
    End With
    
    If matchRow <> 0 Then
        With rngSrc
            matchResult = .Cells(matchRow, "B").Value & Format(.Cells(matchRow, "C").Value, "0000")
            .Cells(matchRow, "C").Value = .Cells(matchRow, "C").Value + 1
        End With
        wsDest.Range("B13").Value = matchResult
    End If

End Sub

 

[Fuji]

OK, I was not even close.
To the sheet code module that you have data validation in B25.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Address(0, 0) <> "B25" Then Exit Sub
    Application.EnableEvents = False
    Target(, 2).ClearContents
    Set r = Sheets("sheet2").Columns(1).Find(Target, , , 1)
    If Not r Is Nothing Then
        Target(, 2) = r(, 2) & Format$(r(, 3), "0000")
        r(, 3) = r(, 3) + 1
    End If
    Application.EnableEvents = True
End Sub

 

[doumob]

Is the number in sheet 2 a number formatted as "0000" or Text ?
If its a number you can try something like this:

Sub GetMatch()
    Dim wsDest As Worksheet, wsSrc As Worksheet
    Dim rngSrc As Range
    Dim matchRow As Long, matchResult As String
   
    Set wsDest = Worksheets("Sheet1")
    Set wsSrc = Worksheets("Sheet2")
   
    Set rngSrc = wsSrc.Range("A1:C33")
   
    With Application
        matchRow = .IfError(.Match(wsDest.Range("B25"), rngSrc.Columns(1), 0), 0)
    End With
   
    If matchRow <> 0 Then
        With rngSrc
            matchResult = .Cells(matchRow, "B").Value & Format(.Cells(matchRow, "C").Value, "0000")
            .Cells(matchRow, "C").Value = .Cells(matchRow, "C").Value + 1
        End With
        wsDest.Range("B13").Value = matchResult
    End If

End Sub

Thank you that works.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.