Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
G C G C is offline
external usenet poster
 
Posts: 6
Default 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





  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
G C G C is offline
external usenet poster
 
Posts: 6
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
G C G C is offline
external usenet poster
 
Posts: 6
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.programming
G C G C is offline
external usenet poster
 
Posts: 6
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
G C G C is offline
external usenet poster
 
Posts: 6
Default 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! :)
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP --- Assistance Needed Himansu Excel Programming 3 January 19th 09 06:36 PM
VBA Assistance Needed RalphB Excel Discussion (Misc queries) 5 February 22nd 06 07:16 PM
Coding assistance needed. fpd833 Excel Programming 1 November 17th 04 07:05 PM
Macro assistance needed HJ Excel Programming 3 November 2nd 04 11:46 PM
Assistance Needed with Comparing alexm999[_74_] Excel Programming 4 May 19th 04 10:04 PM


All times are GMT +1. The time now is 08:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"