Filtering rates from rows

[Drewgarry007]

Hi there,
I have a list of exchange rates in column D in sheet1 and have a second list of exchange rates in column Q in sheet2 in the same workbook.

I need a vba code that will allow me to compare the two lists of rates and if there is a rate in column D sheet1 that does not exist in column Q sheet2 then I want to delete that entire row in sheet1.

Can you pls help? Is there some sort of loop or vlookup I can use?

Many thanks!

 

[Peter_SSs]

Is this getting too complicated?

I don't think so. I think the problem is most likely that I don't have your sheet layout quite correct. That is difficult because your tables do not identify rows/columns clearly (like my screen shot in post #6 for example).

In Sheet2, what column is the heading 'Currency1' in? (not Currency1Amount)
What column is the heading 'Currency2' in?

 

[Drewgarry007]

Hi Peter,

Yeah the layout is a bit werd in sheet2, becuse of the way I cut and pasted the origional data into excel from a text file.. there us a gap with ";" in between each column

Sheet2 colums are as follows:

<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=464 x:str><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=66>Currency1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=112>Currency1Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=66>Currency2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=112>Currency2Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=11>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>DealtRate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EUR</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="3025485.98">3,025,485.98</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">GBP</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="-2638284.28">-2,638,284.28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num="0.87202000000000002">0.87202</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EUR</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="-4200000">-4,200,000.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">GBP</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="3662778">3,662,778.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num="0.87209000000000003">0.87209</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17>AUD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 align=right x:num="544400">544,400.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 align=right x:num="-539881.48">-539,881.48</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.99170000000000003">0.9917</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 height=17>EUR</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28 align=right x:num="-6500000">-6,500,000.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>USD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28 align=right x:num="8848255">8,848,255.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26>;</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 align=right x:num="1.36127">1.36127</TD></TR></TBODY></TABLE>

Currecy1 = (col I)
Currency1Amount = (col K)
Currency2 = (col M)
Currency2Amount = (col O)
DealtRate = (col Q)

And yes col Q and R are free in sheet1. col R and S are free in sheet2

 

[Peter_SSs]

Well, that is the layout I have in Sheet2, so the next 'culprit' that I can think of is that what looks the same is not the same.

Take a Customer Rate value that the code fails for and look at the 'Currency1' value in that row on Sheet2 column I. Suppose it is cell I3 and its value is AUD. In a vacant cell on Sheet2 put this formula and check the result

=EXACT(I3,"AUD")


If it returns FALSE, then in other vacant cells (Sheet2 still) put these formulas and report the results

=LEN(I3)
=CODE(RIGHT(I3,1))

 

[Drewgarry007]

hey,

The below test came back as "true"

=EXACT(I2,"AUD")

have you an email address I caould email you over what I am looking at?

 

[Peter_SSs]

have you an email address I caould email you over what I am looking at?

Since this is a public forum, I would like to try to solve the issue in the public domain if possible.

The below test came back as "true"

=EXACT(I2,"AUD")

And, are you saying that if you take the DealtRate from Q2 on Sheet2 and look for its last value in column I on Sheet1, the result in column P of that row is an incorrect value?

Since it seems to be working for me, for a start, I suggest ..

1. Taking a new workbook and copying my data from below and pasting into the relevant sheets (the row numbers will get pasted as well but you can delete those and rearrange to get things in the correct column fairly easily).

2. Delete the data from columns P:R in Sheet1

3. Run the code below. It is the same as the previous code but with 2 lines commented out. This leaves formulas in columns P:R of Sheet1.

Do you get the same formulas and values as me?
If so, we have to start working out what is different between this data (that I copied from your earlier post) and your actual data.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Dealt CCY	Dealt Amt	Counter CCY	Counter Amt	Customer Rate							Check Totals
2	USD	-540000	AUD		0.960793								1	5
3	USD	-3260000	AUD		0.960793							OK	1	5
4	AUD	3170000	USD		0.961107								2	1
5	AUD	2620000	USD		0.961107								2	1
6	AUD	2540000	USD		0.961107								2	1
7	AUD	6520000	USD		0.961107								2	1
8	AUD	47510000	USD		0.961107								2	1
9	AUD	12440000	USD		0.961107								2	1
10	AUD	10000	USD		0.961107								2	1
11	AUD	190000	USD		0.961107							OK	2	1
Sheet1

Excel Workbook
	I	J	K	L	M	N	O	P	Q
1	Currency1		Currency1Amount		Currency2		Currency2Amount		DealtRate
2	AUD		3,955,066.28		USD		-3,800,000.00		0.960793
3	AUD		75,000,000.00		USD		-72,083,025.00		0.961107
Sheet2

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckTotals()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> frmlaP <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, frmlaQ <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, frmlaR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> frmlabaseP <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=IF(I2=I3,"""",IF(SUMIF(I$2:I2,I2,F$2:F2)" _<br>        & "=INDEX(Sheet2!K$2:O$#,Q2,R2),""OK"",""ERROR""))"<br>    <SPAN style="color:#00007F">Const</SPAN> frmlabaseQ <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=MATCH(I2,Sheet2!Q$2:Q$#,0)"<br>    <SPAN style="color:#00007F">Const</SPAN> frmlabaseR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=MATCH(E2,INDEX(Sheet2!I$2:M$#,Q2,0))"<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        lr2 = .Range("Q" & .Rows.Count).End(xlUp).Row<br>    End <SPAN style="color:#00007F">With</SPAN><br>    frmlaP = Replace(frmlabaseP, "#", lr2, 1, -1, 1)<br>    frmlaQ = Replace(frmlabaseQ, "#", lr2, 1, -1, 1)<br>    frmlaR = Replace(frmlabaseR, "#", lr2, 1, -1, 1)<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        lr1 = .Range("I" & .Rows.Count).<SPAN style="color:#00007F">End</SPAN>(xlUp).Row<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Range("P1").Value = "Check Totals"<br>        <SPAN style="color:#00007F">With</SPAN> .Range("P2:P" & lr1)<br>            .Offset(, 1).Formula = frmlaQ<br>            .Offset(, 2).Formula = frmlaR<br>            .Formula = frmlaP<br><SPAN style="color:#007F00">'            .Value = .Value</SPAN><br><SPAN style="color:#007F00">'            .Offset(, 1).Resize(, 2).ClearContents</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


If the problem still persists, if you could post a small section of sample data from each sheet for which the code is not working, it may help me track down the problem. Consider trying Excel jeanie since it is good for also showing rows/column/formulas. See my signature block for a link.

 

[Drewgarry007]

Hi Peter,

I have figured out what the issue is....

In the cells that you have blank in sheet2 J, L, N, P I have this ";"

I have manually deleted these out of the cells and your formula works!!... such a simple thing can cause so much grief HA!

Will have to now come up with a code to clean these out of sheet2 I guess?

 

[Peter_SSs]

I have manually deleted these out of the cells and your formula works!!... such a simple thing can cause so much grief HA!

Will have to now come up with a code to clean these out of sheet2 I guess?

You may wish to clean those out anyway, but you shouldn't need to since I actually had an error in one of my formulas - sorry about that.

Try making this change to the relevant code line and I think it should work with or without the ";" values.

Const frmlabaseR As String = "=MATCH(E2,INDEX(Sheet2!I$2:M$#,Q2,0),0)"

 

[Drewgarry007]

Hi Peter,

I have come acreoos a problem with the source data for sheet two which means when I copy and paste this data into sheet2 I have to text to columns using semicolon... this removes the ";" in between each column which means the above code no longer works... I have tried to re-gig it column "R" in sheet1 does not seem to work?

Here is my attemted amended code:


Dim lr1 As Long, lr2 As Long
Dim frmlaP As String, frmlaQ As String, frmlaR As String

Const frmlabaseP As String = "=IF(I2=I3,"""",IF(SUMIF(I$2:I2,I2,F$2:F2)" _
& "=INDEX(Sheet2!F$2:H$#,Q2,R2),""OK"",""ERROR""))"
Const frmlabaseQ As String = "=MATCH(I2,Sheet2!I$2:I$#,0)"
Const frmlabaseR As String = "=MATCH(E2,INDEX(Sheet2!E$2:G$#,Q2,0),0)"

With Sheets("Sheet2")
lr2 = .Range("I" & .Rows.Count).End(xlUp).Row
End With
frmlaP = Replace(frmlabaseP, "#", lr2, 1, -1, 1)
frmlaQ = Replace(frmlabaseQ, "#", lr2, 1, -1, 1)
frmlaR = Replace(frmlabaseR, "#", lr2, 1, -1, 1)
With Sheets("Sheet1")
lr1 = .Range("I" & .Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("P1").Value = "Check Totals"
With .Range("P2:P" & lr1)
.Offset(, 1).Formula = frmlaQ
.Offset(, 2).Formula = frmlaR
.Formula = frmlaP
' .Value = .Value
' .Offset(, 1).Resize(, 2).ClearContents
End With
Application.ScreenUpdating = True
End With

The only thing that has changed is there is no more ";" and the data is in different colums now ie

Currency1 = col E
Currency1Amount = F
Currency2 = G
Currency2Amount = H
Dealt rate = I

<TABLE style="WIDTH: 496pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=660 x:str><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=124 x:str=" Currency1 ">Currency1 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=145 x:str=" Currency1Amount ">Currency1Amount </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=124 x:str=" Currency2 ">Currency2 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=145 x:str=" Currency2Amount ">Currency2Amount </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=122 x:str=" DealtRate ">DealtRate </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" GBP ">GBP </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="3110000">3,110,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-4978687.04">-4,978,687.04</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.6008640000000001">1.600864</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" AUD ">AUD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-4560000">-4,560,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="4779842.16">4,779,842.16</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.048211">1.048211</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" EUR ">EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="4300000">4,300,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-6024988">-6,024,988.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.40116">1.40116</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" AUD ">AUD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-4220000">-4,220,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="4422302.58">4,422,302.58</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.047939">1.047939</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" GBP ">GBP </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="3190000">3,190,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-5101499.04">-5,101,499.04</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.599216">1.599216</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" EUR ">EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="2000000">2,000,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" GBP ">GBP </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-1750008">-1,750,008.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="0.875004">0.875004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" EUR ">EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="4240000">4,240,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-5931810.8799999999">-5,931,810.88</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.3990119999999999">1.399012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" EUR ">EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="2500000">2,500,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" USD ">USD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-3496905">-3,496,905.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.3987620000000001">1.398762</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=" EUR ">EUR </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="800000">800,000.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str=" AUD ">AUD </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num="-1062926.4">-1,062,926.40</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="1.3286579999999999">1.328658</TD></TR></TBODY></TABLE>

Was wondering if you can help me amend the code as I am getting these results is cols P,Q and R in sheet1 (looks like there is somthing wrong with col R fornula) but I cannot figue out what??:

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" height=17 width=128 colSpan=2>Check Totals</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=""></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>32</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=""></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>32</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>32</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 x:str=""></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>14</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 align=middle x:err="#N/A">#N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>17</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=middle x:err="#N/A">#N/A</TD></TR></TBODY></TABLE>


Pls help it driving me crazy!!

 

[Drewgarry007]

Does anyone know what is wrong with the about code??

 

[Peter_SSs]

When posting code, please post formatted (ie indented) code. Reading & de-bugging code that is all left-aligned is very difficult. See my signature block for ways to post vba code.

Are you sure you have described the columns for Sheet2 correctly and that Sheet1 columns haven't changed? I ask because I ran your code and it appears to work fine.

Here are my sheets after running your code exactly as posted in post #18.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Dealt CCY	Dealt Amt	Counter CCY	Counter Amt	Customer Rate							Check Totals
2	USD	-540000	AUD		0.960793								1	3
3	USD	-3260000	AUD		0.960793							OK	1	3
4	AUD	3170000	USD		0.961107								2	1
5	AUD	2620000	USD		0.961107								2	1
6	AUD	2540000	USD		0.961107								2	1
7	AUD	6520000	USD		0.961107								2	1
8	AUD	47510000	USD		0.961107								2	1
9	AUD	12440000	USD		0.961107								2	1
10	AUD	10000	USD		0.961107								2	1
11	AUD	190000	USD		0.961107							OK	2	1
Sheet1

Excel Workbook
	E	F	G	H	I
1	Currency1	Currency1Amount	Currency2	Currency2Amount	DealtRate
2	AUD	3,955,066.28	USD	-3,800,000.00	0.960793
3	AUD	75,000,000.00	USD	-72,083,025.00	0.961107
4
Sheet2

There is one very minor issue (that I had wrong in my earlier code) that could possibly place the heading "Check Totals" on the wrong sheet, but doesn't affect the formula results. There was a '.' missing from the beginning of this line of code

.Range("P1").Value = "Check Totals"

If you are still getting those #N/A values then, after running the code, please copy the formulas from cells P2, Q2 and R2 from Sheet1 and post them here.