VBA to keep format when concatenating

[isasa74]

Hi Guys,

This is my first post in this Forum so I first would like to thanks all of you for the given opportunity.

I need help with a code , that I actually did myself only partially because I'm a very bad beginner with VBA.

At some point of the code I applied a formula to concatenate A:B:C;D:E:F in Sheet2, and in Sheet1 I applied an Index /Match formula to have visualized that concatenation in Sheet1 column J.

Now what I really would need to complete this tool is having the format ( [$€-2] #,##0.00 or $#,##0.00) of Column D & F be added to the concatenation.
I've read a couple of thread about this but I couldn't find a way to adapt the suggested solution to my code, for my poor skill and also because it's not that easy since the entire code must not be messed up by this additional function.

Below the part of the code with the concatenation Formula and then also the entire code:

 With Range(.Cells(2, 7), .Cells(LR, 7))
         .ClearContents
         WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,""  "",B2,""  "",C2,""  "",""Price"",""  "",D2,""  "",""Freight"","" "",E2,""  "",""Duties"","" "",F2,))"
         .Cells(1, 1).Formula = WkStg
         .FillDown
      End With

Entire code

Sub Treat_Currency_Formules()
Application.ScreenUpdating = False
Dim ObjDic   As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim LR  As Long
Dim WSh1  As Worksheet, WSh2  As Worksheet
Dim I As Long
Dim CheckChar As String
Dim ValD, ValE
Dim WkStg As String
   Set WSh2 = Worksheets("Sheet2")
   Set WSh1 = Worksheets("Sheet1")
   CheckChar = "v"
   
   Application.ScreenUpdating = False
   With WSh2
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
'---
      For I = 2 To LR
         ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
         ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/")) = Empty
      Next I
'---
      With Range(.Cells(2, 7), .Cells(LR, 7))
         .ClearContents
         WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,""  "",B2,""  "",C2,""  "",""Price"",""  "",D2,""  "",""Freight"","" "",E2,""  "",""Duties"","" "",F2,))"
         .Cells(1, 1).Formula = WkStg
         .FillDown
      End With
   End With
'=====
   With WSh1
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
'---
      With Range(.Cells(2, 7), .Cells(LR, 7))
         .FillDown
         .ClearContents
      End With
'---
      For I = 2 To LR
         ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
         If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/"))) Then
            .Cells(I, 7) = "V"
         End If
      Next I
'---
      With Range(.Cells(2, 8), .Cells(LR, 8))
         .ClearContents
         WkStg = "=IF(ISBLANK(RC[-7]),"""",IF(RC[-1]<>""V"",INDEX(Sheet2!C1:C7,MATCH(1,(RC[-7]=Sheet2!C1)*(RC[-6]=Sheet2!C2)*(RC[-5]=Sheet2!C3),0),7),""""))"
         .Cells(1, 1).FormulaArray = WkStg
         .FillDown
      End With
   End With
   Application.ScreenUpdating = True
   

End Sub
Function CurAdj(WkVal As Range) As String
Dim WkF As String
   WkF = WkVal.NumberFormat
   CurAdj = IIf(InStr(1, WkF, "$$") <> 0, "$", IIf(InStr(1, WkF, "$€") <> 0, "€", "")) & WkVal
End Function