Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel2000: Weird behaviour in VBA
Hi
When writing a VBA procedure, somehow I can't refer to workbooks by their name anymore. P.e. in Watch window Workbooks("ReadRep") returns an error "Subscript out of range". At same time p.e. Workbooks(1) works. So I have to use p.e. Workbooks(1).Sheets("Raport").Range("B6:H" & [RowCount] + 5).ClearContents instead of Workbooks("ReadRep").Sheets("Raport").Range("B6:H" & [RowCount] + 5).ClearContents , but I can never be sure, that there were no workbooks opened before. Another odd thing: I needed in 2 columns to replace all commas with periods. The code ActiveWorkbook.Range("G:H")..Replace What:=",", Replacement:=".", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False did return an error. I tried ActiveWorkbook.Range("G:H").Select Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False , and ActiveWorkbook.Columns("G:H").Select Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False , and again did get an error - on Select command. The error message was "Run-time error '438': Object doesn't support this property or method" Has someone a clue, what is going on? Thank in advance! -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel2000: Weird behaviour in VBA
It sounds like you have the code in a different spreadsheet to the one on which you want to operate - but I would have thought you had noticed that. You could check using activeworkbook.name and thisworkbook.name but again I would have thought you would have noticed this. Regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=507142 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel2000: Weird behaviour in VBA
Hi
The code was in active workbook, but your tip did gave me a start anyway. I had to refer to workbook usinf it's name with extension. I.e. Workbooks("ReadRep.xls") instead Workbooks("ReadRep"), etc. Strange, from MS Help : Workbooks Property Example This example activates the workbook Book1.xls. Workbooks("BOOK1").Activate And with replace was my fault too - I did forget to determine the worksheet :-(( -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "tony h" wrote in message ... It sounds like you have the code in a different spreadsheet to the one on which you want to operate - but I would have thought you had noticed that. You could check using activeworkbook.name and thisworkbook.name but again I would have thought you would have noticed this. Regards -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=507142 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel2000: Weird behaviour in VBA
Glad to be of help. You may notice that a new workbook does not have a .xls extension it is only once it has been saved. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=507142 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to re-set excel2000 to open a second instance in a separate wi | Excel Discussion (Misc queries) | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) | |||
Excel2000: Weird chart behaviour | Excel Discussion (Misc queries) | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Weird interger calculation | Excel Worksheet Functions |