[SOLVED:] VBA to keep format when concatenating [Archive]

isasa74

10-04-2015, 03:40 AM

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:

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

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

snb

10-04-2015, 06:05 AM

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

isasa74

10-04-2015, 06:19 AM

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

snb

10-04-2015, 07:54 AM

What was your original question ?

isasa74

10-04-2015, 08:08 AM

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:

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

snb

10-04-2015, 08:21 AM

everywhere where you need it.

SamT

10-04-2015, 10:11 AM

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.

isasa74

10-04-2015, 10:11 AM

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

isasa74

10-04-2015, 11:05 AM

SamT

10-04-2015, 03:39 PM

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.

isasa74

10-05-2015, 12:46 AM

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

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.

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.

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.

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)

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

SamT

10-05-2015, 06:56 AM

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.

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?

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.

isasa74

10-05-2015, 11:52 AM

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).

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:

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

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...:)

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

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

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 :)

SamT

10-05-2015, 04:29 PM

Before I check out your file

(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.

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.

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.

SamT

10-05-2015, 06:13 PM

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.

isasa74

10-06-2015, 12:49 AM

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 :)

Before I check out your file

(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:

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

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 :)

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 :)

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 :)

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

isasa74

10-06-2015, 01:32 AM

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:

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

Run the tool

Press Prepare button
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.

snb

10-06-2015, 02:43 AM

@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.

SamT

10-06-2015, 08:12 AM

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

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.

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.

snb

10-06-2015, 12:55 PM

@SamT

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

isasa74

10-06-2015, 01:19 PM

Hi SamT

SORRY I just didn't understand that you prepared a file for me , I misread your post, sorry again I feel so stupid...I actually not even saw the file (because my small laptop screen maybe).
I thought they were all instructions for me too clean up my messy personal file and I was planning to follow those tonight after the office, and as a stupid in the meantime I thought to send you again my stuff but just with the (wrong ) conviction of making your support easier...please understand and forgive my ignorance once more...

I did now all you have suggested (using the file that you prepared for me), what is the next step please?

@snb

Hi mate

thanks for reading my thread and for your former suggestions :)
You are completely right (too much right!!) and I have actually started to study VBA from the basics (even if I'm maybe a bit old).

However this project it is not involved with my learning process, of course is over ambitious for me and that is why guys I was really asking for a GREAT favor from you...

I've been projecting this tool for two months now , and no matter how much of the code inside (little) is mine I can assure you that I 've put a lot of efforts in this is true also for the people that helped me.
So of course I can close the thread at your smallest sign and leave (if there is a way to do) a very good feedback to both, but I won't stop this project...

isasa74

10-09-2015, 02:54 AM

Hi SamT,

I have now a very light and clean Personal file with only the three procedures (two process and a Macro, now that I know the difference) that I use for the tool.

Do you think you could kindly have a look at it please? if not, no problems! I will send you an email with big thansk for what you have been doing till now for me :)

THANKS!

SamT

10-09-2015, 07:36 AM

Yes, I would like to see it. Also, please prepare a new Test book, but with a fresh non modified in any way sample of the raw data before any changes are made to it. I still have the original, but I can't test all the code without a raw sample to start with.

isasa74

10-10-2015, 05:18 AM

Hi SamT,

I hope you are fine, THANKS for helping me as always :)

Ok I managed to load the three macro/procedures in a file along with the raw data, so no need ( I hope) to send the personal file too.
I tried to comment each action of all the procedures , I hope this can help you reading the code.

Our main target stays as : to have the formats of the values in sheet2 columns D:E be dynamically concatenated along with the values.

the three procedures are:

run First Step of Tool Basically it prepares the two sheets for the comparison
run Second Step of Tool Compare the two sheets and get rid of formulas to make the two sheet lighter when working with filters.
run Reset of the tool. Reset the tool. (I have a better version of this very basic reset but I left the file with the macro in my laptop I will send it to you later if you want)

Thanks!!!

SamT

10-10-2015, 08:56 AM

I still can't help you. You haven't provided what I asked you for.

Raw means: pure, unadulterated, fresh, new, basic, coarse, crude, natural, organic, rough, uncooked, unprocessed, untreated, green, native, immature, unbaked, undressed, unfashioned, unformed, ungraded, unpasteurized, unprepared, unrefined, unsorted, unstained

No one can help you if you keep giving them the information that you think they should have instead of the information they ask for.

Yes, I would like to see it. Also, please prepare a new Test book, but with a fresh non modified in any way sample of the raw data before any changes are made to it. I still have the original, but I can't test all the code without a raw sample to start with.
To clarify, I just want a book with only one sheet in it with the RAW data on the sheet.

