|
-
Oct 6th, 2010, 01:12 AM
#1
Thread Starter
Hyperactive Member
[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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|