Setting up Criteria for VBA Macros in Excel

Closed
Polo - May 24, 2010 at 12:51 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- May 25, 2010 at 05:23 PM
Hello,

I need to set up a loop function that will only output the data of the cheapest house, with a pool.


Here is what I have so far, but my code just seems to be outputting the cheapest house, instead of the cheapest house with a pool:

Sub CheapestHomeWithPool_Click()
Dim lowest, currentHomePrice, numBedrooms, sqFt As Double
Dim Pool As String

ActiveSheet.Range("A5").Select
If IsEmpty(ActiveCell) Then
MsgBox "No home info found"
Exit Sub
End If

lowest = ActiveCell.Value
numBedrooms = CDbl(ActiveCell.Offset(0, 1).Value)
sqFt = CDbl(ActiveCell.Offset(0, 3).Value)
Pool = ActiveCell.Offset(0, 5).Value

If Pool = "Yes" Then

Do
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If

currentHomePrice = CDbl(ActiveCell.Value)

If currentHomePrice < lowest Then
lowest = currentHomePrice
numBedrooms = CDbl(ActiveCell.Offset(0, 1).Value)
sqFt = CDbl(ActiveCell.Offset(0, 3).Value)
End If
Loop


MsgBox "The cheapest home with a pool is " & FormatCurrency(lowest, 0) & " with " & numBedrooms & " bedrooms and " & sqFt & " square feet."
End If


1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
May 25, 2010 at 05:23 PM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
0