VBA code to match and move value

[artz]

Hi,

In my workbook, I have a workheet named Download and another named Portfolio. On the Download worksheet, I can activate a webquery to download the latest prices for given stock. What I need is a VBA sub which will do the following:

Place the value contained in the last row of Download worksheet in column G, into the appropriate cell (row) in column K on the Portfolio worksheet. The appropriate row is determined by matching the value in M6 on the Download worksheet (stock symbol) to the row on the Portfolio worksheet containing the same symbol.

I'd like to place the code for this sub inside my Download() sub.

Can anyone in the Forum help with some VBA code to do this?

Thanks,

Art

 

[artz]

John,

Thanks for your comments. I replaced my hokey code with the following which works great:

Sub C_SS2()

    Dim LastDate As Variant
    Dim LastClose As Variant
    Dim LastHigh As Variant
    Dim LastLow As Variant
    Dim LastHH As Variant
    Dim LastLL As Variant
    Dim LastATR As Variant
    
    Dim stockSymbol As String
    Dim stockSymbolRange As Range
    
    With Sheets("Download")
    
        LastDate = Application.Lookup(9.999E+307, Sheets("Download").Columns("C"))
        LastClose = Application.Lookup(9.999E+307, Sheets("Download").Columns("G"))
        LastHigh = Application.Lookup(9.999E+307, Sheets("Download").Columns("E"))
        LastLow = Application.Lookup(9.999E+307, Sheets("Download").Columns("F"))
        LastHH = Application.Lookup(9.999E+307, Sheets("Download").Columns("N"))
        LastLL = Application.Lookup(9.999E+307, Sheets("Download").Columns("O"))
        LastATR = Application.Lookup(9.999E+307, Sheets("Download").Columns("T"))
        
    End With
    
    stockSymbol = Sheets("Download").Range("M6").Value
    
    With Sheets("Portfolio")
        Set stockSymbolRange = .Columns("B:B").Find(What:=stockSymbol, LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
        If Not stockSymbolRange Is Nothing Then
            .Cells(stockSymbolRange.Row, "K").Value = LastDate
            .Cells(stockSymbolRange.Row, "L").Value = LastClose
            .Cells(stockSymbolRange.Row, "M").Value = LastHigh
            .Cells(stockSymbolRange.Row, "N").Value = LastLow
            .Cells(stockSymbolRange.Row, "T").Value = LastHH
            .Cells(stockSymbolRange.Row, "U").Value = LastLL
            .Cells(stockSymbolRange.Row, "W").Value = LastATR
        Else
            MsgBox "Stock symbol " & stockSymbol & " not found in column B of Portfolio sheet"
        End If
        
    End With

End Sub

Art