isasa74

10-10-2015, 12:18 PM

Hi SamT,

Sorry for making you loose more time, but actually in the file I've sent you have already data at its rawest stage, what you have in there is data BEFORE any adjustment , and BEFORE any macro.
I have just copied/pasted directly from the system, I don't have a previous version of those data and I don't need.

So:

In the sheet1 you have already the data coming from the system in its rawest stage.(just coming out of the system like it is)

In the sheet2 you have already the data coming from the requester (the person who requests those data be uploaded) in its rawest stage.

THERE ARE NO RAWER STAGES for neither of the two sheets (sorry for the caps, but is to put in evidence)
The tool is actually meant to check if what was requested (Sheet2) was correctly uploaded in the system (Sheet1).

but you are the boss and the expert :) so I have now attached two new separated files (since I'm not sure which one you may need more )
Please as I said consider that this is already the rawest form that I have.

the RAWEST form of data that I need to have in sheet1 as coming from our system (Unprocessed)
the RAWEST state of data that I need to have in sheet2 ascoming from the requester (Unprocessed by me , maybe by the requester but not by me)

I hope it is clear if not let me know please :)

THANKS!! and THANKS AGAIN :)

SamT

10-10-2015, 01:55 PM

I am slow, but I am getting there. I now have an understanding that I did not before

You receive both sheets of data from "The Requester"

Can you upload whatever it id that this "Requester" sends you?

One more question. (sorry 'bout that.)

In the sheet2 you have already the data coming from the requester (the person who requests those data be uploaded) in its rawest stage.

What does the person mean when he requests that "those data be uploaded"?

isasa74

10-10-2015, 02:38 PM

Hi SamT :)

Thanks a lot for reading my long post...
I think at this point the best is too give you a bit of background...

We have a System from where we drop orders.
My team must upload/input in this system prices, freights, import duties for all the part numbers (products), on this purpose we use a mass upload tool created years ago by our IT team.
Prices, Freights, Import Duties are provided by another team.
They fill in a Request Form (that is actually what you have in sheet2) and ask us to upload those in the system according to their request.

using their request form I upload/transfer those info in our system, but we have an old issue with our mass upload tool and sometimes the upload can be NOT entirely successful, and for few part numbers either the price or the freight or only the currency of those can be wrongly uploaded.

So after the upload I MUST double check if everything is ok, hence I started to work on this tool.
I copy the request form in sheet2, then I download the info from the system (sheet1) and comparing those two sheets I check if the mass upload (transfer) of the info went through correctly.

So going back to our project for example :

The request from the other team is: Vendor X Plant NL10 P/N Apple Price 4$ Freight 4€ import duties 3,5
I transfer those INFO to the system via the mass upload tool (that unfortunately I cannot trust blindly).
After the upload I download an extract from the system (what you have in sheet1) and I compare it to what I had in the request (what you have in sheet2) to see if the INFO upload went 100% through.
Part numbers can be thousands that is why I need a tool to check all of them and fix them if needed.

SamT

10-10-2015, 06:46 PM

Edit my previous post to add

Can you upload whatever it id that this "Requester" sends you?
And please make copies of sheets 1 and 2 [just leave the default names Sheet1(2) and Sheet2(2)]

SamT

10-10-2015, 06:56 PM

Edited to add: I posted this before I saw you last post.
Is the Mass upload tool an Excel tool? Maybe it would be best if we just fixed that.
Back to before editing:

I just spent the last few hours parsing and improving your code in MOD1runFirstStepofTool and I think I see the system process.

The system post the data to a web site, the "Requester" person downloads the information to (what looks like) a Worksheet. He/She also fills a Worksheet directly form the system and send them to you.

Your job is to find the discrepancies between the two sheets, which are not even close to being in the same formats, styles, or layouts.

But the real problems are in how the data is input into the system and how the system exports the data to the Web Page.

Any Way... Here is the work I've done on your code. I suggest that you run each by itself (Click inside the sub, then press F5,) to see if it works. The whole module compiles so it should work OK.

Option Explicit

'NumberFormats used for comparing sheets
Const NmFrmtEURSign As String = "[$€-2] #,##0.00"
Const NmFrmtUSDSign As String = "$#,##0.00"
Const NmFrmtSimple As String = "0.00"

