VBA to keep format when concatenating

Hi Guys,

This is my first post in this Forum so I first would like to thanks all of you for the given opportunity.

I need help with a code , that I actually did myself only partially because I'm a very bad beginner with VBA.

At some point of the code I applied a formula to concatenate A:B:C;D:E:F in Sheet2, and in Sheet1 I applied an Index /Match formula to have visualized that concatenation in Sheet1 column J.

Now what I really would need to complete this tool is having the format ( [$€-2] #,##0.00 or $#,##0.00) of Column D & F be added to the concatenation.
I've read a couple of thread about this but I couldn't find a way to adapt the suggested solution to my code, for my poor skill and also because it's not that easy since the entire code must not be messed up by this additional function.

Below the part of the code with the concatenation Formula and then also the entire code:

Code:

---

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

---

Entire code

Code:

---

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
'=====
  With WSh1
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
'---
      With Range(.Cells(2, 7), .Cells(LR, 7))
        .FillDown
        .ClearContents
      End With
'---
      For I = 2 To LR
        ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
        If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/"))) Then
            .Cells(I, 7) = "V"
        End If
      Next I
'---
      With Range(.Cells(2, 8), .Cells(LR, 8))
        .ClearContents
        WkStg = "=IF(ISBLANK(RC[-7]),"""",IF(RC[-1]<>""V"",INDEX(Sheet2!C1:C7,MATCH(1,(RC[-7]=Sheet2!C1)*(RC[-6]=Sheet2!C2)*(RC[-5]=Sheet2!C3),0),7),""""))"
        .Cells(1, 1).FormulaArray = WkStg
        .FillDown
      End With
  End With
  Application.ScreenUpdating = True
 

End Sub
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

---

Code:

---

msgbox Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 4).text, ValD, ValE, .Cells(I, 6).text)

---

Quote:

---

Originally Posted by snb

Code:

---

msgbox Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 4).text, ValD, ValE, .Cells(I, 6).text)

---

---

Hi VBAX Guru,

Thanks a lot for helping me out :)

your string must be added where? and should replace what please?
THANKS again...

What was your original question ?

Quote:

---

Originally Posted by snb

What was your original question ?

---

My question was

"At some point of the code I applied a formula to concatenate A:B:C;D:E:F in Sheet2, and in Sheet1 I applied an Index /Match formula to have visualized that concatenation in Sheet1 column J.

Now what I really would need to complete this tool is having the format ( [$€-2] #,##0.00 or $#,##0.00) of Column D & F be added to the concatenation.
I've read a couple of thread about this but I couldn't find a way to adapt the suggested solution to my code, for my poor skill and also because it's not that easy since the entire code must not be messed up by this additional function.
"

then you suggested:

Code:

---

msgbox Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 4).text, ValD, ValE, .Cells(I, 6).text)

---

And now I'm asking how and where to insert the line suggested by you in the code please

everywhere where you need it.

For your purposes
ValD = .Cells(I, 4).Text

Therefore
ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/")) = Empty

Can be
ObjDic.Item(.Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)) = True

And
If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/"))) Then
.Cells(I, 7) = "V"
End If

Can Be
If ObjDic(.Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)) Then .Cells(I, 7) = "V"

My personal preference would be
TestString = Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)
ObjDic.Item(TestString) = True

But that is just me, I like to Uber_Comment my code with named variables.

Quote:

---

Originally Posted by snb

everywhere where you need it.

---

thanks a lot for your interest and help :) , but actually to integrate my code with the line you suggested is too complex for me...

Quote:

---

Originally Posted by SamT

For your purposes
ValD = .Cells(I, 4).Text

Therefore
ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/")) = Empty

Can be
ObjDic.Item(.Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)) = True

And
If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/"))) Then
.Cells(I, 7) = "V"
End If

Can Be
If ObjDic(.Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)) Then .Cells(I, 7) = "V"

