Solved

slight tweak to previous solution

Posted on 2013-01-29
6
203 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 29

Accepted Solution

by:
gowflow earned 500 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 29

Expert Comment

by:gowflow
ID: 38840741
Tks your welcome. Pls let me know any other issue you may need help with.
gowflow
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 29

Expert Comment

by:gowflow
ID: 38841232
ok np
gowflow
0
 

Author Comment

by:JaseSt
ID: 38850946
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 the scrolling table in Microsoft Excel using the INDEX function.

733 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