VBA help-Look up previous day data and return result

Natalia · May 6, 2011

Hi,

I need your help with a worktask, not a big task. I have reconciliation reports which gets updated each day, so some entries will clear and some will stay and there will be new entries appearing. My team enter their names for the recs they are responsible for as shown in 050511 wkb. The next day when the new report is updated they would have to re-input their names in A6:A to their recs.

I need a macro that will compare current data to previous day if match found then return their names in Col A6:A. This will enable them to see which breaks haven't cleared.

Now there are two tabs "Cash" and "Asset", the asset tab is easy to do a lookup as there is a case number in E6:E, but for cash the macro will need to concatenate in VBA memory a few columns to create a unique ref. So to concatenate cash use B&C&D&E&F&G&H&I&L&M&N, this should do it.

050511 Workbook

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

5

Name

Group

Category

Set ID

Value Date

Entry Date

Type

Amount

CCY

Age

Source

Ref1

Ref2

Ref3

Reference

6

CITIBANK

CASH CITI

BNPPSITESAPP

2-Jun-08

2-Dec-09

SCR

4,417.80

GBP

1019

SMP1

FUNDS TFR

NATL WESTMINSTER BK PLC LONDON

SITE ROB GREGOR

7

JP MORGAN

CASH JP

YTYTTYTT

12-Aug-10

7-Nov-10

SCR

23,434.36

BNM

345

WER

YTYTY

244

544

8

CITIBANK

CASH CITI

BNPPSITESAPP

3-Jun-08

2-Dec-09

SCR

2,208.90

GBP

1018

SMP1

FUNDS TFR

CITIBANK NA LONDON

JEFFERY NEIL 3 GOLDERSL

9

CITIBANK

CASH CITI

WTSIE1GBPBNPP

20-Dec-10

SCR

5,834.29

GBP

88

SMP1

NONREF

SG1035400992OMTS

AMP CAPITAL INVESTORS

kjhjhjhjh

10

CITIBANK

CASH CITI

WTPE2AUSDBNPP

23-Dec-10

SCR

6,250.00

USD

85

SMP1

634

SG1035700026OMTS

CETERA BROKERS NETWORK, LLC

11

CITIBANK

CASH CITI

WTGDPFEURBNPP

24-Jan-11

SDR

-2,200,000.00

EUR

53

SMP1

FXWTDPFP22

SG1102400435OMTS

ALLEZ AND ASSOCIES SCP MARTIN

12

Cash

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

5

Name

Rec

Account

Source

Case No.

Value Date

Security Code

Description

Ccy Code

Ledg Stmt

Units

Exception

Age Break

6

JP MORGAN

CITA.CBAS

CIT1

234233

8-Apr-11

EWREWTEW

Test

NOR

Ledg

3,235,325.00

-12,155.00

21

7

BNPPINTL

BNPP.WTPE1A

JP1

773537

4-Apr-11

SOLCAUSD

SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A

USD

Ledg

1,003,682.00

-1,003,682.00

29

8

BNPPINTL

BNPP.WTPE1B

JP1

773538

4-Apr-11

SOLCAUSD

SOLYNDRA INC CONVERTIBLE NOTE TRANCHE A

USD

Ledg

177,120.00

-177,120.00

29

9

SMP

DIAT.DAI

JP1

773875

8-Apr-11

AU3CB0173953

MEDL 2011-1 A2 6.5PCT 22NOV42

Stmt

1,000,000.00

25

10

SMP

CITA.CBAS

JP1

774234

12-Apr-11

AU000000EQN4

EQUINOX MINERALS CDI NPV /ST/010

Stmt

59,782.00

21

11

SMP

CITA.CBAS

JP1

774235

12-Apr-11

AU000000CGG3

CITADEL RESOURCE GROUP LIMITED NPV /ST/010

Stmt

854,892.00

-854,892.00

21

12

SMP

NCSX.GESBE

JP1

774944

15-Apr-11

NCM

NEWCREST MINING LIMITED

AUD

Ledg

465,027.00

1,200.00

18

13

SMP

NCSX.GESBE

JP1

774944

15-Apr-11

AU000000NCM7

NEWCREST MINING LTD

AUD

Stmt

466,227.00

18

14

SMP

JPMT.WHEISS

JP1

775097

18-Apr-11

B1FYW63

GAGFAH SA

