concatenate the currency too

isasa74 · #1 12-11-2015, 08:00 AM

Hi Guys,

this is my first post, so first of all thanks to everybody for the forum

I have a code that I use with great beneficial to compare two different sheets and spot out the differences.
At some point I used a formula to concatenate six cells, now two of them have a currency symbol along with the value (can be either USD or EUR) and I would need the symbol being concatenated as well.

VBA must dynamically be able to read the symbol in the cell and preserving it in the concatenation.
I'm not good with VBA so every suggested solution please have to be very well explained (sorry!)

Here the code:

Code:

Option Explicit
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

Please any help is much much appreciated!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use Two (concatenate) Fields If Condition Is Met (If... Then... Else)	SoonerLater	Mail Merge	1	09-30-2015 05:40 PM
how to concatenate text box on new slide	wichitawx	PowerPoint	5	05-19-2015 05:17 AM
Concatenate with @ symbol	anickles	Excel	2	02-06-2015 09:39 AM
Concatenate if in descending order	pgeorgilas	Excel Programming	2	12-10-2014 01:35 AM
If, move and concatenate in VBA	devcon	Excel Programming	0	07-04-2011 12:44 AM