MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Malc on July 11, 2001 3:06 AM

Sub ChkCol()

The problem will be dattxt & prcode are not exactly the same as the concanted equivelent on the sheet. Perhaps there's a space somewhere thats not obvious. Try tempoarily insert another variable showing the actual contents of a concanted cell and dattxt & prcode use watch to see if they are the same. It's either that or some funny from mixing dates and other data types