Need A VBA Code. Please Can Someone take a look and Help!!

[mbarnett]

Hi All,

I want to compare two string values in two different worksheets. Each string value has four other values I want to compare based on the string value. If I do not match on any of values of the string then I want the results (mismatching strings and the values) to be printed out on another sheet. I don’t want to use vlookup or sumif formulas because my criteria range is always changing. Here is an example of the data this is supposed to be the same on both worksheets:


String
Cusip Notional Mat Date Coupon Notional 2
999hf2 50000 2/1504 5.00 100000
999hf3 50000 2/1504 5.00 100000
999hf4 50000 2/1504 5.00 100000
999hf5 50000 2/1504 5.00 100000
999hf6 50000 2/1504 5.00 100000
999hf7 50000 2/1504 5.00 100000
999hf8 50000 2/1504 5.00 100000



Thanks
Mike

 

[PHV]

What have you so far ?
Keep in mind that Tek-Tips is NOT an help-desk.

 

[mbarnett]

Indeed...

Sub TobUpdateCompare()
Dim TradeType, JPMData As String
Dim i, x As Integer

Sheets("TOBReference&quot.Activate
'Loops through the trade types in order to set the formula for the valuation if range is greater than 200
'then increase to match data

For i = 6 To 200 Step 1

TradeType = ActiveSheet.Cells(i, "AQ&quot.Value

Select Case TradeType

Case "Muni Bond"
If Cells(i, "AZ&quot = "YES" Then
JPMData = "=SUMIF(BlueRiver!TOB_Close, RC[-6],BLUEPNL)"
Else
JPMData = "=SUMIF(TOBUnsettled!TOB_Close, RC[-6],TOBBLUEPNL)"
End If
Case "B Certificate"
If Cells(i, "AZ&quot = "YES" Then
JPMData = "=SUMIF(BlueRiver!BIG_Ref, RC[-6],BLUETOALVALUE)"
Else
JPMData = "=SUMIF(TOBUnsettled!BIG_Ref, RC[-6],TOBUNVALUE)"
End If
Case "Swap"
JPMData = "=VLOOKUP(RC[-6] & ""MIRS"" ,LiborHedges!MTM_Rec,4,false)"
Case "Swaption"
JPMData = "=IF(ISNA(VLOOKUP(RC[-6] & ""MSOP"" ,LiborHedges!MTM_Rec,4,false)),0,VLOOKUP(RC[-6] & ""MSOP"" ,LiborHedges!MTM_Rec,4,false))"
Case Else
JPMData = ""
End Select

ActiveSheet.Cells(i, "AX&quot.FormulaR1C1 = JPMData

Next i

ActiveSheet.Calculate

Range("AX6:AX65000&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveSheet.Calculate
Sheets("TOBReference&quot.Select
Range("AX182&quot.Value = 0

Calculate
Sheets("MainMenu&quot.Select

End Sub