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
Solved

slight tweak to previous solution

Posted on 2013-01-29
6
200 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

789 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