PDA

View Full Version : code debugging



arnab0711
02-20-2011, 07:16 AM
Hi,
I have this code which works on the sheet input although the code marked in red is not working,pl advice
Sub test()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sqlstr As String
With ActiveWorkbook.Sheets(3): If .[a2] <> "" Then .Range(.[a2], .[a2].End(xlDown)).Resize(, 56).Clear
End With: With ActiveWorkbook: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .Path & "\" & .Name & ";Extended Properties=""Excel 8.0;HDR=No;"";"
sqlstr = "SELECT T1.F1,T1.F2,T1.F3,T1.F4,T1.F5,T1.F6,T1.F7,T1.F8,T1.F9,T1.F10,T1.F11,T1.F12, T1.F13,T1.F14,T1.F15,T1.F16,T1.F17," & _
"T1.F18,T1.F19,T1.F20,T1.F21,T1.F22,T1.F23,T1.F24,T1.F25,T1.F26,T1.F27,T1.F2 8,T1.F29,T1.F30,T1.F31,T1.F32,T1.F33,T1.F34,T1.F35," & _
"T1.F36,T1.F37,T1.F38,T1.F39,T1.F40,T1.F41,T1.F42,T1.F43,T1.F44,T1.F45,T1.F4 6,T1.F47,T1.F48,T1.F49,T1.F50,T1.F51,T1.F52,T1.F53," & _
"T1.F54,T1.F55,T1.F56 FROM `raw data$A2:BD65536` T1 WHERE"
With Sheets("Input")
Select Case .[e3]
Case Is <> ""
sqlstr = sqlstr & " UCASE(T1.F4) LIKE '%" & UCase(.[e3]) & "%'"
If .[f3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
If .[g3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
End Select
rs.Open sqlstr, cn
With Sheets(3): .[a2].CopyFromRecordset rs: [a:bd].EntireColumn.AutoFit: End With
End With: Set rs = Nothing: Set cn = Nothing: End With: End Sub

Zack Barresse
02-20-2011, 09:51 AM
Why do you have two Case statements that are the same thing? Doesn't make any logical sense. Did you mean to have a Case Else?

Bob Phillips
02-20-2011, 09:55 AM
How can you have an else on a <> and an =? There is no other option :)

Zack Barresse
02-20-2011, 09:56 AM
Ha! So much for my 'logical' statement. LOL!

Paul_Hossler
02-20-2011, 10:06 AM
ZB was thinking of "True", "False", "Maybe"

:giggle

Paul

Zack Barresse
02-20-2011, 10:07 AM
Maybe... ;)

Bob Phillips
02-20-2011, 10:22 AM
ZB was thinking of "True", "False", "Maybe"

:giggle

Paul

Or maybe Male, Female, Other?