Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Ways To Reference Ranges
I need a very short way to refer to single cell ranges in a worksheet. I
have a calculator that I use in VBA and it references cells in many worksheets. So I want to keep the code as clean as possible. For example, 'this is what I currently do Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value Is there a shorter way to write this? I think I have seen people use [B4] * [C6] or something like that, but how can I include the worksheet? Thanks in Advance! -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Ways To Reference Ranges
Try this
Dim ws As Worksheet Set ws = Worksheets("Labor") With ws .Range("C4").Value = _ .Range("B4").Value * .Range("C6").Value End With "Ryan H" wrote: I need a very short way to refer to single cell ranges in a worksheet. I have a calculator that I use in VBA and it references cells in many worksheets. So I want to keep the code as clean as possible. For example, 'this is what I currently do Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value Is there a shorter way to write this? I think I have seen people use [B4] * [C6] or something like that, but how can I include the worksheet? Thanks in Advance! -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Ways To Reference Ranges
You could use:
Worksheets("Sheet1").[a1] or even ['Sheet 99'!x88] or if you know the code name for that sheet Sheet1.[a1] But it turns out, that the [] notation is slower than the range(...).value syntax. Personally, I find the range(...).value easier to read/debug/write--even though there are more characters that I have to type. Ryan H wrote: I need a very short way to refer to single cell ranges in a worksheet. I have a calculator that I use in VBA and it references cells in many worksheets. So I want to keep the code as clean as possible. For example, 'this is what I currently do Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value Is there a shorter way to write this? I think I have seen people use [B4] * [C6] or something like that, but how can I include the worksheet? Thanks in Advance! -- Cheers, Ryan -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different Ways To Reference Ranges
Thanks for the replay Dave. Just curious, how do you know it is slower with
the brackets? -- Cheers, Ryan "Dave Peterson" wrote: You could use: Worksheets("Sheet1").[a1] or even ['Sheet 99'!x88] or if you know the code name for that sheet Sheet1.[a1] But it turns out, that the [] notation is slower than the range(...).value syntax. Personally, I find the range(...).value easier to read/debug/write--even though there are more characters that I have to type. Ryan H wrote: I need a very short way to refer to single cell ranges in a worksheet. I have a calculator that I use in VBA and it references cells in many worksheets. So I want to keep the code as clean as possible. For example, 'this is what I currently do Sheets("Labor").Range("B4").Value * Sheets("Labor").Range("C6").Value Is there a shorter way to write this? I think I have seen people use [B4] * [C6] or something like that, but how can I include the worksheet? Thanks in Advance! -- Cheers, Ryan -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference to external ranges | Excel Programming | |||
variable reference ranges | Excel Discussion (Misc queries) | |||
Named ranges which seem to reference old workbooks/worksheets | Excel Discussion (Misc queries) | |||
Reference Ranges | Excel Programming | |||
How to Variable Reference Row Ranges? | Excel Programming |