Improve company productivity with a Business Account.Sign Up

x
Solved

slight tweak to previous solution

Posted on 2013-01-29
Medium Priority
210 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
Question by:JaseSt
• 3
• 3
6 Comments

LVL 31

Accepted Solution

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
``````

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

ID: 38838120
That did it! Thank you.
0

LVL 31

Expert Comment

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

Author Comment

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

ID: 38841232
ok np
gowflow
0

Author Comment

ID: 38850946
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Course of the Month4 days, 11 hours left to enroll

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

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