My personal preference would be
TestString = Cells(I, 1) & .Cells(I, 2) & .Cells(I, 3) & .Cells(I, 4).Text & .Cells(I, 5).Text & .Cells(I, 6)
ObjDic.Item(TestString) = True

But that is just me, I like to Uber_Comment my code with named variables.

---

Hi SamT

you are a genius and I'm almost embarrassed to bother you guys, sorry...
but in my code where to put:

ValD = .Cells(I, 4).Text? please?
(just to start)

Quote:

---

but in my code where to put:

ValD = .Cells(I, 4).Text? please?
(just to start)

---

I see that very little of "your" code is actually yours. That's OK, it is just that from the code itself, it looked like you had quite a bit of experience with VBA.

What I meant was that even though ValD = CurAdj(blah, blah) is not the same as .Cells(I, 4).Text, For the purpose of differentiating between $$1.23 and $€1.23, Cells(r, c).Text would work.

So... You probably could use Cells(r, c),Text instead of ValD and ValE and not need the function "CurAdj."

I have to say "probably" because we don't know all the nuances of all your code and of your workbook. For Example, none of that code changes the actual appearance of $$ or $€ in any Worksheet. Is that important or not? Why are individual cells in a column formatted differently? Does anybody care?


BTW, it is considered bad practice to use UCase I (EYE) and LCase l (ell) because, depending on the font I (EYE), l (ell), and 1 (one) can appear identical.

LCase i and UCase L are almost always significantly different in appearance. ( i, L, 1)

As to where to put snb's code? Look at what terms it is using. Look at your code and find where the same terms are used. Ignore the fact that he "accidentally" used a 4 instead of a 3 in one place. BTW, I sometimes have the same type of accident.


I know that you are using a copy of the workbook to play with, but use two copies. Make small changes in the second and only when they test out add them to the first copy. You can make so many changes in the second trying to get somebody's suggestion to compile that you forget where you were, the first copy lets you start over from the last successful change.

Hi SamT you are very kind and patient thanks a lot for you time and sorry if it can be hard to explain VBA to me...

Quote:

---

I see that very little of "your" code is actually yours. That's OK, it is just that from the code itself, it looked like you had quite a bit of experience with VBA.

---

Yes sorry I thought I said it wasn't mine, but I didn't, a very nice person in a forum helped me with that. I have only very basic notions,just started a few weeks ago.

Quote:

---

What I meant was that even though ValD = CurAdj(blah, blah) is not the same as .Cells(I, 4).Text, For the purpose of differentiating between $$1.23 and $€1.23, Cells(r, c).Text would work.

So... You probably could use Cells(r, c),Text instead of ValD and ValE and not need the function "CurAdj."

---

I need to have a look at this and I think I can try to understand that.

Quote:

---

I have to say "probably" because we don't know all the nuances of all your code and of your workbook. For Example, none of that code changes the actual appearance of $$ or $€ in any Worksheet. Is that important or not? Why are individual cells in a column formatted differently? Does anybody care?

---

I have another macro that check all the formats, get rid of trailing spaces etc etc. ,
I'm bulding a two steps tool to check if all prices and tax are correct.
Currency is very important as not all the tax are paid in the same way.

Quote:

---

As to where to put snb's code? Look at what terms it is using. Look at your code and find where the same terms are used. Ignore the fact that he "accidentally" used a 4 instead of a 3 in one place. BTW, I sometimes have the same type of accident.

---

I tried to replace snb's line in my code but nothing happened, the concatanation was still with no format. (I try to attach the result but yesterday the site denied me)

Quote:

---

I know that you are using a copy of the workbook to play with, but use two copies. Make small changes in the second and only when they test out add them to the first copy. You can make so many changes in the second trying to get somebody's suggestion to compile that you forget where you were, the first copy lets you start over from the last successful change.

---

I'll do that thanks!, the thing is that I'm almost there, after a very long job, and what I need is just to have the currency format in the concatenation, this concatenation will be then reported in sheet1 by my formula index/match, and so I can easily check why the two rows in sheet1 and 2 are not matching..

