Parkman
08-05-2013, 11:37 AM
I have a workbook called "FX" with individual sheets named after banks. ie: "bank1","bank2", etc
On each bank's worksheet, the top row of the first 6 colums is populated with each bank's term for a variable.
For example, the generic term "Rate" is defined by bank1 as "TradeRate", by bank2, as "SpotRate", etc.
So for bank1, cell ("A1"), the cell reserved for Rate, has "TradeRate" in it.
I have setup a userform where the user can select a bank.
Private Sub userform_initialize()
Dim cLoc As Range
Dim WS As Worksheet
Set WS = Worksheets("Banks")
For Each cLoc In WS.Range("BankList")
Me.cboBankList.AddItem cLoc.Value
Next cLoc
End Sub
Private Sub CommandButton1_Click()
Call GetFile
End Sub
''Subroutine to open a file search browser
''Sub GetFile()
''End Sub
Private Sub CommandButton2_Click()
Dim WSB1 As Worksheet
Dim WSB2 As Worksheet
Set WB = ThisWorkbook
Dim BankName As String
BankName = cboBankList.Value
wsb1name = BankName & "1"
wsb2name = BankName & "2"
Set WSB1 = Sheets(wsb1name)
Set WSB2 = Sheets(wsb2name)
WB.Activate
WSB1.Activate
Call TradeTerms
End Sub
WSB1 is (ie:worksheetbank1) the sheet which has the terms for each bank as described above.
Here is the TradeTerms sub, the part i need assistance with. My objective is to search a random worksheet, currently the activesheet, for bank1's definitition,ie: "TradeRate".
WBnew and WSnew are public variables, that were assigned values in a previous sub
Sub TradeTerms()
Dim Rate As String
Dim TradedAmt As String
Dim TradedCCT As String
Dim AgainstCCy As String
Dim ValueDate As String
Dim Entity As String
Dim BuySell As String
Dim Search As String
Rate = Range("A1").Value
' TradedAmt = Cell.Range(B1)
' TradedCCY = Cell.Range(C1)
' AgainstCCy = Cell.Range(D1)
' ValueDate = Cell.Range(E1)
' Entity = Cell.Range(F1)
' BuySell = Cell.Range(G1)
Workbooks(WBnew).Activate
Worksheets(WSnew).Activate
Search = Range("Search").Value
Cells.Select
Selection.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
This is what I have from googling other people's issues, it obviously doesnt work. Sorry if my question is vague, this is my first time posting and I just recently started learning VBA. Thanks for your patience and assistance!
On each bank's worksheet, the top row of the first 6 colums is populated with each bank's term for a variable.
For example, the generic term "Rate" is defined by bank1 as "TradeRate", by bank2, as "SpotRate", etc.
So for bank1, cell ("A1"), the cell reserved for Rate, has "TradeRate" in it.
I have setup a userform where the user can select a bank.
Private Sub userform_initialize()
Dim cLoc As Range
Dim WS As Worksheet
Set WS = Worksheets("Banks")
For Each cLoc In WS.Range("BankList")
Me.cboBankList.AddItem cLoc.Value
Next cLoc
End Sub
Private Sub CommandButton1_Click()
Call GetFile
End Sub
''Subroutine to open a file search browser
''Sub GetFile()
''End Sub
Private Sub CommandButton2_Click()
Dim WSB1 As Worksheet
Dim WSB2 As Worksheet
Set WB = ThisWorkbook
Dim BankName As String
BankName = cboBankList.Value
wsb1name = BankName & "1"
wsb2name = BankName & "2"
Set WSB1 = Sheets(wsb1name)
Set WSB2 = Sheets(wsb2name)
WB.Activate
WSB1.Activate
Call TradeTerms
End Sub
WSB1 is (ie:worksheetbank1) the sheet which has the terms for each bank as described above.
Here is the TradeTerms sub, the part i need assistance with. My objective is to search a random worksheet, currently the activesheet, for bank1's definitition,ie: "TradeRate".
WBnew and WSnew are public variables, that were assigned values in a previous sub
Sub TradeTerms()
Dim Rate As String
Dim TradedAmt As String
Dim TradedCCT As String
Dim AgainstCCy As String
Dim ValueDate As String
Dim Entity As String
Dim BuySell As String
Dim Search As String
Rate = Range("A1").Value
' TradedAmt = Cell.Range(B1)
' TradedCCY = Cell.Range(C1)
' AgainstCCy = Cell.Range(D1)
' ValueDate = Cell.Range(E1)
' Entity = Cell.Range(F1)
' BuySell = Cell.Range(G1)
Workbooks(WBnew).Activate
Worksheets(WSnew).Activate
Search = Range("Search").Value
Cells.Select
Selection.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
This is what I have from googling other people's issues, it obviously doesnt work. Sorry if my question is vague, this is my first time posting and I just recently started learning VBA. Thanks for your patience and assistance!