PDA

View Full Version : VBA Varible IF Statement



cdiable
08-09-2016, 10:31 AM
i am trying to get the following macro to work. I am thinking I have the wrong syntax somewhere. i keep trying different things and getting one of two errors, either data mismatch or syntax error. below is my macro. Any help would be greatly appreciated.


Sub TABLE_SEARCH()

Dim LISTINGSSHEET As Worksheet
Set LISTINGSSHEET = ThisWorkbook.Sheets("LISTINGS")

LR = LISTINGSSHEET.Cells(Rows.Count, 1).End(xlUp).Row

Dim SEARCHSHEET As Worksheet
Set SEARCHSHEET = ThisWorkbook.Sheets("SEARCH")

SEARCHSHEET.Range("A4:Z9999").ClearContents
SEARCHSHEET.Range("A4:Z9999").Borders.LineStyle = xlNone

Dim A As Byte
Dim B As Byte
Dim C As Byte
Dim D As Byte
Dim E As Byte
Dim F As Byte
Dim G As Byte
Dim H As Byte
Dim I As Byte
Dim J As Byte
Dim K As Byte
Dim L As Byte
Dim M As Byte
Dim N As Byte
Dim O As Byte
Dim P As Byte
Dim Q As Byte
Dim R As Byte
Dim S As Byte
Dim T As Byte
Dim U As Byte
Dim V As Byte
Dim W As Byte
Dim X As Byte
Dim Y As Byte
Dim Z As Byte


'LAND OR RESIDENT
LANDRES = SEARCHSHEET.Cells(2, 1)
If LANDRES = "" Then
A = [0=0]
Else
A =
[LISTINGSSHEET.Cells(X, 1) = LANDRES]
End If

'STATUS
STAT = SEARCHSHEET.Cells(2, 2)
If STAT = "" Then
B = [0=0]
Else
B =
[LISTINGSSHEET.Cells(X, 2) = STAT]
End If

'PROPERTY LOCATION
PROPERTY = SEARCHSHEET.Cells(2, 3)
If PROPERTY = "" Then
C = [0=0]
Else
C =
[LISTINGSSHEET.Cells(X, 3) = PROPERTY]
End If

'TOWN
TOWN = SEARCHSHEET.Cells(2, 4)
If TOWN = "" Then
D = [0=0]
Else
D =
[LISTINGSSHEET.Cells(X, 4) = TOWN]
End If

'MLS SYSTEM NUMBER
MLS = SEARCHSHEET.Cells(2, 5)
If MLS = "" Then
E = [0=0]
Else
E =
[LISTINGSSHEET.Cells(X, 5) = MLS]
End If

'PRICE MIN
PRICEMIN = SEARCHSHEET.Cells(1, 6)
If SEARCHSHEET.Cells(1, 6) = "" Then
PRICEMIN = 0
End If
F =
[LISTINGSSHEET.Cells(X, 6) >= PRICEMIN]

'PRICE MAX
PRICEMAX = SEARCHSHEET.Cells(2, 6)
If SEARCHSHEET.Cells(2, 6) = "" Then
PRICEMAX = 999999999
End If
G =
[LISTINGSSHEET.Cells(X, 6) <= PRICEMAX]

'ACRE MIN
ACREMIN = SEARCHSHEET.Cells(1, 7)
If SEARCHSHEET.Cells(1, 7) = "" Then
ACREMIN = 0
End If
H =
[LISTINGSSHEET.Cells(X, 7) >= ACREMIN]

'ACRE MAX
ACREMAX = SEARCHSHEET.Cells(2, 7)
If SEARCHSHEET.Cells(2, 7) = "" Then
ACREMAX = 999999999
End If
I =
[LISTINGSSHEET.Cells(X, 7) <= ACREMAX]

'SITE BUILT OR FACTORY BULT
SF = SEARCHSHEET.Cells(2, 8)
If SF = "" Then
J = [0=0]
Else
J =
[LISTINGSSHEET.Cells(X, 8) = SF]
End If

'BED MIN
BEDMIN = SEARCHSHEET.Cells(1, 9)
If SEARCHSHEET.Cells(1, 9) = "" Then
BEDMIN = 0
End If
K =
[LISTINGSSHEET.Cells(X, 9) >= BEDMIN]

'BED MAX
BEDMAX = SEARCHSHEET.Cells(2, 9)
If SEARCHSHEET.Cells(2, 9) = "" Then
BEDMAX = 999999999
End If
L =
[LISTINGSSHEET.Cells(X, 9) <= BEDMAX]