Quote:

---

I need is just to have the currency format in the concatenation, this concatenation will be then reported in sheet1 by my formula index/match

---

Nowhere in your code is the Concatenated string place in a cell. Neither is any cell that is being Concatenated checked to see if there are any currency sign in the value of the cell. IOW, any currency signs on the Worksheets are ignored in the above code.

Quote:

---

so I can easily check why the two rows in sheet1 and 2 are not matching..

---

That is not what your code above is doing. The code above is checking each row of sheet1 to see if it randomly matches any row of sheet2. IOW, If Row 999 matches sheet2 Row 77, then Sheet1 G999 will have a "V" in it.

About NumberFormat: Right Click a cell in Copy # 2 and select Format Cells from the pop up menu. that will display the Format Cells DialoBox. Click on the Number Tab. That displays all possible NumberFormats.

Select Currency. Click the down arrow in the Symbol ListBox. There are no "$$" or "$€" available. So how is it possible for the function to find them?

Select Custom. You can type a custom NumberFormat into the "Type" TextBox, but someone would have had to enter a custom NumberFormat with two Currency symbols on purpose in order for the Function CurAdj to find them.

On the 2nd Workbook copy, select a cell with a currency symbol. Copy it and in another cell Paste Special: Values. Do you still see a currency symbol? NumberFormats do not effect the Value of a cell. They do effect the Text. So... Cell.Value only has a currency symbol if the symbol was entered into the cell. Cell.Text has the symbol even if it is NumberFormatted to have a symbol.

Do you have any cells displaying two currency symbols at once?

Quote:

---

so I can easily check why the two rows in sheet1 and 2 are not matching..

---

That is not what your code above is doing. The code above is checking each row of sheet1 to see if it randomly matches any row of sheet2. IOW, If Row 999 matches sheet2 Row 77, then Sheet1 G999 will all have a "V" in them.

There might be a better way to check for errors. Please upload a workbook you we can understand the nuances of the sheets. Please delete all but a dozen or so Rows in each sheet, Some good Rows, but be sure and leave some Rows that don't match. One for each type of mismatch, please.

Use the Go Advanced Option and scroll down to see the Manage Attachments button.

Hi SamT thanks again and again to follow up with this...I give you a bit of background, Sheet 1 is what we actually have in the system, sheet 2 is what we SHOULD have.
So comparing the two sheets I can find what to adjust in the system, this adjustments include Currencies as we sometimes pay in USD and sometimes in EUR (depending on the region).

Quote:

---

Nowhere in your code is the Concatenated string place in a cell. Neither is any cell that is being Concatenated checked to see if there are any currency sign in the value of the cell. IOW, any currency signs on the Worksheets are ignored in the above code. That is not what your code above is doing. The code above is checking each row of sheet1 to see if it randomly matches any row of sheet2. IOW, If Row 999 matches sheet2 Row 77, then Sheet1 G999 will have a "V" in it.

---

I know the code makes that check, and when an entire row in Sheet1 is matching entirely a row in Sheet2 it gives back the "V" in Sheet1 column J, but in the code I added two formulas too, please see below:

Code:

---

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

---

Code:

---

WkStg = "=IF(ISBLANK(RC[-7]),"""",IF(RC[-1]<>""V"",INDEX(Sheet2!C1:C7,MATCH(1,(RC[-7]=Sheet2!C1)*(RC[-6]=Sheet2!C2)*(RC[-5]=Sheet2!C3),0),7),""""))"

---

Those two formulas are kicked in when you DON'T have the " V" .
Respectively the first formula concatenates the cells from A:F in Sheet2 and the second match one of those concatenated rows with a row in Sheet1 that has got the same values in the first three cells.

That is on the purpose of easily find out WHY the sheet1 and 2 are not matching and what to correct in the system, in the file I have sent you there are no formulas because as you can read in the code at some point I get rid of the formulas copying and paste only values , that way the worksheet is much more lighter and you can work with filters ( sometimes I have more than 500 hundred rows).


