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

 

[RoryA]

Do any of the cells you may be referencing contain an error? (Note that code is inside a loop so it may not be the first run through that is causing the error)

 

[DonkeyOte]

Does this cell contain an error value ?

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

EDIT: oops sorry Rory...

 

[oakwood_001]

The only reason I've not done that Donkey is because I know my data will never exceed that much and I wanted to keep the memory required to a minimum but valid point nonetheless

 

[RoryA]

VBA will convert it to a Long internally anyway so that's a false economy and you're actually slowing your code down!

 

[oakwood_001]

I think it was caused by an error - seems to be running OK now

Thanks again guys - always feel like a dunce when my question gets an answer and it seems to be so simple

 

[DonkeyOte]

I like your new soubriquet Rory... re conversion to Long... don't follow ... can you explain to me ?

 

[RoryA]

I like yours too.
Internally, VBA will use Longs rather than integers. So if you tell it to use Integers, it has to convert to a long, work with it, then convert back. See here for example.

 

[DonkeyOte]

aah I see thanks -- so really it's never worth using... I guess it remains as a legacy not to mention I'm sure some people may like the overflow catch it can generate ...