Hello 'joeu2004[_2_]
Apologises for late response I was away with no real access to the net.
the code below is what tried before with no success. basically I want a macro that I can place the following in front "Sheets("Rainbow").Select" or "Sheets("local").Select" and the macro will run on that page. failing that I would like it to run on all sheets except "Sheets("Staff").Select","Sheets("Balance").Select ","Sheets("other").Select".
Any help is much Appreciated
CR
Code in current state
Sub runtest()
'
' Reset counters
iRejCnt = 0
iTotDRVal = 0
iTotCRVal = 0
iRejAdd = 0
Application.ScreenUpdating = False
' Underline and count relevant lines
rwIndex = 1
Do Until dim wsh s(rwIndex, 1).Value = ""
' Check if current line is a rejection
ActiveSheet.Cells(rwIndex, 1).Select
bRejItem = False: bDRItem = False: bCntBal = True: iRejAdd = 1
sline = wsh.Cells(rwIndex, 1).Value
If InStr(1, sline, "REJECTED TRANSACTION", 1) Then bRejItem = True: iRejAdd = 1
If InStr(1, sline, "INVALID TRANSACTION", 1) Then bRejItem = True: iRejAdd = 1
If InStr(1, sline, "EARLY SETTLEMENT OF", 1) Then bRejItem = False: bCntBal = True: iRejAdd = 1
If InStr(1, sline, "CURRENT SETTLEMENT", 1) Then bRejItem = True: bCntBal = False: iRejAdd = 1
If InStr(1, sline, "PARTIAL PAYMENT", 1) Then bRejItem = True: bCntBal = True: iRejAdd = 1
If InStr(1, sline, "REJECTED DUE TO REBATE DISCREPANCY", 1) Then bRejItem = True: iRejAdd = 1
If InStr(1, sline, "REJECTED TRANSACTION PARTIAL", 1) Then bRejItem = True: iRejAdd = 0
If InStr(1, sline, "ACCOUNT TOTAL TO DATE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "FEES IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "REBATES IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "INTEREST IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "PREMIUM IN TRANSIT", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "LEDGER BALANCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "THE BALANCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "TODAYS TRANSACTION", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "CREDITOR INTEREST", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "DIFFERENCE", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(1, sline, "INITIALS", 1) Then bRejItem = False: iRejAdd = 0: bCntBal = False
If InStr(37, sline, "DR", 1) Then bRejItem = True: bDRItem = True
' Calculate figure to add to balancing totals
If bCntBal = True Then
sRejValue = "": bFndNum = False
sline = Selection.Value
For iExtNum = 40 To Len(sline)
sLineExt = Mid$(sline, iExtNum, 1)
If sLineExt = Chr(46) And sLineExt <= Chr(57) And bFndNum = False Then sRejValue = sRejValue & sLineExt
If sLineExt Chr(57) And sRejValue < "" Then bFndNum = True
Next iExtNum
If bRejItem = False Then iTotCRVal = iTotCRVal + Val(sRejValue)
End If
' Underline report line
If bRejItem = True Then
LASTROW = rwIndex
iRejCnt = iRejCnt + iRejAdd
Selection.Borders(xlEdgeBottom).Weight = xlHairline
If bDRItem = True Then
Selection.Interior.ColorIndex = 35
If bCntBal = True Then iTotDRVal = iTotDRVal + Val(sRejValue)
Else
Selection.Interior.ColorIndex = xlNone
If bCntBal = True Then iTotCRVal = iTotCRVal + Val(sRejValue)
End If
If iRejCnt 0 And iRejCnt / 20 = Int(iRejCnt / 20) Then Range("B" & rwIndex) = iRejCnt
End If
rwIndex = rwIndex + 1
Loop
Range("W2") = rwIndex - 1
' Total of CR/DR for bottom of printout
Range("A" & rwIndex) = "Total CR Value = " & iTotDRVal
Range("A" & rwIndex + 1) = "Total DR Value = " & iTotCRVal
Range("T2") = iTotCRVal
Range("S2") = iTotDRVal
Range("x2") = LASTROW - 1
'
End Sub
Quote:
Originally Posted by joeu2004[_2_]
"lostgrave2001" wrote:
There would be 4 sheets i couldnt have the macro run on.
Your requirements are no longer clear to me.
Based on your original posting, I assumed you wanted to run a macro
__manually__ against any active worksheet. You simply wanted to know how to
make the macro available to all worksheets, and what coding changes might be
needed.
Claus assumed you wanted a macro that you would run once and it applied its
algorithm to some number of worksheets. Claus's loop selected all
worksheets. Your response indicates that you want all but 4 worksheets.
In either case, you indicated that you had difficulty applying the changes I
suggested. I assume you would have similar difficulties integrated those
changes with Claus's suggest. The changes are similar, but not exactly the
same.
If you still want help with this, please indicate which solution you want:
one macro that you run manually for any active worksheet; or a loop like
Claus's, but avoiding certain worksheets.
And please post the modified code, based on my suggestions, that did not
seem to work for you.
Finally, please let us know where the macro code currently resides: a
worksheet (object) module, located by right-clicking on the worksheet tab
and clicking on View Code; or a normal worksheet module, created by clicking
on Insert, then Module.
|