concatenate the currency too

charlesdh · #1 12-17-2015, 12:19 PM

Hi,

I hate to say this. But, this has me stumped. I can usually find a answer. But...
That being said I was able to modify the function. It will set the "CurAdj to the correct format. This line of code shows the correct format,

Code:

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

ValD and ValE have the correct format. But, when this is (I think) translated to

"D2" and "E2" in this line of code

Code:

         WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,""  "",B2,""  "",C2,""  "",""Price"",""  "",D2,""  "",""Freight"","" "",E2,""  "",""Duties"","" "",F2,))"

D2 and E2 does not have the correct format.
Unfortunately I'm not familiar with "Scripting.Dictionary". So this is a problem for me.

Here's the Function code that worked for me.

Code:

' This fuction obviously not mine :) is meant to make the check for giving the "V" format sensitive and it works GREAT.
Function CurAdj(WkVal As Range) As String
Dim WkF As String
   WkF = WkVal.NumberFormat
   CurAdj = IIf(InStr(1, WkF, "$") = 1, "$", IIf(InStr(1, WkF, "$€") = 2, "€", "")) & WkVal
End Function

Hopefully another member can help you.

isasa74 · #2 12-19-2015, 04:54 AM

thanks Charles for your help and I must say that until now I couldn't find anyone who can sort it out, it looks simple but it certainly must be not.
thanks again.

isasa74 · #3 12-19-2015, 10:20 AM

I sorted it out with the following formula :

=IF(ISBLANK(A1),"",CONCATENATE(A2," ",B2," ",C2," ","Price"," ",IF(CELL("format",D2)="C2",TEXT(D2,"$0.00"),TEXT( D2,"€ 0.00"))," ","Freight"," ",IF(CELL("format",E2)=",2",TEXT(E2,"€ 0.00"),TEXT(E2,"$ 0.00"))," ","Duties"," ",F2))

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