Type mismatch while running If statement

[Guest]

I've got Type Mismatch error message while I'm running the following code
(It happens half way through, so I think the code can work ok, but I don't know why it stops right in the middle!!!!
How can I improve the code and solve the problem? Pls Help!!! Thanks!!!

Function Copy(ByVal RowNum As String
Sheets("Region").Selec
C1 =
For S2 = 2 To 5000
If Workbooks("Test.xls").Worksheets("Region").Cells(S2, 1) = "" Then Exit For
If Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 3) _ <----- It stop right here
= Workbooks("Test.xls").Worksheets("Macro").Cells(RowNum, 4)
And Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 3)
<> "South" The

For S3 = 1 To 3
Worksheets("Region").Cells(C1, S3)
= Workbooks("Test.xls").Worksheets("Region").Cells(S2, S3
Next S
C1 = C1 +
ElseIf Left(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 4)
= Workbooks("Test.xls").Worksheets("Macro").Cells(RowNum, 5) The

For S3 = 1 To 3
Worksheets("Region").Cells(C1, S3)
= Workbooks("Test.xls").Worksheets("Region").Cells(S2, S3
Next S
C1 = C1 +
End I
Next S
End Functio

 

[RWN]

Not an expert, but you'll probably get a type mismatch with using the
"RowNum" as a subscript. You have defined it as a string (vs. integer)

HTH

--
Regards;
Rob
------------------------------------------------------------------------

I've got Type Mismatch error message while I'm running the following code:
(It happens half way through, so I think the code can work ok, but I

don't know why it stops right in the middle!!!!)

How can I improve the code and solve the problem? Pls Help!!! Thanks!!!!

Function Copy(ByVal RowNum As String)
Sheets("Region").Select
C1 = 2
For S2 = 2 To 50000
If Workbooks("Test.xls").Worksheets("Region").Cells(S2, 1) = "" Then Exit For
If

Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 3) _
<----- It stop right here

=

Workbooks("Test.xls").Worksheets("Macro").Cells(RowNum, 4) _

And

Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 3) _

<> "South" Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
=

Workbooks("Test.xls").Worksheets("Region").Cells(S2, S3)

Next S3
C1 = C1 + 1
ElseIf

Left(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 4) _

=

Workbooks("Test.xls").Worksheets("Macro").Cells(RowNum, 5) Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
=

Workbooks("Test.xls").Worksheets("Region").Cells(S2, S3)

 

[Guest]

But I don't think it matters, because it works on the other code.

 

[Colo]

Hello,
I'm afraid I didn't understood your function completely, so What I
wrote may wide of the mark...
But here are some suggestions.

1.As RWN posted, a variable RowNum should be declared as a Long type.
If RowNum need to a string type, you can use it in the code as
Clng(RowNum)
2.Add the word "Option Explicit" in the very top line of your module.
3.Don't you need a result for this UDF?
4.You can use Worksheet type variables and it makes your code short.
5.The word "South" is 5 characters so the formula

Code:
--------------------

Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 3)

--------------------

would be

Code:
--------------------

Right(Workbooks("Test.xls").Worksheets("Region").Cells(S2, 3), 5)

--------------------



The code would be something like this.
Plece BREAK POINT in VBE, run your code Step by step with pressing [F8]
key.



Code:
--------------------


Option Explicit

Function UDFCopy(ByVal RowNum As Long) As Boolean
Sheets("Region").Select
Dim C1 As Long, S2 As Long, S3 As Long
Dim wsReg As Worksheet, wsMcr As Worksheet
On Error GoTo Terminate
Set wsReg = Workbooks("Test.xls").Worksheets("Region")
Set wsMcr = Workbooks("Test.xls").Worksheets("Macro")
C1 = 2
For S2 = 2 To 50000
If wsReg.Cells(S2, 1) = "" Then Exit For
If Right(wsReg.Cells(S2, 3), 3) = wsMcr.Cells(RowNum, 4) _
And Right(wsReg.Cells(S2, 3), 5) <> "South" Then
For S3 = 1 To 30
wsReg.Cells(C1, S3) = wsReg.Cells(S2, S3)
Next S3
C1 = C1 + 1
ElseIf Left(wsReg.Cells(S2, 3), 4) = wsMcr.Cells(RowNum, 5) Then
For S3 = 1 To 30
wsReg.Cells(C1, S3) = wsReg.Cells(S2, S3)
Next S3
C1 = C1 + 1
End If
Next S2
UDFCopy = True
Exit Sub
Terminate:
UDFCopy = False
End Function

--------------------

 

[Patrick Molloy]

It would be easier to read & debug if you set worksheet
object to the sheets etc...


Function Copy(ByVal RowNum As Long)
Dim WBTest As Workbook
Dim WSRegion As Worksheet
Dim WSMacro As Worksheet

Set WBTest = Workbooks("Test.xls")
Set WSRegion = _
Workbooks("Test.xls").Worksheets("Region")
Set WSMacro = WBTest.Worksheets("Macro")


C1 = 2
For S2 = 2 To 50000
With WSR
If .Cells(S2, 1) = "" Then Exit For
If Right(.Cells(S2, 3), 3) _
= WSMacro.Cells(RowNum, 4) _
And Right(.Cells(S2, 3), 3) _
<> "South" Then

For S3 = 1 To 30
.Cells(C1, S3) = .Cells(S2, S3)
Next S3
C1 = C1 + 1
ElseIf Left(.Cells(S2, 3), 4) _
= WSMacro.Cells(RowNum, 5) Then

For S3 = 1 To 30
.Cells(C1, S3) _
= .Cells(S2, S3)
Next S3
C1 = C1 + 1
End If
Next S2
End Function



Try this.
By the way otice that I changed the parameter to Long
In the code where you test the value of a string, VBA
will coerce the value. eg
If "123" > 100 then
This is a text value ("123") which VB coerces to a number
123 then tests againt 100.
This isn't an error. However if the value was "A" then an
error would occur. By passing the parameter as long, you
enforce the rule and the coercion cannot happen as the
variable is now a number by definition.

-----Original Message-----
I've got Type Mismatch error message while I'm running the following code:
(It happens half way through, so I think the code can

work ok, but I don't know why it stops right in the
middle!!!!)

How can I improve the code and solve the problem? Pls Help!!! Thanks!!!!

Function Copy(ByVal RowNum As String)
Sheets("Region").Select
C1 = 2
For S2 = 2 To 50000
If Workbooks("Test.xls").Worksheets

("Region").Cells(S2, 1) = "" Then Exit For

If Right(Workbooks("Test.xls").Worksheets

("Region").Cells(S2, 3), 3) _ <----- It stop right here

= Workbooks("Test.xls").Worksheets ("Macro").Cells(RowNum, 4) _
And Right(Workbooks("Test.xls").Worksheets ("Region").Cells(S2, 3), 3) _
<> "South" Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
= Workbooks

("Test.xls").Worksheets("Region").Cells(S2, S3)

Next S3
C1 = C1 + 1
ElseIf Left(Workbooks("Test.xls").Worksheets ("Region").Cells(S2, 3), 4) _
= Workbooks("Test.xls").Worksheets

("Macro").Cells(RowNum, 5) Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
= Workbooks

("Test.xls").Worksheets("Region").Cells(S2, S3)