Solved

# update of previous solution for Mastercard spreadsheet

Posted on 2013-01-08
Medium Priority
294 Views
gowflow,

For the Mastercard workbook, you have it so that when a value is input into Col E of any MCR sheet, if the value is over \$2,000.00 a new row is copied from the previous row until the full amount from Col J is used up. All that works fine.

However, what it also does is create an empty row after the last calculated row. I would rather no blank row is created. Do you understand what I'm referring to?  I can't find the solution in EE that you worked on for this.

I know this is pretty vague. I'm hoping you remember, but if not, I'll dig some more to find the original solution.
0
Question by:JaseSt
• 8
• 7

LVL 31

Expert Comment

ID: 38758020
Hello,
Don't know if this will do it but you can try this and revert if not will look further.

1) Make a copy of your latest MC file and give it a new name.
2) goto VBA and doubleclick on module1 and click on the bottom left icon to view 1 sub at a time.
3) locate Sub SplitAmountsMCR and delete it.
4) Paste the below code after any end sub

``````Sub SplitAmountsMCR(WS As Worksheet, Target As Range)
Dim startDate, LastDateinMC As Date
Dim colnum, rowNum, stopRow As Integer

If UCase(Left(Trim(WS.Name), 3)) = "MCR" Or UCase(Trim(WS.Name)) = "HMF ACCOUNT" Then

If Not Intersect(Target, WS.Columns(5)) Is Nothing Then

If UCase(Left(Trim(WS.Name), 3)) = "MCR" Then
'Col E
colnum = 5
count = 0
Else
'Col I
colnum = 9
count = 1
End If

rowNum = Target.Row
stopRow = Target.Row + Target.Rows.count
'Do While rowNum < stopRow

If WS.Cells(rowNum, colnum).Value > 0 Then
num = WS.Cells(rowNum, colnum).Value
Do While num > 2000
Application.EnableEvents = False
Cells(rowNum + count + RowAdj, colnum).EntireRow.Insert
stopRow = stopRow + 1
WS.Cells(rowNum + count, colnum + 1 + adjust).Value = 2000
WS.Cells(rowNum + count, colnum - 7 + adjust).Value = WS.Cells(rowNum, colnum - 7 + adjust).Value
WS.Cells(rowNum + count, colnum - 6 + adjust).Value = WS.Cells(rowNum, colnum - 6 + adjust).Value
WS.Cells(rowNum + count, colnum - 5 + adjust).Value = WS.Cells(rowNum, colnum - 5 + adjust).Value
Application.EnableEvents = True
num = num - 2000
count = count + 1

Loop
Application.EnableEvents = False
'WS.Cells(rowNum + count + RowAdj, colnum).EntireRow.Insert
stopRow = stopRow + 1
WS.Cells(rowNum + count, colnum + 1 + adjust).Value = num
WS.Cells(rowNum + count, colnum - 7 + adjust).Value = WS.Cells(rowNum, colnum - 7 + adjust).Value
WS.Cells(rowNum + count, colnum - 6 + adjust).Value = WS.Cells(rowNum, colnum - 6 + adjust).Value
WS.Cells(rowNum + count, colnum - 5 + adjust).Value = WS.Cells(rowNum, colnum - 5 + adjust).Value
Application.EnableEvents = True
End If
rowNum = rowNum + 1
'Loop
End If
End If
End Sub
``````

5) SAVE and Exit the workbook
6) Start it again and give it a try.

Let me know
gowflow
0

Author Comment

ID: 38758710
Thank you, gowflow. Will try it out later today.
0

LVL 31

Expert Comment

ID: 38760316
ok fine
gowflow
0

Author Comment

ID: 38762621
it worked, gowflow, but in replicating (splitting and copying) the second row in the below (numbers and names scrubbed) the card number (5116830007878787 ) changed to scientific notation for each of the rows that were created. :

Yyri      Cherjkoue              5116830004567901      6354.3
Vitaly Cojljlkjln                5116830007878787      7500

Can that be fixed?
0

LVL 31

Expert Comment

ID: 38762740
yes pls allow me sometime to get back to my pc as hv to leave for now. Will revert later.
gowflow
0

LVL 31

Expert Comment

ID: 38768924
Oops I thought I was waiting for your input, but seems I overlooked this will attend asap
gowflow
0

LVL 31

Expert Comment

ID: 38769790
In what sheet is this problem ?
gowflow
0

Author Comment

ID: 38770139
MCR Eforex Staff. But it also did the same thing in MCR Eforex Customers.

When I put in this:

Vita      Coost        5116830001234567      7500

is spits out this:

Vita      Coost          5.11683E+15        7500                  2,000.00
Vita      Coost          5.11683E+15                                    2,000.00
Vita      Coost          5.11683E+15                                    2,000.00
Vita      Coost          5.11683E+15                                    1,500.00
0

