Solved

update of previous solution for Mastercard spreadsheet

Posted on 2013-01-08
15
239 Views
Last Modified: 2013-01-12
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
Comment
Question by:JaseSt
  • 8
  • 7
15 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 num, count, adjust, dateAdder As Integer
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
         adjust = 4
         count = 0
         RowAdj = 1
     Else
        'Col I
         colnum = 9
         count = 1
         adjust = 0
         RowAdj = 0
     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

Open in new window


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

Let me know
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
Thank you, gowflow. Will try it out later today.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
yes pls allow me sometime to get back to my pc as hv to leave for now. Will revert later.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Oops I thought I was waiting for your input, but seems I overlooked this will attend asap
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
In what sheet is this problem ?
gowflow
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JaseSt
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
so it is all the MCR files basically ?
gowlfow
0
 

Author Comment

by:JaseSt
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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 num, count, adjust, dateAdder As Integer
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
         adjust = 4
         count = 0
         RowAdj = 1
     Else
        'Col I
         colnum = 9
         count = 1
         adjust = 0
         RowAdj = 0
     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

Open in new window


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

by:JaseSt
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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

by:JaseSt
Comment Utility
0
 

Author Comment

by:JaseSt
Comment Utility
please be aware that the MC spreadsheet I posted is an old one from last May
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Earnings Spreadsheet in Excel 3 37
Excel  Worksheet Relationships 7 24
How to compare data in excel 5 24
onOpen 14 27
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now