Runtime error 13 - Type Mismatch when DIM Range as String

I have a small macro running which puts a list of names in random order, then splits the list into two separate lists based on the total number of names in the list.
In the macro, I set up start and end of the range as two strings 'srange' &' erange'. The start and end cells references are held in cells B79 & B80 respectively. These cell refs change depending on total number of names.

Everything works fine in Microsoft 365, but I get a Runtime Error 13 - Type Mismatch at the first line in the code below, when I run the macro in Excel 2013.

Any ideas what I could be doing wrong? I've already had to change my code for 2013 because it gave a 1004 error when I used INDIRECT to pick up the cell references from B79/B80.

Any help appreciated.

Dim sRange As String, eRange As String
sRange = ActiveSheet.Range("B79"):
eRange = ActiveSheet.Range("B80")
Range(sRange & ":" & eRange).Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste

Hi there,

Knee-jerk suggestion - without access to your workbook, anything else is very difficult:

Please Login or Register  to view this content.

Regards,

Greg M

Hi Greg,

I'm new to VBA so this is going to sound like a really stupid question. Do I just type .value as shown in your reply, or is excel looking for an actual value? Do I also still need the : between the range statements?
B79 & B80 contain cell references to another part of the same sheet, i.e. G3 & G27, but they change constantly depending on total number of names on the list.

There's actually a formula in B79 & B80 to build the cells reference I need. Formula is =CONCAT("G", B77+2). At the moment this gives the cell reference G33. Wondering if I just need to copy B79/B80 so that the formula is only picking up text?

Hi again,

Do I just type .value as shown in your reply, or is excel looking for an actual value?

Yes, just add ".Value" (without quotes) as per my first post.

Do I also still need the : between the range statements?

The ":" character is used to separate statements which are on the same LINE of code, so it's not necessary to use a ":" separator if nothing else follows it on the same line.


As mentioned in other posts, I think you're more likely to get a helpful suggestion if you post your workbook here. It often happens that the cause of a problem reveals itself only after someone has had a chance to "play around" with a workbook.


Regards,

Greg M

Code attached.

There's one macro which runs when the DRAW button is pressed. The idea behind the sheet is to do a random draw of entrants and calculate out how many Ties & Byes there will be in a knockout tournament.

As I said previously, it works fine in Microsoft 365, but gives the Runtime error 13 - Type Mismatch in Excel 2013. Baffled??

Thanks for your help and patience with this.

Dave

My apologies. I thought I had removed all passwords. It's DB14

That's the issue. It runs great on 365, but I get the Runtime Error 13 when I run it in Excel 2013.
Unfortunately most of the users have older versions of Excel on their computers.

Sorry, this doesn't work. It gives a 1004 error - This action won't work on multiple sections at the Range("G2, B78").Copy Range("I2") line.

Thanks to everybody who responded to my post. Turns out there was a lot more wrong than I had anticipated, but with your help the program is now running beautifully in all versions of Excel. The main takeaway is I should have checked that the formulae and commands I was using actually existed in Excel 2010/2013, not just in 365. Lesson learned! Thanks again.

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

Best regards,

Greg M