EUR

Ledg

8,547.00

-8,547.00

15

SMP

NCSX.CSL

JP1

775233

19-Apr-11

WHG

WHK GROUP LIMITED

AUD

Ledg

80,578.00

-80,578.00

14

16

SMP

NCSX.GESS

JP1

775232

19-Apr-11

WHG

WHK GROUP LIMITED

AUD

Ledg

126,002.00

-126,002.00

14

17

SMP

NCSX.GESS

JP1

151511

19-Apr-11

WHG

WHK GROUP LIMITED

AUD

Ledg

126,002.00

-54,545.32

14

Asset

Natalia · May 6, 2011

*bump*

Natalia · May 6, 2011

*bump*

Natalia · May 6, 2011

*bump*

Natalia · May 7, 2011

*bump*

Natalia · May 7, 2011

*bump*

bertie · May 7, 2011

Hi Nat, thanks for the pm.

I would place this code in a module. This can be exported to your hard drive and imported into each new workbook when needed.

I have used Application.GetOpenFilename to allow the user to select the file for comparison.

Here are the range variables I have used:

Code:

[color=green]'============================================[/color]
   [color=green]'set up the worksheet ranges to loop through[/color]
   [color=green]'============================================[/color]
   [color=darkblue]Set[/color] rngAssetNew = Sheets("Asset").Range("B6")
   [color=darkblue]Set[/color] rngCashNew = Sheets("Cash").Range("B6")
   
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [color=darkblue]If[/color] FileToOpen <> [color=darkblue]False[/color] [color=darkblue]Then[/color]
      [color=darkblue]Set[/color] wbOld = Workbooks.Open(FileToOpen)
      [color=darkblue]Set[/color] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      [color=darkblue]Set[/color] rngCashOld = wbOld.Sheets("Cash").Range("B6")
   [color=darkblue]Else[/color]
      MsgBox "No file Selected"
      [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
   [color=darkblue]End[/color] [color=darkblue]If[/color]

The code then loops through both ranges, builds up the string for comparison and updates the name when a match is found.

Code:

   [color=green]'===============================[/color]
   [color=green]'asset worksheet[/color]
   [color=green]'==============================[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetNew = ""
      [color=green]'build up the string[/color]
      [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] 13
         MatchNew = MatchNew & Trim(rngAssetNew.Offset(0, i - 2))
      [color=darkblue]Next[/color] i
      
      [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetOld = ""
         [color=green]'build up the string[/color]
         [color=darkblue]For[/color] j = 2 [color=darkblue]To[/color] 13
            MatchOld = MatchOld & Trim(rngAssetOld.Offset(0, j - 2))
         [color=darkblue]Next[/color] j
      
         [color=darkblue]If[/color] MatchOld = MatchNew [color=darkblue]Then[/color]
            rngAssetNew.Offset(0, -1) = rngAssetOld.Offset(0, -1)
            [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]

The code to handle the cash sheet is pretty much the same.

Here is the full code:

Code:

[color=darkblue]Sub[/color] CompareStrings()
   [color=darkblue]Dim[/color] wbOld [color=darkblue]As[/color] Workbook
   [color=darkblue]Dim[/color] wbNew [color=darkblue]As[/color] Workbook
   [color=darkblue]Dim[/color] rngAssetOld As Range
   [color=darkblue]Dim[/color] rng[color=darkblue]As[/color]setNew [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] rngCashOld [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] rngCashNew [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] FileToOpen
   [color=darkblue]Dim[/color] MatchOld [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] MatchNew [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] i As [color=darkblue]Integer[/color], j As [color=darkblue]Integer[/color]   [color=green]'loop index'[/color]
   
   [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] errCompareStrings
   
   [color=green]'============================================[/color]
   [color=green]'set up the worksheet ranges to loop through[/color]
   [color=green]'============================================[/color]
   [color=darkblue]Set[/color] rngAssetNew = Sheets("Asset").Range("B6")
   [color=darkblue]Set[/color] rngCashNew = Sheets("Cash").Range("B6")
   
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [color=darkblue]If[/color] FileToOpen <> [color=darkblue]False[/color] [color=darkblue]Then[/color]
      [color=darkblue]Set[/color] wbOld = Workbooks.Open(FileToOpen)
      [color=darkblue]Set[/color] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      [color=darkblue]Set[/color] rngCashOld = wbOld.Sheets("Cash").Range("B6")
   [color=darkblue]Else[/color]
      MsgBox "No file Selected"
      [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
   [color=darkblue]End[/color] [color=darkblue]If[/color]
   
   [color=green]'===============================[/color]
   [color=green]'asset worksheet[/color]
   [color=green]'==============================[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetNew = ""
      [color=green]'build up the string[/color]
      [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] 13
         MatchNew = MatchNew & Trim(rngAssetNew.Offset(0, i - 2))
      [color=darkblue]Next[/color] i
      
      [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetOld = ""
         [color=green]'build up the string[/color]
         [color=darkblue]For[/color] j = 2 [color=darkblue]To[/color] 13
            MatchOld = MatchOld & Trim(rngAssetOld.Offset(0, j - 2))
         [color=darkblue]Next[/color] j
      
         [color=darkblue]If[/color] MatchOld = MatchNew [color=darkblue]Then[/color]
            rngAssetNew.Offset(0, -1) = rngAssetOld.Offset(0, -1)
            [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]
         
         MatchOld = ""
         [color=darkblue]Set[/color] rngAssetOld = rngAssetOld.Offset(1, 0)
      [color=darkblue]Loop[/color]
      
      [color=darkblue]Set[/color] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      MatchNew = ""
      [color=darkblue]Set[/color] rngAssetNew = rngAssetNew.Offset(1, 0)
   [color=darkblue]Loop[/color]
   
   [color=green]'===============================[/color]
   [color=green]'cash worksheet[/color]
   [color=green]'==============================[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngCashNew = ""
      [color=green]'build up the string[/color]
      [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] 13
         MatchNew = MatchNew & Trim(rngCashNew.Offset(0, i - 2))
      [color=darkblue]Next[/color] i
      
      [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngCashOld = ""
         [color=green]'build up the string[/color]
         [color=darkblue]For[/color] j = 2 [color=darkblue]To[/color] 13
            MatchOld = MatchOld & Trim(rngCashOld.Offset(0, j - 2))
         [color=darkblue]Next[/color] j
      
         [color=darkblue]If[/color] MatchOld = MatchNew [color=darkblue]Then[/color]
            rngCashNew.Offset(0, -1) = rngCashOld.Offset(0, -1)
            [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]
         
         MatchOld = ""
         [color=darkblue]Set[/color] rngCashOld = rngCashOld.Offset(1, 0)
      [color=darkblue]Loop[/color]
      
      [color=darkblue]Set[/color] rngCashOld = wbOld.Sheets("Cash").Range("B6")
      MatchNew = ""
      [color=darkblue]Set[/color] rngCashNew = rngCashNew.Offset(1, 0)
   [color=darkblue]Loop[/color]
   
   
exitCompareStrings:
   [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
   
  [color=green]' wbOld.Close SaveChanges:=False[/color]
   [color=darkblue]Set[/color] wbOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngAssetNew = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngCashNew = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngAssetOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngCashOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
errCompareStrings:
   MsgBox "Error: " & Err.Number & vbCrLf & _
         "Description: " & Err.Description
   [color=darkblue]Resume[/color] exitCompareStrings:
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Edit: ps I have commented out a line to close the workbook which was opened.
Bertie

Natalia · May 7, 2011

Hi Bertie, i did a test and found the code does not return matched data for asset. For Asset the code only needs to lookup E6:E since the case number is sufficient to perform a lookup.

For cash i dont want the code to look at Age (Col J) and Source (Col K) as these variable will be changing constantly from day to day, whereas the other variables are fixed.

bertie said:

Hi Nat, thanks for the pm.

I would place this code in a module. This can be exported to your hard drive and imported into each new workbook when needed.

I have used Application.GetOpenFilename to allow the user to select the file for comparison.

Here are the range variables I have used:

Code:

[COLOR=green]'============================================[/COLOR]
   [COLOR=green]'set up the worksheet ranges to loop through[/COLOR]
   [COLOR=green]'============================================[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetNew = Sheets("Asset").Range("B6")
   [COLOR=darkblue]Set[/COLOR] rngCashNew = Sheets("Cash").Range("B6")
   
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [COLOR=darkblue]If[/COLOR] FileToOpen <> [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wbOld = Workbooks.Open(FileToOpen)
      [COLOR=darkblue]Set[/COLOR] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      [COLOR=darkblue]Set[/COLOR] rngCashOld = wbOld.Sheets("Cash").Range("B6")
   [COLOR=darkblue]Else[/COLOR]
      MsgBox "No file Selected"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

The code then loops through both ranges, builds up the string for comparison and updates the name when a match is found.

Code:

   [COLOR=green]'===============================[/COLOR]
   [COLOR=green]'asset worksheet[/COLOR]
   [COLOR=green]'==============================[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngAssetNew = ""
      [COLOR=green]'build up the string[/COLOR]
      [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] 13
         MatchNew = MatchNew & Trim(rngAssetNew.Offset(0, i - 2))
      [COLOR=darkblue]Next[/COLOR] i
      
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngAssetOld = ""
         [COLOR=green]'build up the string[/COLOR]
         [COLOR=darkblue]For[/COLOR] j = 2 [COLOR=darkblue]To[/COLOR] 13
            MatchOld = MatchOld & Trim(rngAssetOld.Offset(0, j - 2))
         [COLOR=darkblue]Next[/COLOR] j
      
         [COLOR=darkblue]If[/COLOR] MatchOld = MatchNew [COLOR=darkblue]Then[/COLOR]
            rngAssetNew.Offset(0, -1) = rngAssetOld.Offset(0, -1)
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

The code to handle the cash sheet is pretty much the same.

bertie · May 7, 2011

Hi Nat,
I have made the highlighted changes to the code. Try this.

Code:

[color=darkblue]Sub[/color] CompareStrings()
   [color=darkblue]Dim[/color] wbOld [color=darkblue]As[/color] Workbook
   [color=darkblue]Dim[/color] wbNew [color=darkblue]As[/color] Workbook
   [color=darkblue]Dim[/color] rngAssetOld As Range
   [color=darkblue]Dim[/color] rng[color=darkblue]As[/color]setNew [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] rngCashOld [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] rngCashNew [color=darkblue]As[/color] Range
   [color=darkblue]Dim[/color] FileToOpen
   [color=darkblue]Dim[/color] MatchOld [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] MatchNew [color=darkblue]As[/color] [color=darkblue]String[/color]
   [color=darkblue]Dim[/color] i As [color=darkblue]Integer[/color], j As [color=darkblue]Integer[/color]   [color=green]'loop index'[/color]
   
   [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] errCompareStrings
   
   [color=green]'============================================[/color]
   [color=green]'set up the worksheet ranges to loop through[/color]
   [color=green]'============================================[/color]
   [color=darkblue]Set[/color] rngAssetNew = Sheets("Asset").Range("B6")
   [color=darkblue]Set[/color] rngCashNew = Sheets("Cash").Range("B6")
   
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [color=darkblue]If[/color] FileToOpen <> [color=darkblue]False[/color] [color=darkblue]Then[/color]
      [color=darkblue]Set[/color] wbOld = Workbooks.Open(FileToOpen)
      [color=darkblue]Set[/color] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      [color=darkblue]Set[/color] rngCashOld = wbOld.Sheets("Cash").Range("B6")
   [color=darkblue]Else[/color]
      MsgBox "No file Selected"
      [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
   [color=darkblue]End[/color] [color=darkblue]If[/color]
   
   [color=green]'================[/color]
   [color=green]'asset worksheet[/color]
   [color=green]'================[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetNew = ""
      [COLOR="Red"]MatchNew = rngAssetNew.Offset(0, 3).Value[/COLOR]
      
      [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngAssetOld = ""
         [COLOR="Red"]MatchOld = rngAssetOld.Offset(0, 3).Value[/COLOR]
         
         [color=darkblue]If[/color] MatchOld = MatchNew [color=darkblue]Then[/color]
            rngAssetNew.Offset(0, -1) = rngAssetOld.Offset(0, -1)
            [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]
         
         MatchOld = ""
         [color=darkblue]Set[/color] rngAssetOld = rngAssetOld.Offset(1, 0)
      [color=darkblue]Loop[/color]
      
      [color=darkblue]Set[/color] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      MatchNew = ""
      [color=darkblue]Set[/color] rngAssetNew = rngAssetNew.Offset(1, 0)
   [color=darkblue]Loop[/color]
Stop
   [color=green]'===============================[/color]
   [color=green]'cash worksheet[/color]
   [color=green]'==============================[/color]
   [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngCashNew = ""
      [color=green]'build up the string[/color]
[COLOR="Red"]      MatchNew = rngCashNew.Value & _
               rngCashNew.Offset(, 1).Value & _
               rngCashNew.Offset(, 2).Value & _
               rngCashNew.Offset(, 3).Value & _
               rngCashNew.Offset(, 4).Value & _
               rngCashNew.Offset(, 5).Value & _
               rngCashNew.Offset(, 6).Value & _
               rngCashNew.Offset(, 7).Value & _
               rngCashNew.Offset(, 10).Value & _
               rngCashNew.Offset(, 11).Value & _
               rngCashNew.Offset(, 12).Value[/COLOR]
     
      [color=darkblue]Do[/color] [color=darkblue]Until[/color] rngCashOld = ""
         [color=green]'build up the string[/color]
   [COLOR="red"]      MatchOld = rngCashOld.Value & _
                  rngCashOld.Offset(, 1).Value & _
                  rngCashOld.Offset(, 2).Value & _
                  rngCashOld.Offset(, 3).Value & _
                  rngCashOld.Offset(, 4).Value & _
                  rngCashOld.Offset(, 5).Value & _
                  rngCashOld.Offset(, 6).Value & _
                  rngCashOld.Offset(, 7).Value & _
                  rngCashOld.Offset(, 10).Value & _
                  rngCashOld.Offset(, 11).Value & _
                  rngCashOld.Offset(, 12).Value[/COLOR]

         [color=darkblue]If[/color] MatchOld = MatchNew [color=darkblue]Then[/color]
            rngCashNew.Offset(0, -1) = rngCashOld.Offset(0, -1)
            [color=darkblue]Exit[/color] [color=darkblue]Do[/color]
         [color=darkblue]End[/color] [color=darkblue]If[/color]
         
         MatchOld = ""
         [color=darkblue]Set[/color] rngCashOld = rngCashOld.Offset(1, 0)
      [color=darkblue]Loop[/color]
      
      [color=darkblue]Set[/color] rngCashOld = wbOld.Sheets("Cash").Range("B6")
      MatchNew = ""
      [color=darkblue]Set[/color] rngCashNew = rngCashNew.Offset(1, 0)
   [color=darkblue]Loop[/color]
   
   
exitCompareStrings:
   [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
   
  [color=green]' wbOld.Close SaveChanges:=False[/color]
   [color=darkblue]Set[/color] wbOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngAssetNew = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngCashNew = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngAssetOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Set[/color] rngCashOld = [color=darkblue]Nothing[/color]
   [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
errCompareStrings:
   MsgBox "Error: " & Err.Number & vbCrLf & _
         "Description: " & Err.Description
   [color=darkblue]Resume[/color] exitCompareStrings:
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Natalia · May 7, 2011

Thanks Bertie, that worked on the test data, but could you explain what this does? On my real data the code debugs on this line.

Do Until rngAssetNew = ""

bertie said:

Hi Nat,
I have made the highlighted changes to the code. Try this.

Code:

[COLOR=darkblue]Sub[/COLOR] CompareStrings()
   [COLOR=darkblue]Dim[/COLOR] wbOld [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] wbNew [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] rngAssetOld As Range
   [COLOR=darkblue]Dim[/COLOR] rng[COLOR=darkblue]As[/COLOR]setNew [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rngCashOld [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] rngCashNew [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] FileToOpen
   [COLOR=darkblue]Dim[/COLOR] MatchOld [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] MatchNew [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i As [COLOR=darkblue]Integer[/COLOR], j As [COLOR=darkblue]Integer[/COLOR]   [COLOR=green]'loop index'[/COLOR]
   
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errCompareStrings
   
   [COLOR=green]'============================================[/COLOR]
   [COLOR=green]'set up the worksheet ranges to loop through[/COLOR]
   [COLOR=green]'============================================[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetNew = Sheets("Asset").Range("B6")
   [COLOR=darkblue]Set[/COLOR] rngCashNew = Sheets("Cash").Range("B6")
   
   FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
   [COLOR=darkblue]If[/COLOR] FileToOpen <> [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wbOld = Workbooks.Open(FileToOpen)
      [COLOR=darkblue]Set[/COLOR] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      [COLOR=darkblue]Set[/COLOR] rngCashOld = wbOld.Sheets("Cash").Range("B6")
   [COLOR=darkblue]Else[/COLOR]
      MsgBox "No file Selected"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'================[/COLOR]
   [COLOR=green]'asset worksheet[/COLOR]
   [COLOR=green]'================[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngAssetNew = ""
      [COLOR=Red]MatchNew = rngAssetNew.Offset(0, 3).Value[/COLOR]
      
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngAssetOld = ""
         [COLOR=Red]MatchOld = rngAssetOld.Offset(0, 3).Value[/COLOR]
         
         [COLOR=darkblue]If[/COLOR] MatchOld = MatchNew [COLOR=darkblue]Then[/COLOR]
            rngAssetNew.Offset(0, -1) = rngAssetOld.Offset(0, -1)
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         
         MatchOld = ""
         [COLOR=darkblue]Set[/COLOR] rngAssetOld = rngAssetOld.Offset(1, 0)
      [COLOR=darkblue]Loop[/COLOR]
      
      [COLOR=darkblue]Set[/COLOR] rngAssetOld = wbOld.Sheets("Asset").Range("B6")
      MatchNew = ""
      [COLOR=darkblue]Set[/COLOR] rngAssetNew = rngAssetNew.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
Stop
   [COLOR=green]'===============================[/COLOR]
   [COLOR=green]'cash worksheet[/COLOR]
   [COLOR=green]'==============================[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngCashNew = ""
      [COLOR=green]'build up the string[/COLOR]
[COLOR=Red]      MatchNew = rngCashNew.Value & _
               rngCashNew.Offset(, 1).Value & _
               rngCashNew.Offset(, 2).Value & _
               rngCashNew.Offset(, 3).Value & _
               rngCashNew.Offset(, 4).Value & _
               rngCashNew.Offset(, 5).Value & _
               rngCashNew.Offset(, 6).Value & _
               rngCashNew.Offset(, 7).Value & _
               rngCashNew.Offset(, 10).Value & _
               rngCashNew.Offset(, 11).Value & _
               rngCashNew.Offset(, 12).Value[/COLOR]
     
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngCashOld = ""
         [COLOR=green]'build up the string[/COLOR]
   [COLOR=red]      MatchOld = rngCashOld.Value & _
                  rngCashOld.Offset(, 1).Value & _
                  rngCashOld.Offset(, 2).Value & _
                  rngCashOld.Offset(, 3).Value & _
                  rngCashOld.Offset(, 4).Value & _
                  rngCashOld.Offset(, 5).Value & _
                  rngCashOld.Offset(, 6).Value & _
                  rngCashOld.Offset(, 7).Value & _
                  rngCashOld.Offset(, 10).Value & _
                  rngCashOld.Offset(, 11).Value & _
                  rngCashOld.Offset(, 12).Value[/COLOR]

         [COLOR=darkblue]If[/COLOR] MatchOld = MatchNew [COLOR=darkblue]Then[/COLOR]
            rngCashNew.Offset(0, -1) = rngCashOld.Offset(0, -1)
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
         
         MatchOld = ""
         [COLOR=darkblue]Set[/COLOR] rngCashOld = rngCashOld.Offset(1, 0)
      [COLOR=darkblue]Loop[/COLOR]
      
      [COLOR=darkblue]Set[/COLOR] rngCashOld = wbOld.Sheets("Cash").Range("B6")
      MatchNew = ""
      [COLOR=darkblue]Set[/COLOR] rngCashNew = rngCashNew.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
   
   
exitCompareStrings:
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
   
  [COLOR=green]' wbOld.Close SaveChanges:=False[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wbOld = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngCashNew = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngAssetOld = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngCashOld = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errCompareStrings:
   MsgBox "Error: " & Err.Number & vbCrLf & _
         "Description: " & Err.Description
   [COLOR=darkblue]Resume[/COLOR] exitCompareStrings:
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

VBA help-Look up previous day data and return result

Natalia

Board Regular

Excel Facts

Natalia

Board Regular

Natalia

Board Regular

Natalia

Board Regular

Natalia

Board Regular

Natalia

Board Regular

bertie

Well-known Member

Natalia

Board Regular

bertie

Well-known Member

Natalia

Board Regular

Forum statistics

Share this page

VBA help-Look up previous day data and return result

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock