#1
|
|||
|
|||
concatenate the currency too
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 |
#2
|
|||
|
|||
Hi,
Can you provide a file for us too look at? |
#3
|
|||
|
|||
Correct me if I am wrong but when it comes to numbers in Excel they are just that numbers. Excel allows you to "Format" the number to make it look a certain way but it is still a number. A perfect example is when a cell is formatted as date. If you put in the date 12/12/2015 it will actually be the NUMBER 42350. The same goes for currency if you put in the value $20.00 the cell is still holding the value 20 and then displaying it as $20.00.
With that out of the way I believe you may need to concatenate on a $ to make it a string. Otherwise where you put the value out to on the worksheet you can just ensure the format is in currency and it will show the dollar sign. Again correct me if I am wrong. Thanks |
#4
|
||||
|
||||
Quote:
.Cells(I, 2) to return the value you might use: .Cells(I, 2).Text to return the formatted value.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
@ charlesdh
hi thanks for your help, I uploaded the file with the code and some data as an example. @ excelledsoftware thanks you for the help, unfortunately I cannot simply concatenate a $ or € because they are variables the code must be able to read the format and report it accordingly in the concatenation @ macropod Thanks a lot for your interest, you suggestion is to technical for me, how would you exactly integrate that in the code that I posted please? I didn't write the code myself entirely , I basically only added the two formulas: concatenation and Index/Match. |
#6
|
|||
|
|||
Hi,
Here's a line of code that worked for the "$". You can reformat the rest of the line for the other symbol. I entered a "?" for it. Code:
WkStg = "=IF(ISBLANK(A2),"""",CONCATENATE(A2,"" "",B2,"" "",C2,"" "",""Price"","" "",""$"",D2,"" "",""Freight"","" "",""?"",E2,"" "",""Duties"","" "",F2,))" Last edited by charlesdh; 12-13-2015 at 01:06 PM. Reason: Wrong code entered |
#7
|
|||
|
|||
Quote:
thanks for your help you are very kind, however I think that way I will always have the $ no matter what is the real currency that I have in column D & E. The currency for both columns (Price and Freight) it is not constant, it changes and can be randomly either USD or EUR. So the code should be dynamical and " capable" to read the format in D&E and reporting it accordingly in the concatenation. thanks again |
#8
|
|||
|
|||
Hi,
I think it can however I Will not be able to look at until later this "PM". |
#9
|
||||
|
||||
Does the data in column H need to be a formula or can it be static?
|
#10
|
|||
|
|||
Hi,
I modified your code. Instead of using a "Array" I have the code look in column G for blanks. The I joined the data from column A to E. See code in "Mod1run" for sheet1. I remarked where I made a change. I remarked the code you had. |
#11
|
|||
|
|||
Quote:
Thanks for you precious time charlesdh. It looks like it could work, but the concatenation is wrong. Basically your code (in case of discrepancies between sheet1 and sheee2) is now concatenating the six first columns of sheet1 in column H of the same sheet1. That is pointless, as what is actually needed (when there is no "V") is to have the first sex columns of Sheet2 being concatenated and reported in Sheet1-H for a quick comparison. The check will be on hundred of rows and having that concatenation allows to compare and spot the errors without switching between the two sheets. Do you think we can fix it please? EDIT: another problems I noticed is that before the check was " currency sensitive" it means that thanks to the function at the end of the code (see below the function) the check was able to spot out differences od currency between Sheet1 and Sheet2 , now this function doesn't seem to work any longer. Code:
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 Last edited by isasa74; 12-14-2015 at 01:26 PM. Reason: missing a part |
#12
|
|||
|
|||
I have attached the file with the entire tool, basically you have a step one (three macros unified by the one called "MAIN") that cleans up blank spaces and get rid of weird formats and the second step, the one you already know, that actually compare the two sheets.
then you have a very rudimental Macro that is meant to reset the tool. Maybe it is easier for you if you have the all tool. |
#13
|
|||
|
|||
Ok,
Now you lost me. I ran your code without the modification and ran the modified code. I compared the 2 runs and could not see a difference in the 2 runs. |
#14
|
|||
|
|||
Quote:
My code in absence of the " V" concatenates the columns in Sheet2 and via index/match reports the concatenation to H in sheet1 Yours seems to concatenate the first six columns in sheet1 reporting them in H of the same sheet1 I have uploaded another file, this time you can start from the very raw data, please run the " prepare" button (sub_Main) to have the two sheets prepared for comparison and then the " run check". you will notice that Sheet1 one is very different in the format before the preparation (currency for example are expressed with words USD/EUR rather than with symbols), that is because the two sheets are actually coming from different systems. EDIT attached File was wrong , now attached the correct one sorry hope this helps. Last edited by isasa74; 12-14-2015 at 02:22 PM. Reason: typo |
#15
|
|||
|
|||
Thanks.
I'll have to check on this tomorrow. Perhaps another will pick up. |
|
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 |