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:
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 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
Help Please!!!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




Reply With Quote