Results 1 to 1 of 1

Thread: [RESOLVED] Excel VBA Find functions

Threaded View

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Resolved [RESOLVED] Excel VBA Find functions

    I have a spread sheet setup with 2 columns. The first column is range("B3:B26") and it contains Hours for a 24 hr period (just for 1-24). The Second Column is range("C3:C26") which are the prices that correlate to the hour in the "B" cells.
    I am trying to setup the spreadsheet to take all the prices for the 24 hour period, find the average, the Low hourly price, and the High hourly price (I am currently doing this with the basic excel functions average, min, and max). Currently I have the, in this order, Avg/High/Low functions in cells "C27:C29"
    The problem that I am having is that for the High/Low prices I also need to have the hour that correlates to the high/low price show up in the cell next to the high/low price in cells "G6 & G7"

    The following code gives me a runtime error '91':
    Object variable or with block variable not set:
    Code:
    Sub Match_Hrs()
        
        Dim h As Currency
        h = Range("C28").Value
        Dim l As Currency
        l = Range("C29").Value
        
         Range("A1").Select
        
        Cells.Find(What:=h, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "High $"
        ActiveCell.Offset(0, -1).Range("A1").Copy
        Range("D28").Select
        ActiveSheet.Paste
        
        Range("A1").Select
            
        Cells.Find(What:=l, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Range("A1").FormulaR1C1 = "Low $"
        ActiveCell.Offset(0, -1).Range("A1").Copy
        Range("D29").Select
        ActiveSheet.Paste
        
        Range("A1").Select
                
    End Sub
    And with the following code I can get it to identify the cell that matches the Highprice, but I just don't know how to make it do want I want.
    Code:
    Sub match2()
        
        Dim h As Currency
        h = Range("C28").Value
        
        For Each c In [C3:C26]
            If c.Value Like h Then c.Font.Bold = True
        Next
            
        
    End Sub
    Help Please!!!
    Last edited by nO_OnE; Oct 6th, 2010 at 01:38 AM. Reason: Code Correction

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width