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!!