Posts by Jaffey

I'm looking for assistance with an index/match type situation where I have a transaction table containing all the unique transaction codes which I then need to append to an invoice table.

To assist with the matching, I created a unique ID in each table consisting of Account#+TransDate+TransAmt.

This works fine unless a customer makes multiple payments on the same day in the same amount. Unfortunately, this happens a lot. As you can see in the image, my formula fails in this situation because it is always returning the first match. I would like to find a way to extract and append the subsequent matches.

https://imgur.com/a/4wbVlzi

Here is an example of the raw invoice data as it is received (csv) before I import it into the invoice table.

https://imgur.com/a/O0bxhnC

With regard to the TransID's in the trans table, the number to the left of the decimal is the 'batch' number for that day's payments. The number to the right of the decimal indicates the payment's position in the batch file. If there is only one payment for the day, there will be no number to the right of the decimal. In this particular example, the four trans id's highlighted in yellow are sequential but this is not always the case. Note how Acct# 4628 has two payments on 20210226 but the trans ids are 52144.1 and 52144.12

Any suggestions on how to handle this much appreciated!

I need to make CSV files out of invoices that are sent to me in excel format. They look like this:

https://imgur.com/a/n4TVFvz

I've been doing it manually and would like to speed up the process. I'm looking for a solution where I can copy the invoice to one tab and have the data appear in "CSV ready" format on another tab, which I would then save as a CSV after the boss approves the numbers but I am open to other ideas...bearing in mind the boss has to approve the numbers.

At first, I thought Excel's new array formulas could be a fun way to do it but then I came across one customer whom I've named 'Fast Freddy' in the example picture who made identical payments on the same day in the same amounts which throws off my Unique formulas. I tried appending row numbers but couldn't get that to work.

So I'm thinking I'll need to go the VB route but before I go down that path I thought I'd post it here for feedback. Any suggestions much appreciated.

Instead of starting with a short list of suspicious patterns, you could filter your list using a list of all active area codes and central office exchange codes (aka CO codes). CO codes are the next 3 digits following the area code. Ex. 416.230 is the beginning of my cell number.

You can then automatically rule out any numbers that don't start with 6 proper digits leaving you just the final four digits to analyze.

The following two links will provide you with lists of all existing US and CDA area codes and exchanges. ( including landlines and cell numbers). Good luck!

https://www.nationalnanpa.com/…ports_cocodes_assign.html - look for the one that says all states

http://cnac.ca/data/COCodeStatus_ALL.zip - this is the Candadian list

Thanks Ardus, but that's what was causing my problem. I.e. setting Word before the loop caused the crash but inside the loop it would run. Anyway, I've since figured it out via debugging. It was hanging on WApp.Close. In debug mode ,the Save as window was popping up at that point and not being dealt with so the connection to word app was being broken but this was not apparent when just running it. Once I saved the first file it worked fine thereafter. So, now I can instantiate Word before the loop and all is well. Thank you so much for responding. Now I just need to find the close without saving command in word vba so it doesn't happen again. cheers

I have some code I combined from a couple sources that will open a bunch of pdf's in a directory using Word to extract a sentence of text and place it to excel. It works but is slow because it's opening and closing Word with each iteration. But when I try opening Word before the loop by moving Set WApp = CreateObject("Word.Application") before the start of the loop (and WPapp.Quit after it ends), I get a run time error saying the object is not set. Any advice on how to improve this appreciated.

Option Explicit
Sub LoopPDFsInFolder()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim r As Long
Dim WApp As Object
Dim WDoc As Object
Dim WDR As Object
Dim ExR As Range

'  Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


myPath = "c:\temp\"
myExtension = "*.PDF*"
myFile = Dir(myPath & myExtension)


r = 0