'Possible NumberFormats 'X, Y' & Z are commonly used as names when there are
'no reasonable mnemonics and their usage is as limited as it is herein.
Const strX As String = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Const strY As String = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* ""-""??_);_(@_)"
Const strZ As String = "_([$€-2] * #,##0.00_);_([$€-2] * (#,##0.00);_([$€-2] * ""-""??_);_(@_)"

Sub Main()
Sheet2PartNumSpaces
Sheet2NumberFormats
Sheet1NumberFormats
End Sub

Sub Sheet2PartNumSpaces()
'Removes All Spaces from Part Number Column
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Sheet2").Columns(3)
For rw = 2 To .Cells(Rows.Count).End(xlUp).Row
.Cells(rw) = Join(Split(.Cells(rw), " "), "") 'SamT says, "Thank you snb."
Next rw
End With
Application.ScreenUpdating = True
End Sub

I really should put the 'Headerless Import Duties Column code into its own sub, but it fits in with For... Next loop.

Sub Sheet2NumberFormats()
' this first parte of this procedure gets rid of some wrong formats in sheet2,
' there are many formats that give to the cell the symbol €/$ (Accounting is one of those,or English Dollars)
' this procedures changes all Accounting USD/EUR (and others " Wrong"formats) that are used in the sheets2 into the Currency USD/EUR formats that rw want.
' All this part of the code rw did by myself :)

Dim rw As Long

Application.ScreenUpdating = False

With Sheets("Sheet2")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
' Wrong format are replaced with correct ones ($#,##0.00 - [$€-2] #,##0.00)

'Format Price Column
With .Range("D" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, Missing import duty.
.NumberFormat = NmFrmtSimple
End If

Select Case .NumberFormat
Case NmFrmtUSDSign 'Do nothing, Same As Exit Select
Case NmFrmtEURSign 'Do nothing, Same As Exit Select
Case NmFrmtSimple 'Do nothing, Same As Exit Select
Case strX: .NumberFormat = NmFrmtUSDSign
Case strY: .NumberFormat = NmFrmtUSDSign
Case strZ: .NumberFormat = NmFrmtEURSign
Case Else
.Interior.ColorIndex = 3 'Turn the cell Red, new or missing Currency Format
End Select
End With

'Format Freight Column
With .Range("E" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, Missing import duty.
.NumberFormat = NmFrmtSimple
End If

Select Case .NumberFormat
Case NmFrmtUSDSign 'Do nothing, Same As Exit Select
Case NmFrmtEURSign 'Do nothing, Same As Exit Select
Case NmFrmtSimple 'Do nothing, Same As Exit Select
Case strX: .NumberFormat = NmFrmtUSDSign
Case strY: .NumberFormat = NmFrmtUSDSign
Case strZ: .NumberFormat = NmFrmtEURSign
Case Else
.Interior.ColorIndex = 3 'Turn the cell Red, new or missing Currency Format
End Select
End With

'Headerless Import Duties Column
With .Range("F" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, Missing import duty.
End If
End With
Next
End With
Application.ScreenUpdating = True
End Sub

Sub Sheet1NumberFormats()
'Change format for D & F accodingly to value in E & G WORKS! rw didn't this part of the code :(
' we re now working with sheet1, this procedure " reads" columns E & G and format cells in D & F accordingly
' the word USD/EUR in any cells of columns E & G triggers accordingly the format $#,##0.00 or [$€-2] #,##0.00 for the corrispondent Cells in Columns D & F
' After the D & F are formatted the procedure delete columns E & G

Dim rw As Long
Application.ScreenUpdating = False

With Sheet1
For rw = 2 To Cells(Rows.Count, "C").End(xlUp).Row 'If you're missing a part number, you gots real problems

'Set NumberForamt in Column Price
With .Cells(rw, "D")
Select Case .Offset(0, 1).Value
Case "EUR"
.NumberFormat = NmFrmtEURSign
Case "USD"
.NumberFormat = NmFrmtUSDSign
Case Else
.Interior.ColorIndex = 3 'Turn the cell Red, missing EUR/USD
End Select
End With

'Set Number Format in Column Import Duty
With .Cells(rw, "F")
Select Case .Offset(0, 1).Value
Case "EUR"
.NumberFormat = NmFrmtEURSign
Case "USD"
.NumberFormat = NmFrmtUSDSign
Case Else
.Interior.ColorIndex = 3 'Turn the cell Red, missing EUR/USD
End Select

'Import Duties Column
With .Range("F" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, Missing import duty.
End If
End With
End With
Next

Columns(5).Delete
Columns(6).Delete
End With
Application.ScreenUpdating = True
End Sub

isasa74

10-11-2015, 05:01 AM

Hi SamT,

thanks for all this...really appreciated.

Is the Mass upload tool an Excel tool? Maybe it would be best if we just fixed that.

No it is not :( it is an old SAP tool, developed by a person that left the company and nobody took care of maintaining the tool any longer.

the system post the data to a web site, the "Requester" person downloads the information to (what looks like) a Worksheet. He/She also fills a Worksheet directly form the system and send them to you.

Exactly, only one clarification needed:
The requester is downloading the info from a system (let's call it A) that is different from mine (let's call it B) so that is why I need to transfer from A to B via the mass upload defective tool.
The request form is actually the worksheet that you have in Sheet2.

Your job is to find the discrepancies between the two sheets, which are not even close to being in the same formats, styles, or layouts.

PERFECT! thanks. I do that comparison after the mass upload from A to B and when I find discrepancies I adjust those manually in B

But the real problems are in how the data is input into the system and how the system exports the data to the Web Page.

a part from the faulty mass upload tool and the thing that the A & B systems are not communicating between them, another problem is that the person who fills in the Request Form is always different and when indicating , for example, the currencies... they always go for different formats :D

Lets go to the code, first of all thanks you are as much expert as much you are kind :)
Comments:

I have saved the module (it is only one module right?) in my personal file as in the normal workbook I have a message saying that I cannot save VB projects.

Not Sure how it works with the Option Explicit thing :) you posted for first, how and where have I to save it please? what I did is : I started recording a Macro then stopped it, edited and copied/pasted

Sheet2PartNumSpaces() WORKS GREAT thanks

Sheet2NumberFormats() WORKS GREAT only one comment please : No need to highlight in red missing import duties when B<>NL10.

Sheet1NumberFormats() Got an Error please see the attachment.

Main Got an Error please see the attachment.

Thanks so Much :)

SamT

10-11-2015, 09:56 AM

Option Explicit is a Compiler directive that tells the Compiler that all variables must be Explicitly declared. Mostly it is a spell check tool for made up words like Variable Names. Without Option Explicit, you might declare a variable (Dim abcX as String) but in the code make a typo (abcZ = " This is a String") and not know it until the program crashed.

"Compile Error: Variable not defined" means one of two things; 1) you literally forgot to Declare a Variable (No 'Dim abcX As String') or 2) you made a typo. The typo doesn't need to be in a variable Name, it can be in a regular VBA word like 'Range' spelled 'Ragne'. All the Sub Names in sub 'Main' look to be spelled correctly, so I don't understand why the Compiler is point 'Main' out to you.I compiled the entire project (all modules) without error.

Usually VBA will highlight the error for you after you click one of the buttons on the error message Dialog

VBA Settings for troubleshooting Code:
In the VBA Editor menu Tools >> Options and on the 'Editor' Tab Check every CheckBox in the 'Code Settings' Frame.

On the 'General' Tab check all the CheckBoxes and the 'Break on All Errors' Radio Button.

On the 'Editor Format' Tab, 'Code Colors' Frame, make sure that 'Syntax Errors Text' is set to Red Foreground, and that 'Execution point text' has a Yellow Background. Check the 'Margin Indicator bar' CheckBox.

SAP is a company that has a suite of programs for Databases. not only can Excel itself deal directly with most Databases, we have experts in most of the common DBs. You might want to contact our Owners, they also have a for profit consulting firm, that handles large projects and can be trusted with proprietary information. Click the 'Portal' link at the top of the page.

a part from the faulty mass upload tool and the thing that the A & B systems are not communicating between them, another problem is that the person who fills in the Request Form is always different and when indicating , for example, the currencies... they always go for different formats :D

We can rearrange the code in Sheet2NumFormats to standardize them before you transfer the data into your system. The you can run both Sheet2 subs to clean it up before transfer.

Remove these constants
'Possible NumberFormats 'X, Y' & Z are commonly used as names when there are
'no reasonable mnemonics and their usage is as limited as it is herein.
Const strX As String = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Const strY As String = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* ""-""??_);_(@_)"
Const strZ As String = "_([$€-2] * #,##0.00_);_([$€-2] * (#,##0.00);_([$€-2] * ""-""??_);_(@_)"

Replace the three Format Column Code sections With this

'Format Price Column
With .Range("D" & rw)
If InStr(.NumberFormat, "€") <> 0 Then
NumberFormat = NmFrmtEURSign
ElseIf InStr(.NumberFormat, "$") <> 0 Then
.NumberFormat = NmFrmtUSDSign
Else
.Interior.ColorIndex = 3 'Turn the cell Red, new or missing Currency Format
End If

If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, missing Price.
.NumberFormat = NmFrmtSimple
End If
End With

'Format Freight Column
With .Range("E" & rw)
If InStr(.NumberFormat, "€") <> 0 Then
NumberFormat = NmFrmtEURSign
ElseIf InStr(.NumberFormat, "$") <> 0 Then
.NumberFormat = NmFrmtUSDSign
Else 'This won't catch a new format that also uses a $ sign ("[$₮-2]")
.Interior.ColorIndex = 3 'Turn the cell Red, new or missing Currency Format
End If

If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, missing Price.
.NumberFormat = NmFrmtSimple
End If
End With

'Headerless Import Duties Column
With .Range("F" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
If .Range("B" & rw) <> "NL10" Then
.Interior.ColorIndex = 3 'Turn the cell Red, missing Import Duty.
End If
End If
End With

isasa74

10-11-2015, 12:32 PM

Hi Samt :)

Ok regarding SAP I need to talk to my line manager , to be honest I'm doing this tool with you only to help out my team that right now has to check all the value one by one. I doubt my managers investing money to help us with this issue :(

PLEASE NOTE : I have all the four subs in only one module, is that ok?
(BTW I now managed to copy everything in a NON Personal file using the Project Explorer as you taught me.)

Regarding the Main sub and Sheet1NumberFormats()

After I changed some settings as you suggested (most of them were already ok), when I run either the Main or Sheet1NumberFormats()sub I
have the debug highlighting in yellow .ClearContents in the following part of Sheet1NumberFormats() (please see below and Attachment )

'Import Duties Column
With .Range("F" & rw)
If .Value = "" Or .Value = 0 Then
.ClearContents
.Interior.ColorIndex = 3 'Turn the cell Red, Missing import duty.
End If
End With
End With
Next

Regarding the Sheet2NumberFormats()
Please let's fix first Sheet1NumberFormats otherwise I will make confusion..is that ok with you too please?

THANKS!!!!!!

isasa74

10-11-2015, 01:25 PM

Hi SamT,

I fixed it !!!!
actually it should have been With .Range("H" & rw) because Import duties are still in H at that stage of the procedure.
In fact only at the end of the sub with the deletion of Columns E & G import duties will be in F :)

I think we are ready to go ahead with the second part of the tool right? if you like of course and when you have time for me :)

SamT

10-11-2015, 01:30 PM

What did the error message say?
Looking at that line and the lines before it, I don't "see" any problems.

When the Execution (Yellow Background) is inside and 'If... Then' like that, it means that the 'If' was good and was TRUE.

BTW, Now that you have set the Troubleshooting Options, if you hover the mouse cursor over an item that actually has a value, you can see the value as a ToolTip. When ClearContents is Yellow, Hover the mouse over '.Value'.

BTW2, if you place the typing cursor inside a VBA word and press F1, you well get to the Help section on that word.

BTW3. You do realize that when you get done with this you will be able to code for yourself :D

SamT

10-11-2015, 01:32 PM

I think we are ready to go ahead with the second part of the tool right? if you like of course and when you have time for me
Post your (detailed) question whenever you like. I will look at it when I have time. :devil2:

snb

10-11-2015, 01:34 PM

Ask your boss to send a h u g e cheque to SamT !!

isasa74

10-11-2015, 02:02 PM

thanks so much I'm learning a lot from you...
you have already everything you need I think (in the file RAW_Test1), I'm talking about the second macro that I 've sent you :

MOD2runSecondStepofTool

that is the second code for the second part of the tool, which is the actual comparison.

and the main target, the target for which I started the thread , stays as:

to have the formats of the values in sheet2 columns D:E be dynamically concatenated along with the values.

When you have time please :), we are almost there...

@snb

I will :D , and by the way I leave in Amsterdam so if you two guys plan a trip in Europe just drop me an email ;)

snb

10-11-2015, 02:51 PM

I live just around the corner... you can see that my website www.snb-vba.eu is in Dutch.

SamT

10-11-2015, 04:46 PM

I don't want a check.

If you really want to thank me, send me a letter of thanks (on letterhead stationary?) and a nice new looking ƒ1.00 Gulden I can frame and put on my "I love me" wall. I'm trying to see how many Countries and States I can get for my grandkids to "ooh" and "aah" over.

and the main target, the target for which I started the thread , stays as:

to have the formats of the values in sheet2 columns D:E be dynamically concatenated along with the values.

No. It's not the main target, it's not even a minor target.

To use a football analogy, concatenating the NumberFormats is merely one way to shape your tongue while head butting the ball down field, and I am not even sure that a head butt is the best possible passing technique in your case.

I don't know if you are familiar with this old adage, but you've been focused on one tree for so long that you have forgotten the forest.

The main goal of all this work is to improve the functioning of your Database system. Period. Stop. End.

BTW, Iam using Office Excel XP as shown in my signature block, so I don't ever have any Buttons on Sheets to work with while I am helping you. Because Excel insists on opening all Excel 2007 and greater Workbooks in Read only mode, even ones I create myself, I wind up with many versions of your workbooks and after a short time I can't remember wcich was which, so I delete tham all and just download the last version you uploaded.

That was 13 posts ago. Yesterday.

Please upload your latest version as yesterday's is way out of date.

In the meantime I will try to muddle through that old version.

SamT

10-11-2015, 06:34 PM

The runSecondStepofTool Algorithm:

With Sheet2

Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
Assign the Record String to a Dictionary Object
To the left of the Table insert a formula to concatenate Each Record + some additional strings

With Sheet1

Assign "Eur" to the Freight Currency Field of all Records
Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
Compare this Record String to the Sheet2 Dictionary. If found, Assign "V" to column "G" to the right of the Table
In Column "H", If "V" not present in Column "G", assign an INDEX + MATCH formula using certain fields from the corresponding Row in Sheet2
Replace Formulas in Column "H" with Values.
Format Column "H"
In Column "H", Replace "N/A" with "Not Requested"

To investigate look down ~ 900 rows in Column H to find clues as to problem. And when you get it to work a logic bomb will make it fail at the first "Not Requested" and all subsequent rows will be "Not Requested"

My Step 2 Algorithm
Sheet1 has no formatting except the headers
Run Step 1 Code on sheet2
Sort both sheets by Vendor then Plant Number then Part number

With Sheet1 format Price and Freight NumberFormat with Currency Symbol to match sheet 2 Currency Symbol
For each Record in Sheet 1 & 2

If Vendor & Plant & Part# = Vendor & Plant & Part#, then Records Match

Else Compare above and below Records to see which Sheet has missing/additional record
Insert blank Highlighted row on sheet missing record. Adjust Row Counter as needed. GoTo Next Row (Record)

If Freight = Freight Then Records Match Else Highlight Discrepancies
If Price = Price Then Records Match Else Highlight Discrepancies
If P.Currency.NumberFormat = Price.NumberFormatThen Records Match Else Highlight Discrepancies
IF.Currency.NumberFormat = Freight.NumberFormat Then Records MatchElse Highlight Discrepancies

Next Record (Row)
With Sheets 1 & 2, delete all records with no highlighting for ease of investigation, keeping all records (rows) aligned

To investigate merely look from sheet to sheet, row by row, only rows with discrepancies are there.

With the clues above, can you see the Logic Bomb in your code?

snb

10-12-2015, 12:09 AM

@SamT

The 'newest' gulden is at least 14 year old; since 2001 we adopted the less valuable EURO.

SamT

10-12-2015, 07:51 AM

@ snb,

I know, but Euros are not country specific. Or are they?

When I got out of the USNavy, I had a scrapbook with over 30 different currencies and a raft of diplomas and certificates from various tech schools and colleges.

It was stolen from me, by children, no doubt, as even if they had a way to exchange all the money, there was less than 30 USD in it. But the bills were very pretty compared to US "greenbacks."

snb

10-12-2015, 08:36 AM

All Euros are country specific.

SamT

10-12-2015, 09:16 AM

Then, although a Gulden is prettier, a Nederland Euro would work.

isasa74

10-12-2015, 01:16 PM

HI snb
Belgium? I think I once bumped into you web site :)

@ SamT
Thanks! :)

I will try to find you a more valuable Gulden for you for sure I'll try hard
and the letter obviously to say thank you :)

No. It's not the main target, it's not even a minor target.

To use a football analogy, concatenating the NumberFormats is merely one way to shape your tongue while head butting the ball down field, and I am not even sure that a head butt is the best possible passing technique in your case.

I don't know if you are familiar with this old adage, but you've been focused on one tree for so long that you have forgotten the forest.

Great SamT...I had been studying literature for all my life...then when I was going to starve... I adapted my dreamer mind to technical jobs as much I could.. I
was not familiar with your old adage but now I love it :)

When I say that my main target was that silly thing with the concatenation, I meant that I disturbed you guys for that reason... as in my very bugged opinion the tool was ok and the only missing thing was the possibility to have also the format (EUR/USD) reported in sheet1...

Now your immensely more technical mind unveiled as the all procedure is very subject to errors, the Logic Bomb as you call it.
And now that I read your explanation, that unfortunately I can only understand partially, I see that my tool was very rudimental...

What Can I say? I can only say thanks and asking you to be patient please , as you certainly have been till now.
Your algorithm sounds supremely good, and if it's not asking too much of your time yes please let's do it :)

I attached the latest version of the file, where you have both mine and yours codes, only one comment about your codes:

We can rearrange the code in Sheet2NumFormats to standardize them before you transfer the data into your system. The you can run both Sheet2 subs to clean it up before transfer.
Remove these constants and Replace the three Format Column Code sections With this

I did it I removed the three constants and added the long section you posted but I got an error so I went back to the first version you sent...:( , see the screenshot please.

Thanks :)

isasa74

10-12-2015, 01:40 PM

I forgot to say that the only thing, if I can dare http://www.vbaexpress.com/forum/images/icons/icon11.png , that I don't like is:

To investigate merely look from sheet to sheet, row by row, only rows with discrepancies are there.

please this looking from sheet to sheet is exactly what I wanted to avoid with my concatenation rubbish idea!! :think:

my goal (or better to say my dream given my ignorance with the code..) was to have everything at my hand in sheet1 at the very end of the procedure...please:banghead:

SamT

10-12-2015, 04:12 PM

have everything at my hand in sheet1 at the very end of the procedure...please:banghead:

I'll see what I can come up with. Tomorrow.

SamT

10-18-2015, 02:31 PM

isasa,

Give me a list of the various problems, errors, and issues you see in the Request that cause problems with the System..

isasa74

10-20-2015, 05:10 AM

Hi SamT ,

I thought you forgot about me!! THANKS super THANKS for your help as always :)

Here you are:

Sheet2 – Problem with request.

1. Blank Spaces (fixed by the 1st step procedure)
2. Different Formats (fixed by the 1st step procedure)
3. Wrong currencies, basically:

a. When column B = US20 then Freight must be expressed in USD in all the others cases in EUR. (it would be wonderful to have those errors already highlighted by the Step 1st in Sheet2)

b. Price should always be expressed in USD. (it would be wonderful to have those errors already highlighted by the Step 1st in Sheet2)

4. Wrong Import Duties, basically when NL10 in Column B import duties column must have a value, when Column B <> NL10 there must be NO import duties.
(exception when the part number (column C- Sheet2) starts with 8FI* even with column B = NL10 in column B there must be NO import duties.)

Problem with the mass upload tool (reasons why I need to check after the upload, just FYI)

1. We change price/freight very often, when too many “condition” have been uploaded for the same Part Number the upload can go wrong (new price/freight don’t overwrite the old ones)
2. When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.
3. When we have too many vendors for the same part, the upload tool can mess up and change price/freight for the wrong vendor.

PLEASE NOTE :

Concerning point 3. a & b I’m exploring solution with the conditional formatting but I got stuck with the currency as Conditional formatting doesn’t seem to me able to handle that.

SamT

10-20-2015, 09:32 AM

1 to 4: I can fix all that easily... Before you upload the request to the Mass upload tool.

Problem with the mass upload tool (reasons why I need to check after the upload, just FYI)

1. We change price/freight very often, when too many “condition” have been uploaded for the same Part Number the upload can go wrong (new price/freight don’t overwrite the old ones)
2. When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.
3. When we have too many vendors for the same part, the upload tool can mess up and change price/freight for the wrong vendor.

WE can take those tasks away from the MUT and give them to Excel and VBA, But first let's get the request fixed as much as possible.

I wish to introduce a new Programming Term to you: "Business Rules."

Examples of Business Rules for Requests:

No blank Spaces.
Only Number Formats "$" and "€"

Exception: When Value = zero or Blank, NumberFormat = "$0.00"

When column B = US20 then Freight must be expressed in Number Formats "$"
when NL10 in Column B import duties column must have a value

exception: when the part number starts with 8FI* there must be NO import duties.

when Column B <> NL10 there must be NO import duties.
Import Duties NumberFormat = "General" (No Number Format)

isasa74

10-20-2015, 11:02 AM

1 to 4: I can fix all that easily... Before you upload the request to the Mass upload tool.
Thanks! :)

WE can take those tasks away from the MUT and give them to Excel and VBA, But first let's get the request fixed as much as possible.

for the last three points, don't bother about because even when the Upload Template ( a MUT sheet where you put DATA in) would be perfect, there is always something getting lost/wrong when transmitted from the MUT to SAP, and to fix that I don't have either clue or authorization.

For example:

When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.

that " many conditions" I'm talking about are those already in the system so , at that point, it is not only about the MUT but more about how the MUT and SAP are communicating.

I'm already super happy if we can have the first 4 points fixed by the First Step of the tool and then the second step comparing the request (sheet2) to the result of the upload that I get as an extract from the system (Sheet1).
that way I will have:

Step one:

Making the two sheets perfectly comparable (no blank, all same format, etc.).
Plus, if we fix the 4 points of the Business rules, spotting out those errors already coming with the request.(so not imputable to the upload)

Step two:

Actually Comparing the two sheets , Spotting out what went wrong with the upload itself (if anything goes wrong of course, errors are indeed very rare but too dangerous)

I wish to introduce a new Programming Term to you: "Business Rules."

thanks SamT I'm learning a lot from you, thank you very much. :)

Let me know what else you may need for your magic please.

isasa74

10-28-2015, 11:55 AM

long time that I don't hear from you :dunno

SamT

10-28-2015, 01:29 PM

Thank you for reminding me. I have many projects an 5 or 6 are for VBA Express members. I recently cleaned up my hard drive and some of my work for you was accidentally deleted. I have now downloaded again all the files we shared.

isasa74

10-29-2015, 10:58 AM

oops no problem :)

Listen Great SamT :bow:...

if for you it is a problem to start it over don't bother please, for me it would be wonderful even only to have, somehow with a magic of yours, the currency reported in Sheet1 along with all the concatenation (I know you hate this idea sorry!!).

On the contrary if you still enjoy helping me :bow:I have please an exception for one of the business rules (Step-One for Sheet2):

Exception for rule:

4 when NL10 in Column B import duties column must have a value

IF the 6th,7th,8th digits in the part number in Sheet2-Column C is one among the following combination:

069; 052; 019; 017; 036 (e.g. 8UFT.019.15) then even if B=NL10 still NO import duties.

Thanks!

SamT

10-30-2015, 02:51 PM

Isasa,

How do you know who requested the parts, and when the request as issued?

Is there a Request ID number?

I think that an Error rweprot is better than indicating errors on the request or the SAP Report. That way your team only has to look at lines with errors, instead of scanning the entire request and Sap Error Report.

But I need to also put the requesting facility, person, date, and request number for this to be a very good error reporting tool.

isasa74

10-30-2015, 03:27 PM

Hi SamT, I hope you are good and thanks for helping me as always :)

Isasa,

How do you know who requested the parts, and when the request as issued?

All via emails.

Is there a Request ID number?
Negative :*), all via emails

I think that an Error rweprot is better than indicating errors on the request or the SAP Report. That way your team only has to look at lines with errors, instead of scanning the entire request and Sap Error Report.

The errors in the request form are not the only problem, it is good to have those captured but they are not the only ones.

The request content is copied (with other details that I add) in a template to be uploaded via MUT, but after the upload I still need to check the outcome via SAP extract as we have those random (rarely indeed but dangerous) errors with the MUT itself.

That is why it is (very) good to check the business rules in the request (Sheet2) but it is also crucial to check the extract from SAP (Sheet1), the latter check is actually the main goal of the all tool thing.

But I need to also put the requesting facility, person, date, and request number for this to be a very good error reporting tool.

Don't have a name, always coming from different people and always via emails.

SamT

10-31-2015, 09:23 AM

So there is no method to identify a request and the SAP output together?

isasa74

10-31-2015, 02:56 PM

Hi SamT :)

No there no way to identify them together because the outcome of SAP is identified with only my name/or one's of my team an the date.

I receive the prices via email (many emails to be precise) to be uploaded for the next month one week before month end in the request form that you know, I do a first check to get read of the very gross errors (typos for example)
I transfer the content of the request in a template that can be processed by the MUT.
I upload everything via MUT.
I have then a windows of one week to check for errors in SAP.
I get out of SAP an extract of all the prices upload for a given date (all those for next month usually)
I check the extract or errors.

My (yours too) tool is meant:

to check errors in the request sheet2 (even after the upload , that doesn't matter because prices are for the next month) that way I can understand whether the error comes from a wrong request or from the faulty upload tool.
to check out if what requested is actually what we have in the system (by comparing the two sheets)
to Spot not Requested Records

You have noticed that the tool gives the message NOT Requested when in the SAP extract you have a record that wasn't in the request, that is because in SAP there are some prices records (funny ones) created when someone tries to place an order for a piece that doesn't have a prices record yet.