Now..
what I would really need is that the concatenation of the rows in sheet 2 could maintain the format you have in column D & E in sheet2 (basically those two currency can only be either EUR or USD), if please look at the file I've attached you will see that the concatenation has been reported by index and match from sheet 2 to 1 for an easy check, but unfortunately I need also the currencies.
Please tell me if until here I managed to be clear...:)

Quote:

---

Do you have any cells displaying two currency symbols at once?

---

No I don't, only one currency for each cell.

Quote:

---

Please upload a workbook you we can understand the nuances of the sheets. Please delete all but a dozen or so Rows in each sheet, Some good Rows, but be sure and leave some Rows that don't match. One for each type of mismatch, please.

Use the Go Advanced Option and scroll down to see the Manage Attachments button.

---

I have already uploaded a file, is not visible to you? it was in my latest post before this one, please check so I know if I did it well,
I've now uploaded two files the workbook (the tool) and my personal file (otherwise you cannot run the tool, as far as I know).
The tool is loaded and with some errors too, you must simply:

• run the first macro pressing " PREPARE" button
• and then the check itself with the "START" button

THANKS THANKS :)

Before I check out your file

Code:

---

(basically those two currency can only be either EUR or USD)

---

Did you know that those two currency symbols, (EUR and USD,) are different from $ and €, but all four are available from the Format Cells DialogBox.

And I see that you are, in fact, using all four of the symbols. The Abbreviation on sheet1 and the symbol on sheet2

A main requirement for coders is specificity.

Quote:

---

Sheet 1 is what we actually have in the system, sheet 2 is what we SHOULD have.

---

I would much rather write code to determine which currency to pay with from sheet1 than sheet 2 because sheet1 has an actual Cell Value to work with in columns E & G, where in sheet 2 I have to look at the cell's NumberFormat to tell. But I would have to use Sheet1 in the first place to know what NumberFormat to set sheet 2 to. IOW, double your work for no benefit.

Quote:

---

we sometimes pay in USD and sometimes in EUR (depending on the region).

---

I assume you mean the region the Vendor is in or the region the Plant is in. So why not use a Vendor/Plant list?

If you must use Sheet2, then I suggest merely using columns E & G from sheet1 to add the actual symbol to the Cell's value and not use any NumberFormat on the Cells. If this is done when transferring data to sheet 1, it would not be hard.

As to the code above. It won't work. Ever. There is no Currency NumberFormat in Sheet1, Columns D to G for Function CurAdj to work with. BTW, I looked at the Currency Function test on hidden sheet 4 , but could not make sense of the notes.

Why don't we go back to the two images on the INSTRUCTIONS sheet and work on Importing the data from "List info records" into Sheet2 in a format you can work with. If there is no Currency type listed in Columns PB00curr and/or ZFR1curr, then we can highlight those rows (on both sheets.) That is a pretty straight forward procedure. I strongly suggest that you use the Currency symbols ($ or USD) as prefixes to the amount rather than formatting the cells. If you need to sum the cells later, then you really, really, REALLY should keep the currency columns.

As you can tell. I am still not clear on all the nuances of the problem.

I looked over your PERSONAL.xlsb and you have inherited quite a mess. Duplicate subs, Macros mixed in with Procedures, 70 Modules with less than half actually containing any code, an empty UserForm, no modules with significant Names. And as far as I can see, absolutely none of which should even be in a PERSONAL.xlsb workbook since all of it appears to only apply to the current Project under discussion in this thread.

PERSONAL.xlsb is named that because it is personal, you can't share it around the office, you can't use one that someone else emailed to you or attached to a post on VBAExpress. It can only be used on one computer, because all computers with Excel probably have their own PERSONAL.xlsb.

PERSONAL.xlsb is a unique Type of workbook and simply copying it merely creates a new Workbook of the same Type with a new name. I have attached a workbook of the non-PERSONAL Type with all the code from your PERSONAL.xlsb.