LVL 31

Expert Comment

ID: 38770145
so it is all the MCR files basically ?
gowlfow
0

Author Comment

ID: 38770158
Yes.
Also the HMF Account page. Now this happens with I hit F2 in Col E. (As that is what I have to do to initiate the function)

For the HMF Account page it comes out a bit different in that the first and last card number shows but in between you see the results:

Vita      Coost        5116830001234567      7500
Vita      Coost        5.11683E+15                                    2,000.00
Vita      Coost        5.11683E+15                                    2,000.00
Vita      Coost        5.11683E+15                                    2,000.00
Vita      Coost        5116830001234567                        1,361.25
0

LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 38770196
ok here it is:

1) Make a copy of your latest MC file and give it a new name.
2) goto VBA and doubleclick on module1 and click on the bottom left icon to view 1 sub at a time.
3) locate Sub SplitAmountsMCR and delete it.
4) Paste the below code after any end sub

``````Sub SplitAmountsMCR(WS As Worksheet, Target As Range)
Dim startDate, LastDateinMC As Date
Dim colnum, rowNum, stopRow As Integer

If UCase(Left(Trim(WS.Name), 3)) = "MCR" Or UCase(Trim(WS.Name)) = "HMF ACCOUNT" Then

If Not Intersect(Target, WS.Columns(5)) Is Nothing Then

If UCase(Left(Trim(WS.Name), 3)) = "MCR" Then
'Col E
colnum = 5
count = 0
Else
'Col I
colnum = 9
count = 1
End If

rowNum = Target.Row
stopRow = Target.Row + Target.Rows.count

'---> Fix Card number to be formated correctly.
WS.Cells(rowNum + count, colnum - 5 + adjust).NumberFormat = "################"
WS.Cells(rowNum, colnum - 5 + adjust).Value = Format(WS.Cells(rowNum, colnum - 5 + adjust).Value, "################")

'Do While rowNum < stopRow

If WS.Cells(rowNum, colnum).Value > 0 Then
num = WS.Cells(rowNum, colnum).Value
Do While num > 2000
Application.EnableEvents = False
Cells(rowNum + count + RowAdj, colnum).EntireRow.Insert
stopRow = stopRow + 1
WS.Cells(rowNum + count, colnum + 1 + adjust).Value = 2000
WS.Cells(rowNum + count, colnum - 7 + adjust).Value = WS.Cells(rowNum, colnum - 7 + adjust).Value
WS.Cells(rowNum + count, colnum - 6 + adjust).Value = WS.Cells(rowNum, colnum - 6 + adjust).Value
WS.Cells(rowNum + count, colnum - 5 + adjust).NumberFormat = "################"
WS.Cells(rowNum + count, colnum - 5 + adjust).Value = Format(WS.Cells(rowNum, colnum - 5 + adjust).Value, "################")
Application.EnableEvents = True
num = num - 2000
count = count + 1
Loop

Application.EnableEvents = False
'WS.Cells(rowNum + count + RowAdj, colnum).EntireRow.Insert
stopRow = stopRow + 1
WS.Cells(rowNum + count, colnum + 1 + adjust).Value = num
WS.Cells(rowNum + count, colnum - 7 + adjust).Value = WS.Cells(rowNum, colnum - 7 + adjust).Value
WS.Cells(rowNum + count, colnum - 6 + adjust).Value = WS.Cells(rowNum, colnum - 6 + adjust).Value
WS.Cells(rowNum + count, colnum - 5 + adjust).NumberFormat = "################"
WS.Cells(rowNum + count, colnum - 5 + adjust).Value = Format(WS.Cells(rowNum, colnum - 5 + adjust).Value, "################")
Application.EnableEvents = True
End If
rowNum = rowNum + 1
'Loop
End If
End If
End Sub
``````

5) SAVE and Exit the workbook
6) Open it and Try both MCR sheets and HMF Account sheet.

Let me know.
gowflow
0

Author Closing Comment

ID: 38770233
Great work!! Works perfectly.

Next up: Remember how when an email is processed for Earthport wires the email category turns green? Well, it stopped doing it for Mastercard when I switched to Outlook 2010. Still works for the Visa process. I'll put the question line here later.

Thanks again, gowflow. Greatly appreciate your help.
0

LVL 31

Expert Comment

ID: 38770323
ok no problem shoot. Try when you post the question avoid refering to gowflow I know it will seem weired as a post but find a way to put ur post without refering as remember last comment was not too nice.
gowflow
0

Author Comment

ID: 38770487
0

Author Comment

ID: 38770489
please be aware that the MC spreadsheet I posted is an old one from last May
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month16 days, 11 hours left to enroll