On Error GoTo ResetSettings
Do While myFile <> "" 'Loop through each Excel file in folder
Set ExR = Selection ' current location in Excel Sheet
Set WApp = CreateObject("Word.Application")
'WApp.Visible = True
Set WDoc = WApp.Documents.Open(myPath & myFile)
WApp.Selection.HomeKey Unit:=6
WApp.Selection.Find.ClearFormatting
WApp.Selection.Find.Execute "In the matter of the" 'find key phrase
WApp.Selection.MoveDown Unit:=5, Count:=1
WApp.Selection.HomeKey
WApp.Selection.MoveRight Unit:=3, Count:=1, Extend:=1
Set WDR = WApp.Selection ' copy selected sentence to excel


ExR(1, 1).Offset(r, 0) = WDR ' place below selected cell
r = r + 1


WDoc.Close
WApp.Quit


myFile = Dir 'Get next file name


Loop


Set WApp = Nothing
Set WDoc = Nothing
Set WDR = Nothing


MsgBox "Done!"


ResetSettings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Hi, I thought this was going to be simple …hoping for some help

I have an excel table (list-object) of customer addresses with columns like Customer, Address, City etc. Assume a cell (row) has already been selected in the "Address" column.

What I am after is a pair of macros that will increment/decrement the selected row up/down a row based on which macro is run. (will be assigned to menu buttons). The catch is that once the last row has been reached (if you're going down), it should select the top (first row) and then continue down again. Similarly, if you've been incrementing up, and reach the 1st row, then the last row should be selected and then it starts going up again.

These macros will work in conjunction with a Worksheet_SelectionChange event macro that currently formats the selected cell in the address column and also copies the value of it to another cell. Note that I added 'SelectedAddress' as a variable because I thought it might be useful for the Up/Down macros I'm trying to make. It's not actually used in the worksheet change event shown below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim SelectedAddress As String ' for potential use in the UP/DOWN macros


If Not Intersect(Target, Range("tbl_Addresses[Address]")) Is Nothing Then ' if cell not blank/selected do this:
Range("tbl_Addresses[Address]").Interior.ColorIndex = xlColorIndexNone ' set color to nothing


With Target 'when cell in target range/column is selected do this:
Range("F4").value = ActiveCell.value 'copy address to F4
With Selection.Interior 'change cell interior colour
.Color = 6750207 '=light yellow
End With


SelectedAddress = Target.Address ' for potential use in UP/DOWN macros


Call AddressSubmit 'processes address copied to F4 above


End With
End If
End Sub

[SIZE=11px]@Shazra626 just add PTRsafe to each declaration

Ex. [/SIZE][SIZE=11px]Private Declare Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long[/SIZE]
[SIZE=11px]becomes [/SIZE][SIZE=11px]Private Declare Ptrsafe Function SwitchDesktop Lib "user32"(ByVal hDesktop As Long) As Long[/SIZE]
[SIZE=11px]and it should be fine[/SIZE]

Not yet. Apparently they are doing maintenance and citrix is down.

Quote from KjBox

You also need to get rid of the empty row above each sub-total. Writing inefficient code to cater to a badly designed spreadsheet is very bad practice and very few forum members will do it.

Agree 100%, but when's the last time you saw a bank change their reporting because a contractor like me said their spreadsheet - which was built into their system by Crystal Reports - a third party - over a decade ago - is 'wrong' I will try to be a little clearer about that part of my request next time. Thank you!

Quote from jolivanes

If you change your setup to sheets without "Merged and Center" and "Wrap Text", someone might take the time to help but these two formats are a royal pain.
I had a look but got turned of by that.
Good luck

Hi jolivanes, I agree merged cells are evil. That's why I specifically mentioned I was receiving a Crystal Reports spreadsheet. SAP Crystal Reports is famous for exporting spreadsheet reports that break all the rules so thank you for answering anyway. Unfortunately, a lot of banks and other companies decided Crystal Reports was a good choice back in the 90's and are still using the original product and that's what I have to deal with here unfortunately. Thanks again for responding! I am going to take a look at your solution now. For some reason Ozgrid did not auto-email me your reply 4 days ago!... which is weird ... my email hasn't changed in years. cheers

I receive a Crystal Reports.xls file each day that contains a list of payments similar to the one shown in the picture below. I would like to loop through the file and append the name of the law firm, payment type and clerk name to the end of each payment record.

The client name and law firm name appear only once at the top of the report as shown in the picture'
The Payment type is either Non-Direct or Direct.
The clerk names can change all the time but they always precede an actual payment record(s)
I have attached a sample file as well. Any assistance appreciated!!

I receive a Crystal Reports.xls file each day that contains a list of payments similar to the one shown in the picture below. I would like to loop through the file and append the name of the law firm, payment type and clerk name to the end of each payment record.

The client name and law firm name appear only once at the top of the report as shown in the picture'
The Payment type is either Non-Direct or Direct.
The clerk names can change all the time but they always precede an actual payment record(s)
I have attached a sample file as well. Any assistance appreciated!!

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"OZ Payment file pic.JPG","data-attachmentid":1218219}[/ATTACH]

Hi Carim, you are correct, however, both processes will be occurring. Sometimes users will use the buttons to review offices sequentially, but at other times they may want to select offices at opposite ends of the list so they will skip the buttons and just select them manually.

Hi Carim, that's not quite what I'm looking for. My objective is to select the cell above, or below, the previously selected cell within the target range. It occurred to me that I might be able to do this by adding a line like [VBA]LastOffice = Target[/VBA] after [VBA]With Range("offices_tbl[office address]")[/VBA] in the Worksheet_SelectionChange event and I could then modify your code to be [VBA]LastOffice.Offset(1, 0).Select[/VBA] but it's not working. I tried making LastOffice global by adding [VBA]Global LastStation As String[/VBA] at the top of the module but that didn't help. Do you see what I'm getting at? Thanks again for your assistance!

Hi Carim, than you for replying. Unfortunately, your solution will move the active cell no matter where it is. I am trying to find a solution that will move the last selected cell in the target range Range("offices_tbl[office address]"). If there is no other method for identifying it, it could be identified by the fact that the last cell selected in the target range will be in italics and have an interior colour value of ".Color = 6750207" due to the Worksheet_SelectionChange event firing but I am hoping there is a simpler solution.

Hello Oz, I am looking for a VB script that will select/activate the cell below the last (most recently) selected cell in an excel table column.

My excel table is named "offices_tbl" and the column I want to work with is called "office address".

I.e. Range("offices_tbl[office address]")

I am already using the worksheet change event to perform an action when a cell is selected within the column, as shown below, but I would like to add buttons to the page so users can increment the selection up/down.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("offices_tbl[office address]")) Is Nothing Then 'for cells not selected:
Range("offices_tbl[office address]").Interior.ColorIndex = xlColorIndexNone ' set color to default/nothing

With Range("offices_tbl[office address]") 'when a cell is selected do this:
Range("F4").Value = ActiveCell.Value 'copy cell value to F4
With Selection.Interior 'change interior cell colour to
.Color = 6750207 'light yellow
End With
End With
Application.Run "StartJob1" 'and run Job1
End If
End Sub[/VBA]

Note: the user may have selected another cell in another column, so the current ActiveCell may not be within the target range when they press the button(s)!

As always, any help/tips to accomplish this greatly appreciated!!!

PS I am not looking for a spin button/toggle type solution at the moment, I have to use two buttons, one for up, one for down.

Thank you! Your solution worked perfectly!!

Greetings Oz'!

I have a script that changes a cell's interior (fill) color when the cell is selected using the SelectionChange event, if the selected cell lies within a specific range. It also copies the cell's value to another cell.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range
Const WS_RANGE As String = "G4:G50" 'target range

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target 'if user selects cell in target range
Range("F4").Value = ActiveCell.Value 'copy selected cell value to F4
With Selection.Interior 'and change fill colour of selected cell
.Color = 16777164
End With
End With
End If

End Sub[/VBA]

What I would like to figure out is how to change the selected cell's interior colour back to what it was when I click another cell in the targeted range such that only the currently selected cell is highlighted/filled. Any and all suggestions greatly appreciated!

I'm a little bit late to the party I guess but I suffer from the exact same issues to this day. Just curious if you ever found any solutions?

Carim, I uploaded the spreadsheet after your initial reply. Thanks!