VBA assistance needed
I have to reconcile my multiple bank account transactions with my system's compiled transactions and need the unique system id against that account no.
I've coded the same but 2 problem arise. 1. If bank transaction are out of my system transactions the system stop running and showing error "object not Found" instead of skipping transaction and moving to next transaction. 2. I have few similar(Amount) transactions in my 2 different accounts for which i need to run "findnext" command but its not working in this as well. I'm definitely mistaking somewhere pls guide. Coding:- Sub Reconciling() Dim Mycell As Range Dim Newrange As Range Dim sys_amt As Range Set sys_amt = Range("b2", Range("b2").End(xlDown)) Set Newrange = Range("f2", Range("f2").End(xlDown)) For Each Mycell In Newrange sys_amt.Find(Mycell).Select If ActiveCell.Offset(0, 1) = Mycell.Offset(0, 1) Then ActiveCell.Offset(0, -1).Copy Mycell.Offset(0, 2) Else MsgBox ("Not Showing") End If Next Mycell End Sub |
VBA assistance needed
Hi,
Am Fri, 6 May 2016 00:29:16 -0700 (PDT) schrieb G C: I have to reconcile my multiple bank account transactions with my system's compiled transactions and need the unique system id against that account no. I've coded the same but 2 problem arise. 1. If bank transaction are out of my system transactions the system stop running and showing error "object not Found" instead of skipping transaction and moving to next transaction. 2. I have few similar(Amount) transactions in my 2 different accounts for which i need to run "findnext" command but its not working in this as well. Try (unstested): Sub Reconciling() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Offset(, 2) = c.Offset(, -1) Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
VBA assistance needed
On Friday, May 6, 2016 at 1:15:30 PM UTC+5:30, Claus Busch wrote:
Hi, Am Fri, 6 May 2016 00:29:16 -0700 (PDT) schrieb G C: I have to reconcile my multiple bank account transactions with my system's compiled transactions and need the unique system id against that account no. I've coded the same but 2 problem arise. 1. If bank transaction are out of my system transactions the system stop running and showing error "object not Found" instead of skipping transaction and moving to next transaction. 2. I have few similar(Amount) transactions in my 2 different accounts for which i need to run "findnext" command but its not working in this as well. Try (unstested): Sub Reconciling() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Offset(, 2) = c.Offset(, -1) Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Thanks! for you prompt reply and knowledge. It surely gonna help me. |
VBA assistance needed
On Friday, May 6, 2016 at 1:15:30 PM UTC+5:30, Claus Busch wrote:
Hi, Am Fri, 6 May 2016 00:29:16 -0700 (PDT) schrieb G C: I have to reconcile my multiple bank account transactions with my system's compiled transactions and need the unique system id against that account no. I've coded the same but 2 problem arise. 1. If bank transaction are out of my system transactions the system stop running and showing error "object not Found" instead of skipping transaction and moving to next transaction. 2. I have few similar(Amount) transactions in my 2 different accounts for which i need to run "findnext" command but its not working in this as well. Try (unstested): Sub Reconciling() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Offset(, 2) = c.Offset(, -1) Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hello, Thanks for previous reply. I'm getting the id against the amount but the id is reflecting is system column, i need the same in bank column against bank transaction. Column (A) Has my System ID Column (B) Has my System amount Column (c) Has my System Account NO Column (D) Blank Column (E) Blank Column (F) Bank Amount Column (F) Bank Account No Column (E) This column need to be filled with system ID against the matching transactions. |
VBA assistance needed
On Friday, May 6, 2016 at 4:08:02 PM UTC+5:30, G C wrote:
On Friday, May 6, 2016 at 1:15:30 PM UTC+5:30, Claus Busch wrote: Hi, Am Fri, 6 May 2016 00:29:16 -0700 (PDT) schrieb G C: I have to reconcile my multiple bank account transactions with my system's compiled transactions and need the unique system id against that account no. I've coded the same but 2 problem arise. 1. If bank transaction are out of my system transactions the system stop running and showing error "object not Found" instead of skipping transaction and moving to next transaction. 2. I have few similar(Amount) transactions in my 2 different accounts for which i need to run "findnext" command but its not working in this as well. Try (unstested): Sub Reconciling() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do c.Offset(, 2) = c.Offset(, -1) Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hello, Thanks for previous reply. I'm getting the id against the amount but the id is reflecting is system column, i need the same in bank column against bank transaction. Column (A) Has my System ID Column (B) Has my System amount Column (c) Has my System Account NO Column (D) Blank Column (E) Blank Column (F) Bank Amount Column (G) Bank Account No Column (H) This column need to be filled with system ID against the matching transactions. Hello, Thanks for previous reply. I'm getting the id against the amount but the id is reflecting is system column, i need the same in bank column against bank transaction. Column (A) Has my System ID Column (B) Has my System amount Column (c) Has my System Account NO Column (D) Blank Column (E) Blank Column (F) Bank Amount Column (G) Bank Account No Column (H) This column need to be filled with system ID against the matching transactions |
VBA assistance needed
Hi,
Am Fri, 6 May 2016 03:43:21 -0700 (PDT) schrieb G C: I'm getting the id against the amount but the id is reflecting is system column, i need the same in bank column against bank transaction. try: Sub Reconciling2() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do If c.Offset(, 1) = Mycell.Offset(, 1) Then Mycell.Offset(, 2) = c.Offset(, -1) End If Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
VBA assistance needed
On Friday, May 6, 2016 at 5:21:56 PM UTC+5:30, Claus Busch wrote:
Hi, Am Fri, 6 May 2016 03:43:21 -0700 (PDT) schrieb G C: I'm getting the id against the amount but the id is reflecting is system column, i need the same in bank column against bank transaction. try: Sub Reconciling2() Dim Mycell As Range, c As Range Dim Newrange As Range, sys_amt As Range Dim FirstAddress As String Dim LRow As Long LRow = Cells(Rows.Count, 2).End(xlUp).Row Set sys_amt = Range("B2:B" & LRow) Set Newrange = Range("F2:F" & LRow) For Each Mycell In Newrange Set c = sys_amt.Find(Mycell, LookIn:=xlValues) If Not c Is Nothing Then FirstAddress = c.Address Do If c.Offset(, 1) = Mycell.Offset(, 1) Then Mycell.Offset(, 2) = c.Offset(, -1) End If Set c = sys_amt.FindNext(c) Loop While Not c Is Nothing And FirstAddress < c.Address End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Thanks Mr. Claus, Its Done! :) |
VBA assistance needed
Hi,
Am Fri, 6 May 2016 05:51:44 -0700 (PDT) schrieb G C: Its Done! :) you're welcome. Always glad to help. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
All times are GMT +1. The time now is 05:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com