Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

slight tweak to previous solution

Posted on 2013-01-29
6
Medium Priority
?
205 Views
Last Modified: 2013-02-04
In this previous solution: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27989319.html 

When the above solution creates the duplicate rows for the name and the card number it changes the last digit of all the card numbers to 0 and the format of the cell to 'custom'. I need the card number to stay exactly the same and keep the format at 'text'.
0
Comment
Question by:JaseSt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 38831890
ok here it is

For this you will need to apply the following solution.

1) Make a copy of your latest MC file and give it a new name.
2) open the file and DO NOT ACTIVATE MACROES.
3) You need now to locate All MCR sheets and HMF account and do the following:
for every sheet highlight the Card Number Column (Col D) click on the letter D so it highlight it all and right click choose Format Cell then on the Number Tab select Text and press OK. Do this for each and every sheet MCR and HMF account sheet.
4) Open VBA and doublleclick on module1 and click to view 1 sub at a time and delete the Sub SplitAmountsMCR
5) Paste the below code aftter 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



6) SAVE and Exit the workbook.
7) Open it and give it a try putting manually Card number then manually an amount in E for MCR sheets and see what it give you and also try importing an email.

Let me know
gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 38838120
That did it! Thank you.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38840741
Tks your welcome. Pls let me know any other issue you may need help with.
gowflow
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JaseSt
ID: 38841222
oh, there is more, believe me. Thanks SO MUCH for your help. I'll post another here in the near future.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38841232
ok np
gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

705 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