[meatloaf]

Trying to loop through columns, vlookup the headers(symbols), find it in another workbook, and return the price

fbpos and fbml are spreadsheets in the active workbook HM. They have the price for the symbol by account#.
phmcpws is a spreadsheet that opens earlier in the code. The symbol headers are in row 3, which are also the variable that I am trying to vlookup in fbpos spreadsheet. The symbol is getting it's value from a different cell.

        Dim ticRange As Range, hmRange As Range, hmanRange As Range
        Dim ticStr As String, cStr As String
        Dim vTic As Variant
        Dim lastCol As Integer, tCol As Integer
                
        Set ticRange = phmcpws.Range("E3:N3", "R3:W3") 'symbol range in PH sheet
        Set hmRange = fbpos.Range("A:A", "G:I") 'acct# and symbol range in HM sheet
        Set hmanRange = hmRange.Find(What:=acctNum, LookIn:=xlValues, LookAt:=xlWhole) 'acct# filter
        
        lastCol = ticRange.Columns.Count
        
        For tCol = 1 To lastCol
            ticStr = ticRange.Cells(3, tCol).value
            If Not ticStr = "" Then
                vTic = Application.VLookup(ticStr, hmanRange, 2, False)
                If IsError(vTic) Or IsEmpty(vTic) Then 'error OR empty
                Else
                    cvStr = CStr(vTic)
                    phmcpws.Cells(4, tCol).value = cvStr
                End If
            End If
        Next tCol

[andrewsw]

You may need to fully qualify the VLOOKUP with Application.WorksheetFunction.Vlookup(); but VLOOKUPs second argument is a rectangular range, whereas your use of Find() will probably return a single cell.

It seems a little strange that you are using both Find() and VLOOKUP() - it would be more usual to use one or the other(?) approach.

[If you code produces errors then you should post the content of these errors, or at least indicate what result your code obtains.]

[andrewsw]

You are also looping through a number of columns, performing a VLOOKUP on each loop. I'm sure there is a much simpler approach. Perhaps if you describe clearly what you are attempting to do..

[meatloaf]

In fbpos spreadsheet: has a list of all accounts and the symbols that they hold, so the Find was to narrow down that I only want to look at on specific account for each symbol.

In phmcpws spreadsheet: each column is a symbol that I want to look through fbpos spreadsheet for and return the price, then go on to the next symbol in the next column in the array.
Also on this spreadsheet, and in the larger code, I'm looping the rows because in column B is the account number.

I hope that helps. Thanks for your quick response!

[andrewsw]

The column-count for:

phmcpws.Range("E3:N3", "R3:W3")

will be 19, not 16. The followings' column-count is 10 (the first area):

phmcpws.Range("E3:N3,R3:W3")

A much better way to loop through all these cells is:

Sub Blah()
    Dim rng As Range
    For Each rng In Range("E3:N3,R3:W3")
        rng.Value = 20
    Next rng
End Sub

meatloaf, on 15 March 2013 - 12:11 PM, said:

In fbpos spreadsheet: has a list of all accounts and the symbols that they hold, so the Find was to narrow down that I only want to look at on specific account for each symbol.

In phmcpws spreadsheet: each column is a symbol that I want to look through fbpos spreadsheet for and return the price, then go on to the next symbol in the next column in the array.
Also on this spreadsheet, and in the larger code, I'm looping the rows because in column B is the account number.

I hope that helps. Thanks for your quick response!

It would be much easier if you could post a cut-down (simple) version of the spreadsheet, puting sample data into different sheets of the same workbook. Otherwise you could describe in more details what cells I should fill in, and with what kinds of values to use.

[meatloaf]

Also

ticStr = ticRange.Cells(3, tCol).value

ticStr equals "", but it should be RSP. That cell is referencing cell "='Portfolios'!$A$8".

[andrewsw]

ticRange.Cells(3, tCol).value

..and ticRange is already at the third row, so using Cells(3, will probably look three columns to the right of where you think it is looking; it should be just .Cells(1,.

[meatloaf]

Here are snapshots of spreadsheet examples.

Attached image(s)

• 
• 

[meatloaf]

andrewsw, on 15 March 2013 - 03:18 PM, said:

ticRange.Cells(3, tCol).value

..and ticRange is already at the third row, so using Cells(3, will probably look three columns to the right of where you think it is looking; it should be just .Cells(1,.

O my goodness. That worked great!

It still doesn't like the hman range at all. Any suggestions on that, now that you can see how the spreadsheets are setup? hman is the range of a specific acct num range in fbpos, to find symbol in it.

[andrewsw]

You are not making this easy for me!

So, you are looking for an account number in column A of the first screenshot? Then reading the symbol in column G?
Then in the second screenshot you are finding the account number in column B and retrieving the value that would be in the symbol-column further to the right??

[meatloaf]

andrewsw, on 15 March 2013 - 03:55 PM, said:

You are not making this easy for me!

So, you are looking for an account number in column A of the first screenshot? Then reading the symbol in column G?
Then in the second screenshot you are finding the account number in column B and retrieving the value that would be in the symbol-column further to the right??

I'm sorry for being unclear.

I read account number in column B of phmcpws.
Then I loop through the symbols on that spreadsheet, by reading them, then finding the symbol (with the matching account number) in the fbpos spreadsheet.
If/Once I have found it, I get the market price of that row, and return it to the phmcpws spreadsheet in the cell account# and symbol.
Then I go to the next symbol, with the same account number.


I hope that made a little more sense. -Thanks!

[andrewsw]

.. or is it that you are looking for all symbols for a particular account-number, grabbing the value that is in column C, and want to then put this number in the second screenshot,in the corresponding cell??

[andrewsw]

Well I got it to work for me as far as my understanding of the question goes(?), but you'll need to decipher and adapt it.

Sub Blah()
    Dim phmcpws As Worksheet, fbpos As Worksheet
    Dim ticRange As Range, hmRange As Range
    Dim hmanRange As Range
    Dim acctNum As String
    Dim custRow As Integer
    Dim rng As Range, rng2 As Range
    
    Application.ScreenUpdating = False
    acctNum = "7170"
    
    Set phmcpws = Worksheets("phmcpws")
    Set fbpos = Worksheets("fbpos")
    
    Set ticRange = phmcpws.Range("E3:N3,R3:W3")
    'Set hmRange = fbpos.Range("A:A", "G:I")
    'create Defined Names based on column-headers:
    Application.DisplayAlerts = False
    fbpos.Range("A1").CurrentRegion.CreateNames True, False, False, False
    Application.DisplayAlerts = True
    Set hmRange = fbpos.Range("customer")
    'Set hmanRange = hmRange.Find(What:=acctNum, LookIn:=xlValues, LookAt:=xlWhole) 'acct# filter
    Set hmanRange = hmRange.Find(what:=acctNum, LookIn:=xlValues, lookat:=xlWhole)
    'MsgBox hmanRange.Address
    
    custRow = Application.Intersect(phmcpws.UsedRange, phmcpws.Columns(2)).Find(what:=acctNum).Row
    
    For Each rng In ticRange
        For Each rng2 In fbpos.Range("symbol")
            If rng2.Value = rng.Value And hmRange.Cells(rng2.Row) = acctNum Then
                rng.Offset(custRow - 3).Value = fbpos.Range("market_value").Cells(rng2.Row)
                Exit For
            End If
        Next rng2
    Next rng
    Application.ScreenUpdating = True
End Sub

This is in Excel VBA as well, so you'll need to modify it also for VB6.

It could be refined to look through only those cells from column A that contain the looked-for account number, but I decided just to look through all of the symbols each time, stopping if I find the a/c number in column A.

[andrewsw]

Again, if I've understood the question correctly, it is possible to achieve this with a single formula copied to the empty matrices in the second screenshot:

=SUMIFS(market_value,customer,$B4,symbol,E$3)

[meatloaf]

It's working great, except this bit of code in the for loops

phmcpws.Cells(intRow, rng1.Column).value = fbpos.Range("market_value").Cells(rng2.row)

rng2.row should be the row that it found the symbol and account# to match. The column is right, but it's taking the Range("market_value") of the cell in the row below the one wanted. Any thoughts as to why?

This post has been edited by meatloaf: 18 March 2013 - 08:40 AM