By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,242 Members | 518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,242 IT Pros & Developers. It's quick & easy.

Excel 2000 VBA Matching Value problem

P: 63
I have a bit of visual basic code in an excel spreadsheet that I need some help with. I am attempting to search a file called TO Cancellations2.xls for counterparty names and for each counterparty name that it finds that matches the list of counterparties on the "counterparties" sheet of a file called Time Option Template WIP.xls I want the whole line copied and pasted into a new excel worksheet that should have the counterparty name and then FXTOX.xls after it.

The TO Cancellation2.xls file looks like this

Counterparty
Oy AB 109385 H2 07
Oy AB 109389 H1 08
Oy AB 109390 H2 08
Oy AB 109387 H2 07
Oy AB 109388 H1 08
AB (Sweden) 109391 H2 08

The code below works ok but if the TO Cancellations2.xls file has the same counterparty more than once (as in this case with 5 different records against Oy AB) then instead of saving all 5 records into one file it overwrites the file each time with each of these 5 instances - only keeping the last record. Can anyone suggest any ideas on how to alter this code so that if there is more than one row countaining the same counterparty name it saves all of the rows to the same file and doesn't keep overwriting like it is currently doing?


Sheets("Counterparties").Select

' Initialise Counter
Countz = 0
For Each c In Range("A1:A100")
If c.Value = "" Then
Exit For
Else
Countz = Countz + 1
End If
Next c

Sheets("Data2").Select (NB data2 is a sheet in the Time Option Template WIP.xls file)
Range("A1").Select

For Counter = 1 To Countz

Set Matching = Worksheets("Counterparties").Cells(Counter, 1)
Set Matching2 = Worksheets("Counterparties").Cells(Counter + 1, 1)
Set Matching3 = Worksheets("Counterparties").Cells(Counter + 2, 1)
Set Matching4 = Worksheets("Counterparties").Cells(Counter + 3, 1)
Set Matching5 = Worksheets("Counterparties").Cells(Counter + 4, 1)
Set Matching6 = Worksheets("Counterparties").Cells(Counter + 5, 1)
Set Matching7 = Worksheets("Counterparties").Cells(Counter + 6, 1)

For Each c In Range("A1:A5001")

If c.Value = Matching.Value Then
If c.Offset(0, 0).Value = Matching2.Value Or c.Offset(0, 0).Value = Matching3.Value Or c.Offset(0, 0).Value = Matching4.Value Or c.Offset(0, 0).Value = Matching5.Value Or c.Offset(0, 0).Value = Matching6.Value Or c.Offset(0, 0).Value = Matching7.Value Then
Else
c.EntireRow.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Cells(2, 1).Select
stdocname = "Q:\Dealing Room\Dealing room administration\Admin Things\Batch Report Masters\Outputs\" & Matching.Value & " FXTOX.xls"
ActiveWorkbook.SaveAs Filename:=stdocname _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("Time Option Template WIP.xls").Activate
stdocname = Matching.Value & " FXTOX.xls"
Counti = 2
Do Until c.Offset(Counti, 0).Value = Matching2 Or c.Offset(Counti, 0).Value = Matching3 Or c.Offset(Counti, 0).Value = Matching4 Or c.Offset(Counti, 0).Value = Matching5 Or c.Offset(Counti, 0).Value = Matching6 Or c.Offset(Counti, 0).Value = Matching7 Or c.Offset(Counti, 0).Value = "Contact RG"
c.Offset(Counti, 0).EntireRow.Select
Selection.Copy
Windows(stdocname).Activate
Cells(Counti + 1, 1).Select
ActiveSheet.Paste
Windows("Time Option Template WIP.xls").Activate
Counti = Counti + 1
Loop
Mar 24 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.