VB Guys /Gals need to concatentating 2 variables
Posted by phil on July 11, 2001 2:53 AM
I have two variables "Date" and "Product Code" on a sheet that i want to use to AutoFilter another sheet...
I've done this but it seems to be VERY slow. below is the part of the VB i used to do the filter on the two collumns....
Selection.AutoFilter Field:=6, Criteria1:=prcode
Selection.AutoFilter Field:=2, Criteria1:=dattxt
To make it go faster i have made collumn A on the filter sheet a concatenation of prcode & dattxt....
My question is how do i concatenate the two in VB...???
I think it's something alnomg these lines
Selection.AutoFilter Field:=1, Criteria1:=dattxt & prcode
but i can't get it to work....
Any help is definatly thankfully accepted
Cheers
phil
p.s
below is all of the VB code
Sub ChkCol()
Application.ScreenUpdating = False
Sheets("RM").Select
curcol = ActiveCell.Column
currow = ActiveCell.Row
Select Case curcol
Case 11, 14, 17, 20, 23, 26, 29, 32, 35, 38, 41, 44, 47, 50
Call CompLinks(currow, curcol)
Case Else
titl = "Warning !!!"
msg = "You must be in an 'IN' column to run this macro"
MsgBox msg, vbInformation, titl
End Select
End Sub
Sub CompLinks(currow, curcol)
' Initialise variables
datcol = curcol - 1
dattxt = Cells(3, datcol).Text
prcode = Cells(currow, 3).Value
'Pick up relevant values via filter
On Error GoTo nofilter
Sheets("RM IN").Select
ActiveSheet.ShowAllData
GoTo isafilter
nofilter:
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
isafilter:
Selection.AutoFilter Field:=6, Criteria1:=prcode
Selection.AutoFilter Field:=2, Criteria1:=dattxt
msg = ""
Range("C1").Select
Selection.End(xlDown).Select
lstrow = ActiveCell.Row
If lstrow = 65536 Then GoTo norows:
msg = "The selection you chose returned the figures above"
titl = "Contents of this cell are made up from"
GoTo thend
norows:
Range("C1").Select
titl = "Contents of this cell are made up from"
msg = "The selection you chose returned no data"
MsgBox msg, vbInformation, titl
ActiveSheet.ShowAllData
Sheets("RM").Select
Application.ScreenUpdating = True
Exit Sub
thend:
Application.ScreenUpdating = True
Range("C1").Select
End Sub