I "Removed" all empty Modules and Renamed some to indicate the type of procedure they contain, then moved some Procedures into those significantly named modules. I even did a little cleaning on a couple of Macros in preparation for turning them into proper Procedures. ( a Macro is recorded and has a tremendous amount of redundant and unnecessary steps in it. A procedure is well constructed and only takes the minimum steps to accomplish the task. Your code above is a Procedure and the code in Module20 is a Macro.) BTW, there is no sheet ("Ultimate") for the Macro in Module20 for the code to work on.

The first thing to do is open Excel from the start menu, which will open PERSONAL.xlsb .Delete all sheets except sheet1, then clear all contents and formatting from sheet1. Hide PERSONAL.xlsb.

In the VBA editor, right click all modules and the UserForm and "Remove" every one. then open the workbook I attached and in the VBA Editor, Left drag mod_Utilities into the PERSONAL.xlsb space. You will need to use the View Menu to insure that the Project Explorer is open, that is where you Remove and Drag modules.

Use the Tools >> Options Menu and in the "Editor" Tab of the DialogBox, Check all the CheckBoxes in the "Code Settings" Frame. On the "General" Tab, "Error Trapping" Frame, check the "Break on All Errors" CheckBox. Check both CheckBoxes in the "Compile" Frame.

Note the underscore in the name mod_Utilities. That places it at the top of the list of Modules. mod_Utilities is only for those Procedures that are used quite often in almost every Project and Workbook you create, such as GetLastRow, GetLastColumn, TrimStringsInCells, StripBlanks, etc. Instead of rewriting those little subs every time, just drag the Module into the new Workbook.

Remind me sometime to explain how I use my own Personal.xls. it is 276 KB in size but only 5 subs are even accessible from the Tools >> Macros menu in Excel and 4 of them are less than 8 lines long.

Back to the Attachment:

The author(s) of all that code did not indicate the purpose of any of the Subs and Macros. (see 1st comment line in Sub GetLastRow in Module mod_Utilities.) First you need to fix that problem, then organize all procedures according to their purpose and give the Modules significant names as I started for you. Delete or rename any subs with duplicate names, then delete all subs and Macros that are not used in your project. Remove any empty Modules. Add the line "Option Explicit" to the very top of all modules. See if you can convert all Macros into Procedures. Don't be afraid to ask how, it is a great VBA learning experience.

About the personal file unfortunately it is MINE :) it is personal and mine of course I didn't all the macros in it , kind people like you helped me and olny some I did myself..

sorry It is confused I know, but you only need it to run the tool , ok then let's do this I will post only the three Macros that you need to run the tool ok? :) please find the three codes in my next post :)

[QUOTE=SamT;331669]Before I check out your file

Code:

---

(basically those two currency can only be either EUR or USD)

---

Quote:

---

Did you know that those two currency symbols, (EUR and USD,) are different from $ and €, but all four are available from the Format Cells DialogBox.

And I see that you are, in fact, using all four of the symbols. The Abbreviation on sheet1 and the symbol on sheet2

A main requirement for coders is specificity.

---

Hi SamT thanks for your help once more :)

The first macro of my tool , the one assigned to the green button "PREPARE" actually does:

• Read the abbreviations in Sheet1 Coloumn E & G formatting D & F accordingly; the two Formats used are $#,##0.00 (replace Abbr.USD) & [$€-2] #,##0.00 (replace Abbr. EUR).

• Get rid of the two colomns with the abbreviations (USD/EUR) in Sheet1

​

• Replace the Accounting formats in Sheet2 with $#,##0.00 (if the accounting format was in USD) and with [$€-2] #,##0.00 (if the accounting format was in EUR).

