Application-Defined Error: 1004

[HimanshuK]

Hi Everyone,

I have created the below code and a user defined function to-
- To specify the range of cells based on the input dates -->sdate and edate variable
-Run the user defined function searchcolumns based on input of range

Below is the code


Private Sub CommandButton2_Click()


Dim rstr, rend, sdate, edate As Long
Dim ws, rng As String


ws = Sheets("BG").range("J2").Value
sdate = Sheets("BG").range("J3").Value
edate = Sheets("BG").range("J4").Value


rstr = Application.WorksheetFunction.Match(sdate, Sheets(ws).range("$A:$A"), 0):banghead:
rend = Application.WorksheetFunction.Match(edate, Sheets(ws).range("$A:$A"), 0)


Let rng = "C" & rstr & ":" & "W" & rend

Sheets("BG").range("J5").Value = SearchColumns(Sheets(ws).range(rng), ",")


End Sub


I am getting Run time error: 1004 with rstr. I think then I also might get this at rend

Can anyone tellme what to do next so that I resolve it?

[Kenneth Hobs]

Welcome to the forum! When pasting code, please paste between code tags. Click the # icon to insert tags.


Paste a copy of what the manual formula looks like. If it errors, then the VBA code will error. The Go Advanced button in lower right of a reply shows a toolbar with a paperclip icon to attach a workbook.

[p45cal]

Dim rstr As Long, rend As Long, sdate As Long, edate As Long
Dim ws As String, rng As String

Is SearchColumns a UDF somewhere?

[HimanshuK]

Yes p45cal..searchcolumns is a UDF

And thanks Kenneth and P45cal for your replies...the code now works.

Happy to be here :hi:

[GTO]

Greetings,

I realize that you just joined and welcome :-)

Please see Here (http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters&page=1#comments) for information regarding cross-posting without advisement.

Mark