'BATH MIN
BATHMIN = SEARCHSHEET.Cells(1, 10)
If SEARCHSHEET.Cells(1, 10) = "" Then
BATHMIN = 0
End If
M =
[LISTINGSSHEET.Cells(X, 10) >= BATHMIN]

'BATH MAX
BATHMAX = SEARCHSHEET.Cells(2, 10)
If SEARCHSHEET.Cells(2, 10) = "" Then
BATHMAX = 999999999
End If
N =
[LISTINGSSHEET.Cells(X, 10) <= BATHMAX]

'YEAR MIN
YEARMIN = SEARCHSHEET.Cells(1, 11)
If SEARCHSHEET.Cells(1, 11) = "" Then
YEARMIN = 0
End If
O =
[LISTINGSSHEET.Cells(X, 11) >= YEARMIN]

'YEAR MAX
YEARMAX = SEARCHSHEET.Cells(2, 11)
If SEARCHSHEET.Cells(2, 11) = "" Then
YEARMAX = 999999999
End If
P =
[LISTINGSSHEET.Cells(X, 11) <= YEARMAX]

'SQUARE FOOT MIN
SFMIN = SEARCHSHEET.Cells(1, 12)
If SEARCHSHEET.Cells(1, 12) = "" Then
SFMIN = 0
End If
Q =
[LISTINGSSHEET.Cells(X, 12) >= SFMIN]

'SQUARE FOOT MAX
SFMAX = SEARCHSHEET.Cells(2, 12)
If SEARCHSHEET.Cells(2, 12) = "" Then
SFMAX = 999999999
End If
R =
[LISTINGSSHEETS.Cells(X, 12) <= SFMAX]

'GARAGE MIN
GARMIN = SEARCHSHEET.Cells(1, 13)
If SEARCHSHEET.Cells(1, 13) = "" Then
GARMIN = 0
End If
S =
[LISTINGSSHEET.Cells(X, 13) >= GARMIN]

'GARAGE MAX
GARMAX = SEARCHSHEET.Cells(2, 13)
If SEARCHSHEET.Cells(2, 13) = "" Then
GARMAX = 999999999
End If
T =
[LISTINGSSHEET.Cells(X, 13) <= GARMAX]

'NOTES
U = [0=0]

'WATER
WATER = SEARCHSHEET.Cells(2, 15)
If WATER = "" Then
V = [0=0]
Else
V =
[LISTINGSSHEET.Cells(X, 15) = WATER]
End If

'POWER
Power = SEARCHSHEET.Cells(2, 16)
If Power = "" Then
W = [0=0]
Else
W =
[LISTINGSSHEET.Cells(X, 16) = Power]
End If

'COUNTY
COUNTY = SEARCHSHEET.Cells(2, 17)
If COUNTY = "" Then
X = [0=0]
Else
X =
[LISTINGSSHEET.Cells(X, 17) = COUNTY]
End If

'COMMISSION MIN
COMMIN = SEARCHSHEET.Cells(1, 18)
If SEARCHSHEET.Cells(1, 18) = "" Then
COMMIN = 0
End If
Y =
[LISTINGSSHEET.Cells(X, 18) >= COMMIN]

'COMMISSION MAX
COMMAX = SEARCHSHEET.Cells(2, 18)
If SEARCHSHEET.Cells(2, 18) = "" Then
COMMAX = 999999999
End If
Z =
[LISTINGSSHEET.Cells(X, 18) <= COMMAX]

SR = 4

For Start = 2 To LR

If [A] And [B] And [C] And [D] And [E] And [F] And [G] And [H] And [I] And [J] And [K] And [L] And [M] And [N] And [O] And [P] And [Q] And [R] And [S] And [T] And [U] And [V] And [W] And [X] And [Y] And [Z] Then

