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
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