| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use variable for a range in WITH block
Greetings all. I have the following....
With Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone End With It works fine. However, this is just one of many format changes I need to make. Can I declare a range variable, set it = to the range in question, then use the variable in the With block, so the code is not so cumbersome? This is what I am trying, but it does not work Dim MyRange as Range MyRange = Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) With MyRange .the rest of it End with Any ideas? I've also tried using a string and variant data types, but nothing seems to work. Thank you.l |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use variable for a range in WITH block
Use
Set MyRange = Sheets(...).Range(...) The "Set" is vital. Sam "Greg Snidow" wrote: Greetings all. I have the following.... With Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone End With It works fine. However, this is just one of many format changes I need to make. Can I declare a range variable, set it = to the range in question, then use the variable in the With block, so the code is not so cumbersome? This is what I am trying, but it does not work Dim MyRange as Range MyRange = Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) With MyRange .the rest of it End with Any ideas? I've also tried using a string and variant data types, but nothing seems to work. Thank you.l |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use variable for a range in WITH block
Thank you Sam. I knew I had done it before, but I could not find it.
Anyhow, it works like a charm, and I'll never forget that again. "Sam Wilson" wrote: Use Set MyRange = Sheets(...).Range(...) The "Set" is vital. Sam "Greg Snidow" wrote: Greetings all. I have the following.... With Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone End With It works fine. However, this is just one of many format changes I need to make. Can I declare a range variable, set it = to the range in question, then use the variable in the With block, so the code is not so cumbersome? This is what I am trying, but it does not work Dim MyRange as Range MyRange = Sheets("MacroDEA").Range("C2:" & Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address) With MyRange .the rest of it End with Any ideas? I've also tried using a string and variant data types, but nothing seems to work. Thank you.l |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
| Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
| Run-time Error'91: Object variable or With block variable not set | Excel Programming | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Linear Mode