SEARCHSHEET.Cells(SR, 1) = LISTINGSSHEET.Cells(X, 1)
SEARCHSHEET.Range("A" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 2) = LISTINGSSHEET.Cells(X, 2)
SEARCHSHEET.Range("B" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 3) = LISTINGSSHEET.Cells(X, 3)
SEARCHSHEET.Range("C" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 4) = LISTINGSSHEET.Cells(X, 4)
SEARCHSHEET.Range("D" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 5) = LISTINGSSHEET.Cells(X, 5)
SEARCHSHEET.Range("E" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 6) = LISTINGSSHEET.Cells(X, 6)
SEARCHSHEET.Range("F" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 7) = LISTINGSSHEET.Cells(X, 7)
SEARCHSHEET.Range("G" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 8) = LISTINGSSHEET.Cells(X, 8)
SEARCHSHEET.Range("H" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 9) = LISTINGSSHEET.Cells(X, 9)
SEARCHSHEET.Range("I" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 10) = LISTINGSSHEET.Cells(X, 10)
SEARCHSHEET.Range("J" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 11) = LISTINGSSHEET.Cells(X, 11)
SEARCHSHEET.Range("K" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 12) = LISTINGSSHEET.Cells(X, 12)
SEARCHSHEET.Range("L" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 13) = LISTINGSSHEET.Cells(X, 13)
SEARCHSHEET.Range("M" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 14) = LISTINGSSHEET.Cells(X, 14)
SEARCHSHEET.Range("N" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 15) = LISTINGSSHEET.Cells(X, 15)
SEARCHSHEET.Range("O" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 16) = LISTINGSSHEET.Cells(X, 16)
SEARCHSHEET.Range("P" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 17) = LISTINGSSHEET.Cells(X, 17)
SEARCHSHEET.Range("Q" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 18) = LISTINGSSHEET.Cells(X, 18)
SEARCHSHEET.Range("R" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous

SR = SR + 1

End If

Next Start

End Sub

cdiable
08-09-2016, 10:32 AM
i have tried setting the variables as byte, variant, string

Paul_Hossler
08-09-2016, 05:38 PM
I think maybe it'd be better if you just told us in plain language what it is you're trying to do for just ONE of the parameters

cdiable
08-09-2016, 08:06 PM
i have an excel sheet named LISTINGS, it is a long list of realty properties. i am trying to make another sheet named SEARCH that you can put in certain parameters and the macro will find all matching listings. the initial problem i had was if some of the search parameters were empty. i set the min and max parameters so those worked all the time. i needed a way to basically say that all values are true if the search parameter is blank. originally i had all the expressions in the big IF statement but i am trying to break it up a little and have the expressions as a variable, and depending on the search parameter change the expression.

If this is still confusing i can try to add the excel sheet.

SamT
08-09-2016, 09:08 PM
First mistake

Dim SEARCHSHEET As Worksheet
Set SEARCHSHEET = ThisWorkbook.Sheets("SEARCH")
SearchSeet is now empty.

Second mistake: Habitual Typos or bad code style

A =
[LISTINGSSHEET.Cells(X, 1) = LANDRES]
B =
[LISTINGSSHEET.Cells(X, 2) = STAT]
Those must be like

A = [LISTINGSSHEET.Cells(X, 1) = LANDRES]
B = [LISTINGSSHEET.Cells(X, 2) = STAT]

Third mistake

A = [LISTINGSSHEET.Cells(X, 1) = LANDRES]
A is a Byte type Variable. [LISTINGSSHEET.Cells(X, 1) = LANDRES] evaluates to True or False. True = the number zero and false the number negative one. Later we see that all those Byte Types should really be Boolean Types.

Fourth mistake

For Start = 2 To LR
If [A] And [B] And [C] And [D] And [E] And [F] Etc
The And Function requires Booleans, but thats OK, since you are telling VBA to evaluate all those bytes that represent Booleans, but that is double the work required.

If any one of those Boolean/Bytes is false nothing after the IF will happen. That doesn't matter because all those variables storing values from (the empty) SEARCHSHEET are all = 0 or ""

Fourth mistake

LISTINGSSHEET.Cells(X, 1)
Since X is either the Byte value of 0 or of -1, that will always error.

Fifth mistake. Since the only thing you are changing while looping down the rows is the Row number, they would all be identical if the rest of the code worked.

cdiable
08-09-2016, 09:44 PM
ok so i don't know why mistake one is a mistake.

mistake two was just the way is posted

mistake three, i have tried byte, Boolean, variant string. this is one place im not sure what it should be.

mistake four was a mistake that i have corrected. I've changed the code several times and never thought about having the x as the row call out and a variable. that is corrected now.
'
not exactly sure what you mean by the fifth mistake.

below is a new revision of the macro. under each category you will notice that some have only one variable and others have two depending on what the input is for the search. i need to be able to input the expressions into the large if statement at the end and no matter how i format things i either get a type mismatch or an object required error.

i have attached a copy of macro file to help with the posting messing with the idents, etc


Sub TABLE_SEARCH()

Dim LISTINGSSHEET As Worksheet
Set LISTINGSSHEET = ThisWorkbook.Sheets("LISTINGS")

LR = LISTINGSSHEET.Cells(Rows.Count, 1).End(xlUp).Row

Dim SEARCHSHEET As Worksheet
Set SEARCHSHEET = ThisWorkbook.Sheets("SEARCH")

SEARCHSHEET.Range("A4:Z9999").ClearContents
SEARCHSHEET.Range("A4:Z9999").Borders.LineStyle = xlNone

Dim A As Boolean
Dim B As Boolean
Dim C As Boolean
Dim D As Boolean
Dim E As Boolean
Dim F As Boolean
Dim G As Boolean
Dim H As Boolean
Dim I As Boolean
Dim J As Boolean
Dim K As Boolean
Dim L As Boolean
Dim M As Boolean
Dim N As Boolean
Dim O As Boolean
Dim P As Boolean
Dim Q As Boolean
Dim R As Boolean
Dim S As Boolean
Dim T As Boolean
Dim U As Boolean
Dim V As Boolean
Dim W As Boolean
Dim X As Boolean
Dim Y As Boolean
Dim Z As Boolean

'LAND OR RESIDENT
LANDRES = SEARCHSHEET.Cells(2, 1)
If LANDRES = "" Then
A = [0=0]
Else
A =
[LISTINGSSHEET.Cells(CR, 1) = LANDRES]
End If

'STATUS
STAT = SEARCHSHEET.Cells(2, 2)
If STAT = "" Then
B = [0=0]
Else
B =
[LISTINGSSHEET.Cells(CR, 2) = STAT]
End If

'PROPERTY LOCATION
PROPERTY = SEARCHSHEET.Cells(2, 3)
If PROPERTY = "" Then
C = [0=0]
Else
C =
[LISTINGSSHEET.Cells(CR, 3) = PROPERTY]
End If

'TOWN
TOWN = SEARCHSHEET.Cells(2, 4)
If TOWN = "" Then
D = [0=0]
Else
D =
[LISTINGSSHEET.Cells(CR, 4) = TOWN]
End If

'MLS SYSTEM NUMBER
MLS = SEARCHSHEET.Cells(2, 5)
If MLS = "" Then
E = [0=0]
Else
E =
[LISTINGSSHEET.Cells(CR, 5) = MLS]
End If

'PRICE MIN
PRICEMIN = SEARCHSHEET.Cells(1, 6)
If SEARCHSHEET.Cells(1, 6) = "" Then
PRICEMIN = 0
End If
F =
[LISTINGSSHEET.Cells(CR, 6) >= PRICEMIN]

'PRICE MAX
PRICEMAX = SEARCHSHEET.Cells(2, 6)
If SEARCHSHEET.Cells(2, 6) = "" Then
PRICEMAX = 999999999
End If
G =
[LISTINGSSHEET.Cells(CR, 6) <= PRICEMAX]

'ACRE MIN
ACREMIN = SEARCHSHEET.Cells(1, 7)
If SEARCHSHEET.Cells(1, 7) = "" Then
ACREMIN = 0
End If
H =
[LISTINGSSHEET.Cells(CR, 7) >= ACREMIN]

'ACRE MAX
ACREMAX = SEARCHSHEET.Cells(2, 7)
If SEARCHSHEET.Cells(2, 7) = "" Then
ACREMAX = 999999999
End If
I =
[LISTINGSSHEET.Cells(CR, 7) <= ACREMAX]

'SITE BUILT OR FACTORY BULT
SF = SEARCHSHEET.Cells(2, 8)
If SF = "" Then
J = [0=0]
Else
J =
[LISTINGSSHEET.Cells(CR, 8) = SF]
End If

'BED MIN
BEDMIN = SEARCHSHEET.Cells(1, 9)
If SEARCHSHEET.Cells(1, 9) = "" Then
BEDMIN = 0
End If
K =
[LISTINGSSHEET.Cells(CR, 9) >= BEDMIN]

'BED MAX
BEDMAX = SEARCHSHEET.Cells(2, 9)
If SEARCHSHEET.Cells(2, 9) = "" Then
BEDMAX = 999999999
End If
L =
[LISTINGSSHEET.Cells(CR, 9) <= BEDMAX]

'BATH MIN
BATHMIN = SEARCHSHEET.Cells(1, 10)
If SEARCHSHEET.Cells(1, 10) = "" Then
BATHMIN = 0
End If
M =
[LISTINGSSHEET.Cells(CR, 10) >= BATHMIN]

'BATH MAX
BATHMAX = SEARCHSHEET.Cells(2, 10)
If SEARCHSHEET.Cells(2, 10) = "" Then
BATHMAX = 999999999
End If
N =
[LISTINGSSHEET.Cells(CR, 10) <= BATHMAX]

'YEAR MIN
YEARMIN = SEARCHSHEET.Cells(1, 11)
If SEARCHSHEET.Cells(1, 11) = "" Then
YEARMIN = 0
End If
O =
[LISTINGSSHEET.Cells(CR, 11) >= YEARMIN]

'YEAR MAX
YEARMAX = SEARCHSHEET.Cells(2, 11)
If SEARCHSHEET.Cells(2, 11) = "" Then
YEARMAX = 999999999
End If
P =
[LISTINGSSHEET.Cells(CR, 11) <= YEARMAX]

'SQUARE FOOT MIN
SFMIN = SEARCHSHEET.Cells(1, 12)
If SEARCHSHEET.Cells(1, 12) = "" Then
SFMIN = 0
End If
Q =
[LISTINGSSHEET.Cells(CR, 12) >= SFMIN]

'SQUARE FOOT MAX
SFMAX = SEARCHSHEET.Cells(2, 12)
If SEARCHSHEET.Cells(2, 12) = "" Then
SFMAX = 999999999
End If
R =
[LISTINGSSHEETS.Cells(CR, 12) <= SFMAX]
'GARAGE MIN
GARMIN = SEARCHSHEET.Cells(1, 13)
If SEARCHSHEET.Cells(1, 13) = "" Then
GARMIN = 0
End If
S =
[LISTINGSSHEET.Cells(CR, 13) >= GARMIN]

'GARAGE MAX
GARMAX = SEARCHSHEET.Cells(2, 13)
If SEARCHSHEET.Cells(2, 13) = "" Then
GARMAX = 999999999
End If
T =
[LISTINGSSHEET.Cells(CR, 13) <= GARMAX]

'NOTES
U = [0=0]

'WATER
WATER = SEARCHSHEET.Cells(2, 15)
If WATER = "" Then
V = [0=0]
Else
V =
[LISTINGSSHEET.Cells(CR, 15) = WATER]
End If

'POWER
Power = SEARCHSHEET.Cells(2, 16)
If Power = "" Then
W = [0=0]
Else
W =
[LISTINGSSHEET.Cells(CR, 16) = Power]
End If

'COUNTY
COUNTY = SEARCHSHEET.Cells(2, 17)
If COUNTY = "" Then
X = [0=0]
Else
X =
[LISTINGSSHEET.Cells(CR, 17) = COUNTY]
End If

'COMMISSION MIN
COMMIN = SEARCHSHEET.Cells(1, 18)
If SEARCHSHEET.Cells(1, 18) = "" Then
COMMIN = 0
End If
Y =
[LISTINGSSHEET.Cells(CR, 18) >= COMMIN]

'COMMISSION MAX
COMMAX = SEARCHSHEET.Cells(2, 18)
If SEARCHSHEET.Cells(2, 18) = "" Then
COMMAX = 999999999
End If
Z =
[LISTINGSSHEET.Cells(CR, 18) <= COMMAX]

SR = 4

For CR = 2 To LR

If [A] And [B] And [D] And [E] And [F] And [G] And [H] And [I] And [J] And [K] And [L] And [M] And [N] And [O] And [P] And [Q] And [R] And [S] And [T] And [U] And [V] And [W] And [X] And [Y] And [Z] Then

SEARCHSHEET.Cells(SR, 1) = LISTINGSSHEET.Cells(CR, 1)
SEARCHSHEET.Range("A" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 2) = LISTINGSSHEET.Cells(CR, 2)
SEARCHSHEET.Range("B" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 3) = LISTINGSSHEET.Cells(CR, 3)
SEARCHSHEET.Range("C" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 4) = LISTINGSSHEET.Cells(CR, 4)
SEARCHSHEET.Range("D" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 5) = LISTINGSSHEET.Cells(CR, 5)
SEARCHSHEET.Range("E" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 6) = LISTINGSSHEET.Cells(CR, 6)
SEARCHSHEET.Range("F" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 7) = LISTINGSSHEET.Cells(CR, 7)
SEARCHSHEET.Range("G" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 8) = LISTINGSSHEET.Cells(CR, 8)
SEARCHSHEET.Range("H" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 9) = LISTINGSSHEET.Cells(CR, 9)
SEARCHSHEET.Range("I" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 10) = LISTINGSSHEET.Cells(CR, 10)
SEARCHSHEET.Range("J" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 11) = LISTINGSSHEET.Cells(CR, 11)
SEARCHSHEET.Range("K" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 12) = LISTINGSSHEET.Cells(CR, 12)
SEARCHSHEET.Range("L" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 13) = LISTINGSSHEET.Cells(CR, 13)
SEARCHSHEET.Range("M" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 14) = LISTINGSSHEET.Cells(CR, 14)
SEARCHSHEET.Range("N" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 15) = LISTINGSSHEET.Cells(CR, 15)
SEARCHSHEET.Range("O" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 16) = LISTINGSSHEET.Cells(CR, 16)
SEARCHSHEET.Range("P" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 17) = LISTINGSSHEET.Cells(CR, 17)
SEARCHSHEET.Range("Q" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous
SEARCHSHEET.Cells(SR, 18) = LISTINGSSHEET.Cells(CR, 18)
SEARCHSHEET.Range("R" & SR & "").Borders.LineStyle = XlLineStyle.xlContinuous

SR = SR + 1

End If

Next CR

End Sub

Paul_Hossler
08-10-2016, 07:36 AM
i have an excel sheet named LISTINGS, it is a long list of realty properties. i am trying to make another sheet named SEARCH that you can put in certain parameters and the macro will find all matching listings. the initial problem i had was if some of the search parameters were empty. i set the min and max parameters so those worked all the time. i needed a way to basically say that all values are true if the search parameter is blank. originally i had all the expressions in the big IF statement but i am trying to break it up a little and have the expressions as a variable, and depending on the search parameter change the expression.

If this is still confusing i can try to add the excel sheet.

Since we already by your macro code, what would be more helpful is a workbook with a small sample of the data also

cdiable
08-10-2016, 08:12 AM
Here is a sample of the data.

Paul_Hossler
08-10-2016, 08:24 AM
In any event, I think that Excel's Advanced Filter will do what you want a lot easier

Two simple macros to automate using the filter




Option Explicit
'http://www.contextures.com/xladvfilter01.html
'http://www.contextures.com/xladvfilter02.html
Sub FilterData()
Application.ScreenUpdating = False
Worksheets("LISTINGS").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Worksheets("CRITERIA").Range("A5").CurrentRegion, CopyToRange:=Worksheets("SEARCH").Range("A1").CurrentRegion.Rows(1), Unique:=False
Worksheets("SEARCH").Range("A1").CurrentRegion.EntireColumn.AutoFit
Worksheets("SEARCH").Select
Application.ScreenUpdating = True
End Sub
'http://spreadsheetpage.com/index.php/tip/clearing_the_advanced_filter_dialog_box/
Sub ClearFilter()
Dim r As Range

Application.ScreenUpdating = False
On Error Resume Next
With ActiveWorkbook
.Names("_FilterDatabase").Delete
.Names("Criteria").Delete
.Names("Extract").Delete
End With
On Error GoTo 0
Set r = Worksheets("CRITERIA").Range("A5").CurrentRegion
If r.Rows.Count > 1 Then
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.ClearContents
End If

Set r = Worksheets("SEARCH").Range("A1").CurrentRegion
If r.Rows.Count > 1 Then
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.ClearContents
End If

Worksheets("CRITERIA").Select

Application.ScreenUpdating = True
End Sub




Three sheets

Listing data
Selection criteria
Search Results

So as test, select more than 2 bedrooms, and 1 or 3 bathrooms

16838

SamT
08-10-2016, 08:33 AM
You are right. I was wrong. there was no first mistake.

I think you are trying to compare the headers in SearchSheet, Row 2 to the headers in ListingSheet, Row CR.

The # icon in the Post editor menu will insert CODE Tags in to your post. You can paste the code between them or, paste the code first, then select it, then click the icon. That is what I did when I edited your posts, Select + Icon.

IMO, the best way to configure the VBA Editor is to use the Tools Menu >> Options >> Editor Tab and check every box in the "Code Settings" Frame. Then at the very top of all your existing Code pages, put Option Explicit. On the General Tab check all boxes plus the Break on all errors box. Those checked boxes will let you hover the mouse over variables to see their current value, they will make VBA offer suggestions about what Parameters follow Functions, and will force you to declare all variables and will tell you about all typos and various other mistakes.


i need to be able to input the expressions into the large if statement at the end
As that statement is written, all those have to be True to continue.

All variables are set to 0 for numbers, "" for strings, False for Booleans, and Null or Nothing, for other types when they are declared.

[...] is defined as Evaluate the inner expression. What it does is tell VBA to figger out zactly what you want and do it to it. None of your code needs Evaluate.

Lets parse this bit before we go on.

'LAND OR RESIDENT
LANDRES = SEARCHSHEET.Cells(2, 1)
If LANDRES = "" Then
A = True '[0=0]
Else
A = LISTINGSSHEET.Cells(CR, 1) = LANDRES ' don't need [...]
End If
LANDRES = SEARCHSHEET.Cells(2, 1): Set Landres = First header
If LANDRES = "" Then A = [0=0]: If it's empty then A is True
A =
[LISTINGSSHEET.Cells(CR, 1) = LANDRES]: A is True if that Listing Cell = that header cell

The OR function means if either is true, so A = B Or C. Since that big If at the bottom must all be true to continue, I would rewrite that section of code to read If not at least one is true then exit

If SEARCHSHEET.Cells(2, 1) <> "" Or SEARCHSHEET.Cells(2, 1) <> LISTINGSSHEET.Cells(CR, 1) Then Exit Sub
The problem is that you have not assigned a Row number to CR.

The above won't fix all the problems, but it will replace a lot of the 6 line IF... THEN sections with a one line IF...THEN. Personally, after editing all instances of that pattern to one liners, I would not put any white space between one liners.

Dim CR as Long
CR = 2 'adjust as needed
If SEARCHSHEET.Cells(2, 1) = "" Or SEARCHSHEET.Cells(2, 1) <> LISTINGSSHEET.Cells(CR, 1) Then Exit Sub
If SEARCHSHEET.Cells(2, 2) = "" Or SEARCHSHEET.Cells(2, 2) <> LISTINGSSHEET.Cells(CR, 2) Then Exit Sub
If SEARCHSHEET.Cells(2, 3) = "" Or SEARCHSHEET.Cells(2, 3) <> LISTINGSSHEET.Cells(CR, 3) Then Exit Sub
If SEARCHSHEET.Cells(2, 4) = "" Or SEARCHSHEET.Cells(2, 4) <> LISTINGSSHEET.Cells(CR, 4) Then Exit Sub

YO! Check that out, Dude or Dudette! We can use a loop for all instances of that pattern.:D


Dim Col As Long
For Col = 1 to 4
If SEARCHSHEET.Cells(2, Col) <> "" Or SEARCHSHEET.Cells(2, Col) <> LISTINGSSHEET.Cells(CR, Col) Then Exit Sub
Next Col

Regardless of all that above, since I don't know what you are trying to do, I don't know if the above code will do it. All I can do is help you improve the code that you are using. I can just about guarantee that the code will Exit Sub.

SamT
08-10-2016, 08:43 AM
Maybe what you want is: If Search Cell is empty or Doesn't Match Listing Cell then add Listing cell to Search Sheet

Dim Col As Long
For Col = 1 To 4
If SEARCHSHEET.Cells(2, Col) <> "" Or SEARCHSHEET.Cells(2, Col) <> LISTINGSSHEET.Cells(CR, Col) Then
SEARCHSHEET.Cells(SR, col) = LISTINGSSHEET.Cells(CR, col)
Next Col

Paul_Hossler
08-10-2016, 08:52 AM
I still think you'd be better off using what Excel offers out of the box instead of trying to roll your own

This is your data, but using my suggestion of the Excel advanced filter

cdiable
08-10-2016, 10:35 AM
yes the out of the box excel does work but not exactly how i would like it too.

hence why i was trying to create my own macro

Is there an easy way to set min and max with the out of the box? or can i only set less than or greater than or equal too

Paul_Hossler
08-10-2016, 02:20 PM
Min/max are possible - think of them as two criteria

Since they're on the same row, it's an AND

So (Price > 199999) AND (Price < 400001)

16849


16850

SamT
08-10-2016, 04:12 PM
We have hinted several times. but let me make it perfectly clear.

What are you trying to accomplish with this code?