• Get rid of all the possible blank spaces after the part numbers in sheet2 (sheet1 comign out from the system doesn't have blanks spaces after the P/N).

So after using the PREPARE button the two sheets are ready to be compared also Format wise (as the only two formats, after the first macro, are now $#,##0.00 & [$€-2] #,##0.00).

and in the main code (the macro assigned to the red button RUN CHECK) you have the below futcion that can compare the formats along with the values to give the"V" only if EVERYTHING is acutally matching:

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

---

Quote:

---

I would much rather write code to determine which currency to pay with from sheet1 than sheet 2 because sheet1 has an actual Cell Value to work with in columns E & G, where in sheet 2 I have to look at the cell's NumberFormat to tell. But I would have to use Sheet1 in the first place to know what NumberFormat to set sheet 2 to. IOW, double your work for no benefit.

---

I cannot sue Sheet1 as reference because the contentof that it is what I need to check out,No need of that, please read above :)

Quote:

---

As to the code above. It won't work. Ever. There is no Currency NumberFormat in Sheet1, Columns D to G for Function CurAdj to work with. BTW, I looked at the Currency Function test on hidden sheet 4 , but could not make sense of the notes.

---

Please read above my first comment :)

Quote:

---

Why don't we go back to the two images on the INSTRUCTIONS sheet and work on Importing the data from "List info records" into Sheet2 in a format you can work with. If there is no Currency type listed in Columns PB00curr and/or ZFR1curr, then we can highlight those rows (on both sheets.) That is a pretty straight forward procedure. I strongly suggest that you use the Currency symbols ($ or USD) as prefixes to the amount rather than formatting the cells. If you need to sum the cells later, then you really, really, REALLY should keep the currency columns.

---

I would need as format unfortunately, it is much better for what I have to do after. all the tool is working very well I only need a way to report the format in the concatenation...if you can do this miracle you are my hero :)

Quote:

---

As you can tell. I am still not clear on all the nuances of the problem.

---

I hope it is clearer now...Please don't understimate what done till now the all thing works fine :), run my tool using the two Buttons and you will see how it works in a blink of an eye :), no risk for you I'm not an hacker ahahah the other way around I'm too bad with computers..

BTW if you want to avoid my personal file , maybe I can send all the three codes and you can assign them to the buttons using your own personal file? let me know please and THANKS

Hi SamT,

Thanks again :), I'm doing all you suggested to clean up my Personal (takes a bit for me hopefully I'll be done tomorrow), for now:



Here you are attached a simplified Personal File, with the only three macros related to the tool, plus the TESTv3 file which is the let's say "interface" of the tool.
please:

1. Assign Sub PREPARE_Button() to PREPARE Button in sheet1
2. Assign Sub RUN_CHECK() to RUN CHECK button in sheet1
3. Assign Sub RESET_Tool() to RESET button in sheet1

Run the tool

1. Press Prepare button
2. Press Run Check Button

you will have two errors for formats nto matching in the first two rows of sheet1, and two errors for the lack of Import Duties in row 3-4.

@isasa

Please stop this project and start learning the basics of VBA first.
Adapt you ambitions to your abilities and strive to match the latter to the former.

You can't communicate in a language (VBA) if you do not even master the very basics.

Have you read my Signature block at the bottom of my post? Excel XP wont even show the buttons in your Workbook.

Quote:

---

And as far as I can see, absolutely none of which should even be in a PERSONAL.xlsb workbook since all of it appears to only apply to the current Project under discussion in this thread.

PERSONAL.xlsb is named that because it is personal, you can't share it around the office, you can't use one that someone else emailed to you or attached to a post on VBAExpress. It can only be used on one computer, because all computers with Excel probably have their own PERSONAL.xlsb.

---

There is a very good reason I made the effort and spent hours to create a NON_ PERSONAL type workbook for you and it wasn't for my health. If you reject that, I will no longer help you.

Quote:

---

Adapt you ambitions to your abilities and strive to match the latter to the former.

You can't communicate in a language (VBA) if you do not even master the very basics.

---

@SamT

Sorry for the mistake: 'you' instead of 'your'