reference to multiple columns not working in a file

**bagullo** · 02-24-2011, 10:56 AM

hi there,

this is the weirdest thing ever

i often refer to a number of rows or columns so I got tired of writting
ws.rows(firstrow & ":" & lastrow & ")"
and specially for columns

so I made a couple user functions to get me through (see below)

however this functions have stoped working for a single file
in which it returns "Type mismatch" when evaulating a row such as

sub public test()
dim ws as worksheet
dim firstcol,lastcol as integer
set ws = activesheet
firstcol = 1
lastcol = 2

ws.columns(rgc(firstcol,lastcol))

end sub

actually it does not accept anymore references like ws.columns("A:B") or even ws.range("A:B") or ws.rows("1:2") for that matter

what could it be???

please help!

it just stopped working, I tried restarting and even from a different pc

number to letter

Public Function N2L(num As Integer) As String
    'returns letter corresponding to column number
    N2L = Split(Cells(1, num).Address, "$")(1)
End Function

to declare a range of rows

Function rgr(ByVal firstrow As Integer, ByVal lastRow As Integer) As String
    'returns the string to refer to a range of rows as in ws.rows(string)
    rgr = firstrow & ":" & lastRow
End Function

to declare a range of columns

Function rgc(ByVal firstcolumn As Integer, ByVal lastColumn As Integer) As String
    'returns the string to refer to a range of columns as in ws.columns(string)
    rgc = N2L(firstcolumn) & ":" & N2L(lastColumn)
End Function

**Greg M** · 02-24-2011, 01:33 PM

Hi there,

Stopped working? I don't think this code could EVER have worked

I've corrected a few errors and the following code works for me:



'   sub public test()                   <<  Syntax Error
Public Sub test()

    Dim ws As Worksheet
'    Dim firstcol, lastcol As Integer   '   Note: This statement declares firstcol as a VARIANT
    Dim firstcol As Integer, lastcol As Integer

    Set ws = ActiveSheet
    firstcol = 1
    lastcol = 2

'    ws.Columns (rgc(firstcol, lastcol))    '   This defines only a range - you must also specify a METHOD
    ws.Columns(rgc(firstcol, lastcol)).Select

End Sub

I hope this helps - please let me know how you get on.

Regards,

Greg M

**bagullo** · 02-24-2011, 05:12 PM

Originally Posted by Greg M

Hi there,

Stopped working? I don't think this code could EVER have worked

I've corrected a few errors and the following code works for me:



'   sub public test()                   <<  Syntax Error
Public Sub test()

    Dim ws As Worksheet
'    Dim firstcol, lastcol As Integer   '   Note: This statement declares firstcol as a VARIANT
    Dim firstcol As Integer, lastcol As Integer

    Set ws = ActiveSheet
    firstcol = 1
    lastcol = 2

'    ws.Columns (rgc(firstcol, lastcol))    '   This defines only a range - you must also specify a METHOD
    ws.Columns(rgc(firstcol, lastcol)).Select

End Sub

I hope this helps - please let me know how you get on.

Regards,

Greg M

thank you greg,
actually the sub where the function is called i wrote it on the spot, did not copy it from my code... the point is that the row "ws.Columns(rgc(firstcol, lastcol)).Select" should work always right??
it does not make any sense that it says "type mismatch"
why would that happen only in one file

I hope that tomorrow, once i get back to work the problem has solved by itself :D I'll let you know anyway.

thnks!

**Greg M** · 02-24-2011, 06:26 PM

Hi again,

Thanks for your feedback. Yes, a Type Mismatch error certainly seems strange, but as you know, it's very hard to debug code in isolation - it's usually necessary to see the entire situation e.g. workbook & worksheet names, usedranges etc. etc. in order to fully understand (or at least try to understand!) what's actually happening rather than what should be happening.

It will be a very happy day when a computer does what we WANT it to do instead of what we TELL it to do!

Anyway, let's hope the problem has disappeared at your end - please keep me informed.

Regards,

Greg M

**bagullo** · 02-25-2011, 02:39 AM

problem still there...

"runtime error '13'
type mismatch"

even on the immediate window

print activesheet.Columns("A:B").address

produces the same error

however if open another file with the exact same line in the immediate window
produces a result with no fuss...

$A:$B

is this file damned or what...?!

the problem seems to be that .columns() does not accept an string as argument --- if I use a single integer, there is no problem.

and the problem is there for this single file for sure

anything to try or better I start over copying code in another sheet and see when things turn bad'?

**bagullo** · 02-25-2011, 02:46 AM

one more thing...

apparently calling Cells by itself (referring then to activesheet) and not calling it as a method of some sheet in particular fails as well

Public Function N2L(num As Integer) As String
    'returns letter corresponding to column number
    N2L = Split(Cells(1, num).Address, "$")(1)
End Function

maybe that's the key to know what is going on... only I have no idea

**bagullo** · 02-25-2011, 05:02 AM

FOUND IT!!!
it looks more like a bug than anything,
but found out that error happened because I had selected a graphobject by code (!!)
for some reason when that's the selection, ws.column("A:B") style reference does not work!

gotcha!