Variable Types - Help!

[oakwood_001]

Morning All,

I have a procedure that was working perfectly yesterday. I've not changed it and yet today it doesn't seem to be working.

I keep getting a 'Runtime Error '13', Type Mismatch' debug error. Can you help?

Public intRowCount As Integer
 
Sub mcrCopyDataNewSheet()
Dim strRawDataBook As String
Dim strNewBook As String
Dim strRawDataRange As String
    intRowCount = Range(Range("B5"), Range("B5").End(xlDown)).Count
    strRawDataBook = ActiveWorkbook.Name
    strRawDataRange = Application.InputBox(Prompt:="Please enter the Raw Data Range beginning with Earliest Start on Sunday and ending with the Saturday Rota:", Default:="L:AR", Type:=8)
    Range(strRawDataRange).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("1:1").Delete
    strNewBook = ActiveWorkbook.Name
    Workbooks(strRawDataBook).Activate
    Range(Range("B5"), Range("B5").End(xlDown)).Copy
    Workbooks(strNewBook).Activate
    Range("AK4").Select
    Selection.PasteSpecial Paste:=xlValues
End Sub

Any help, much appreciated. Driving me crazy

 

[DonkeyOte]

Try changing intRowCount to Long

re data types: http://bytes.com/forum/thread601315.html

 

[oakwood_001]

Doesn't like Long either

And yet it works when I use...

    strRawDataRange = InputBox(Prompt:="Please enter the Raw Data Range beginning with Earliest Start on Sunday and ending with the Saturday Rota:", Default:="L:AR")

As oppose:

    strRawDataRange = Application.InputBox(Prompt:="Please enter the Raw Data Range beginning with Earliest Start on Sunday and ending with the Saturday Rota:", Default:="L:AR", Type:=8)

 

[Richard Schollar]

Type:=8 specifies a range so you'd need to dim strRawDataRange as a Range variable to get it to work (or a Variant).

 

[DonkeyOte]

And if you assign strRawDataRange as a range type you will need to use Set, eg

Set strRawDataRange = Application.InputBox(...

and then given it's now a defined range you can use:

strRawDataRange.Select

(and you might opt to rename it too)

 

[oakwood_001]

Thanks guys

Saved my bacon as usual

 

[oakwood_001]

Me again...

I'm having the same problem with another piece of code where I get a 'Runtime '13'' error whereby it doesn't like having a cell entered as a range in an InputBox.

Sub mcrNamesToUniques()
Dim i As Integer
Dim n As Integer
Dim strStartingConCell As String
Dim strStartingUniqueCell As String
Dim strUnique As String
Dim strCon As String
Dim x As Integer
Dim y As Integer
Dim strOutputEIN As String

strStartingConCell = InputBox("Starting Concatenated Cell")
strStartingUniqueCell = InputBox("Starting Unique Cell")
Application.ScreenUpdating = False
y = Range(Range(strStartingConCell), Range(strStartingConCell).End(xlDown)).Count
x = Range(Range(strStartingUniqueCell), Range(strStartingUniqueCell).End(xlDown)).Count
For n = 0 To x
For i = 0 To y
    strCon = Range(strStartingConCell).Offset(i, 0).Value
    strUnique = Range(strStartingUniqueCell).Offset(n, 0).Value
        If strCon = strUnique Then
            strOutputEIN = Range(strStartingConCell).Offset(i, 2).Value
            Range(strStartingUniqueCell).Offset(n, 200).Select
            Selection.End(xlToLeft).Select
            ActiveCell.Offset(0, 1).Value = strOutputEIN
        Else
        End If
Next i
Next n
Application.ScreenUpdating = True
End Sub

And yet when I 'step into' the procedure it works fine. If I try to run it fully however, it won't work and I get the error.

And this was all working fine yesterday. Is there a setting or something that could have been changed whereby it won't work now?

It's completely baffling.

Thanks.

 

[RoryA]

Top tip: if you tell us which line is causing the problem, it saves us having to try and guess...

 

[oakwood_001]

Yeh that would help

    strCon = Range(strStartingConCell).Offset(i, 0).Value

Thats the line where the Runtime error first appears

And yet stepping through it handles that line fine and performs perfectly

 

[DonkeyOte]

And to reiterate anything that holds a row count or row reference should really always be a Long type as opposed to an Integer type... Integer stops at 32767 so if you try to assign a number > than that to the variable it will bug.