Error 1004 When using Range Cell

[lalaluye]

Hello,

I am facing run time errors 1004 with this piece of code. It is strange because this code worked in another module, but when I placed this in a userform sheet, it doesn't work.

VB:



Dim FileName As String
Dim SummarySheet As Worksheet
Dim WorkBk As Workbook
Dim FolderPath As String
Dim LastRow As Long
Dim LastCol As Long
Dim NRow As Long
Dim NCol As Long
Dim SourceRange As Range
Dim DestRange As Range


' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

' Set Worksheet Name
ActiveSheet.Name = "BTS1 DL_HARQ"

' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*BTS1_PHYMAC(DL_HARQ).csv*")

' Initialize column to 1
NCol = 1


' Loop until Dir returns an empty string.
Do While FileName <> ""

' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1

' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)

' Set the cell in row 1 to be the file name.
SummarySheet.Cells(1, NCol) = FileName

'Find the last row to be copied
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Find the last row to be copied
LastCol = ActiveSheet.Cells(13, Columns.Count).End(xlToLeft).Column

' Set the source range to be K14 to last row
' Modify this range for your workbooks.
' It can span multiple rows.
' Set SourceRange = WorkBk.Worksheets(1).Range("K14:K" & Lastrow)

Dim rFind As Range
Dim ColCount As Long
Dim FindRow As Long
Dim FindCol As Long

For ColCount = 1 To LastCol
With Range(Cells(1, ColCount), Cells(LastRow, ColCount))
Set rFind = .Find(What:="Tx Throughput [kbps]", LookIn:=xlValues, LookAt:=xlWhole)
If Not rFind Is Nothing Then
FindRow = rFind.Row
FindCol = rFind.Column
End If
End With
Next ColCount

Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol), Cells(LastRow, FindCol))

' Set the destination range to start at row 2 and
' be the same size as the source range.
Set DestRange = SummarySheet.Cells(NRow + 1, NCol)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)

' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value

' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count

' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False

' Use Dir to get the next file name.
FileName = Dir()

' Increase NCol to copy the next file on the next column
NCol = NCol + 1
Loop

End Sub



I found the issue occurring on this line when using breakpoints

VB:
Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol), Cells(LastRow, FindCol))


I have researched and tried everything such as

VB:
Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol)).Resize(LastRow)


VB:
Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol)).Resize(LastRow, LastCol)


VB:
With WorkBk.Worksheets(1)
.Range(.Cells(FindRow + 2, FindCol), .Cells(FindRow + 2, FindCol))
End With


And none seemed to work. Again this code worked before on another module. I don't know why it's not working when I put in under the command button sub for userform.

Please help

 

[MARK858]

Does replacing

Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol), Cells(LastRow, FindCol))

with

With WorkBk.Worksheets(1)
        Set SourceRange = .Range(.Cells(FindRow + 2, FindCol), .Cells(LastRow, FindCol))
End With

make a difference?

 

[lalaluye]

Apparently not. It gave me the same error (I'm quite new to VBA)

 

[MARK858]

With the code as I posted, when it errors hover your mouse over FindRow, FindCol and LastRow.

Are they giving the values that you would expect? what are they?

 

[lalaluye]

FindRow = 0
FindCol = 0
LastRow = 222

FindRow and FindCol values are different from the other working module where I placed this code in where FindRow was 12 and FindCol was 11.

The Filename, and FolderPath were unchanged. So it definitely should have a value rather than 0.

 

[MARK858]

FindRow = 0
FindCol = 0

That's where your error is, they should both be positive numbers. You need to check rFind.
Is the text it is searching for on the sheet? is the sheet the activesheet?

 

[lalaluye]

Yes the text it is searching is on the sheet. I am assuming the sheet containing the text is active when searching the text because there is a value for LastRow and LastCol?

I have also tried adding this small line of code before set source range with no luck

WorkBk.Worksheets(1).Activate
Set SourceRange = WorkBk.Worksheets(1).Range(Cells(FindRow + 2, FindCol), Cells(LastRow, FindCol))

 

[lalaluye]

The thing is that this code works on the other module and gives a good result so the text it is searching is definitely there

However, when running it through the ActiveX control command button, it gives this error. And I have absolutely no clue why.

 

[MARK858]

I have already stated why you are getting the error.
Add the lines in red below, if rFind isn't found then the fisrt msgbox will give a 0 and 0 and you will get an error 91 when it reaches the 2nd msgbox.

If that happens check the spelling and spacing is exactly the same with the text you are searching for and specify your sheet rather than use activesheet.

    For ColCount = 1 To LastCol
        With Range(Cells(1, ColCount), Cells(LastRow, ColCount))
            Set rFind = .Find(What:="Tx Throughput [kbps]", LookIn:=xlValues, LookAt:=xlWhole)
            If Not rFind Is Nothing Then
                FindRow = rFind.Row
                FindCol = rFind.Column
            End If
        End With
    Next
    [COLOR="#FF0000"]MsgBox "FindRow =  " & FindRow & "  Findcol = " & FindCol
    MsgBox rFind.Address[/COLOR]

 

[MARK858]

Actually the rFind msgbox will produce an error if your last column doesn't have the search string in it but the first messagebox should not display any zero's.