Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-11-2015, 08:00 AM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default 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
Please any help is much much appreciated!!
Reply With Quote
  #2  
Old 12-12-2015, 03:03 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Can you provide a file for us too look at?
Reply With Quote
  #3  
Old 12-12-2015, 04:51 PM
excelledsoftware excelledsoftware is offline concatenate the currency too Windows 10 concatenate the currency too Office 2007
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

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
Reply With Quote
  #4  
Old 12-12-2015, 05:50 PM
macropod's Avatar
macropod macropod is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,617
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by isasa74 View Post
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.
Unless the cells contain text, the currency symbols are just cell formatting and are not part of the values themselves. That said, you should be able to retrieve the format if you change the way you're populating the array. For example, instead of:
.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]
Reply With Quote
  #5  
Old 12-13-2015, 12:27 PM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
Hi,

Can you provide a file for us too look at?
@ 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.
Attached Files
File Type: xlsm SAMPLE.xlsm (121.8 KB, 10 views)
Reply With Quote
  #6  
Old 12-13-2015, 01:04 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 12-13-2015, 01:22 PM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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,))"
Hi charlesdh,

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
Reply With Quote
  #8  
Old 12-13-2015, 01:31 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

I think it can however I Will not be able to look at until later this "PM".
Reply With Quote
  #9  
Old 12-14-2015, 06:49 AM
Debaser's Avatar
Debaser Debaser is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 196
Debaser will become famous soon enough
Default

Does the data in column H need to be a formula or can it be static?
Reply With Quote
  #10  
Old 12-14-2015, 12:43 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Attached Files
File Type: xlsm SAMPLE (5)-1.xlsm (117.4 KB, 17 views)
Reply With Quote
  #11  
Old 12-14-2015, 01:19 PM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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.

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
Reply With Quote
  #12  
Old 12-14-2015, 01:37 PM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default

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.
Attached Files
File Type: xlsm RAW_TEST_12_October_copy.xlsm (125.7 KB, 7 views)
Reply With Quote
  #13  
Old 12-14-2015, 01:48 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #14  
Old 12-14-2015, 02:12 PM
isasa74 isasa74 is offline concatenate the currency too Windows 7 64bit concatenate the currency too Office 2010 64bit
Novice
concatenate the currency too
 
Join Date: Dec 2015
Posts: 12
isasa74 is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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.
I'm very sorry for the confusion, you don't see differences probably because you run it on the same raw data, try to modify one of the two sheets (better the sheet2) to create "discrepancies" on purpose.

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.
Attached Files
File Type: xlsm RAW_TEST_12_October_copy2.xlsm (131.0 KB, 9 views)

Last edited by isasa74; 12-14-2015 at 02:22 PM. Reason: typo
Reply With Quote
  #15  
Old 12-14-2015, 04:37 PM
charlesdh charlesdh is offline concatenate the currency too Windows 7 32bit concatenate the currency too Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Thanks.

I'll have to check on this tomorrow. Perhaps another will pick up.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
concatenate the currency too 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 the currency too Concatenate with @ symbol anickles Excel 2 02-06-2015 09:39 AM
concatenate the currency too 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft