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
Reply With Quote
it is an old SAP tool, developed by a person that left the company and nobody took care of maintaining the tool any longer.
