Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Is it possible to use variables in a "Set" statement?
For example, I have the following lines: With Worksheets("Lookup") Set General_kV = ("Lookup").Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Why do you need 90 + Set statements?
If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If -- Jim Cone Portland, Oregon USA http://tinyurl.com/XLCompanion .. .. .. "IanC" wrote in message news:mxxao.84806$y_2.69065@hurricane... Is it possible to use variables in a "Set" statement? For example, I have the following lines: With Worksheets("Lookup") Set General_kV = ("Lookup").Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
On 8/17/2010 2:04 PM, Jim Cone wrote:
Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If Has anyone done a speed test to see if there is any appreciable difference between using the two? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Mike S laid this down on his screen :
On 8/17/2010 2:04 PM, Jim Cone wrote: Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If Has anyone done a speed test to see if there is any appreciable difference between using the two? I agree with Jim's comments. Typically, referencing the objects directly doesn't add to resource overhead, AND also usually results in greater speed because VB[A} doesn't have to allocate resources required when storing refs in variables. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Jim Cone wrote :
Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If -- Jim Cone Portland, Oregon USA http://tinyurl.com/XLCompanion . . . "IanC" wrote in message news:mxxao.84806$y_2.69065@hurricane... Is it possible to use variables in a "Set" statement? For example, I have the following lines: With Worksheets("Lookup") Set General_kV = ("Lookup").Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian Couple things occur to me after reading your approach... 1. I'm not sure why you're using workbook level names for ranges on a specific sheet, but I recommend you change them to sheet-level names so they're exclusive to the sheet. 2. Once the defined name ranges are localized to Sheets("Lookup"), your For...Next loop seems a reasonable approach on its own, but I'd ditch loading the names into a variable since your loop can work directly on them without the added overhead. Example... Dim n as Object, wksSource As Worksheet Set wksSource = ThisWorkbook.Sheets("Lookup")'just for clarity For Each n in wksSource.Names If Left$(n.RefersTo, 8) = "=Lookup!" Then With wksSource.Range(n) 'do stuff End With End If Next n Also, if you have a specific list of named ranges that you want to work with then you could store them into a delimited string and use the InStr() function to validate that n is one you want, OR you could just iterate the string var in a loop and only operate on the desired named ranges in your list. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Jim
Depending on the combination various ComboBoxes on a form, differing ranges from the Lookup sheet are copied to the Checklist sheet. As the same named ranges are used in multiple combinations, my code is shorter (and easier to write) by adding 90+ Set statements then using General_kV.Copy .Range("A6") instead of using Worksheets("Lookup").Range("General_kV").Copy .Range("A6") in several locations. I'm simply trying to reduce the code further. -- Ian -- "Jim Cone" wrote in message ... Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If -- Jim Cone Portland, Oregon USA http://tinyurl.com/XLCompanion . . . "IanC" wrote in message news:mxxao.84806$y_2.69065@hurricane... Is it possible to use variables in a "Set" statement? For example, I have the following lines: With Worksheets("Lookup") Set General_kV = .Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Garry
Speed isn't really an issue. Even on the slowest laptops we have the time taken to process the code is negigible. -- Ian -- "GS" wrote in message ... Mike S laid this down on his screen : On 8/17/2010 2:04 PM, Jim Cone wrote: Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If Has anyone done a speed test to see if there is any appreciable difference between using the two? I agree with Jim's comments. Typically, referencing the objects directly doesn't add to resource overhead, AND also usually results in greater speed because VB[A} doesn't have to allocate resources required when storing refs in variables. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Garry
Speed isn't really an issue. Even on the slowest laptops we have the time taken to process the code is negigible. -- Ian -- "GS" wrote in message ... Mike S laid this down on his screen : On 8/17/2010 2:04 PM, Jim Cone wrote: Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If Has anyone done a speed test to see if there is any appreciable difference between using the two? I agree with Jim's comments. Typically, referencing the objects directly doesn't add to resource overhead, AND also usually results in greater speed because VB[A} doesn't have to allocate resources required when storing refs in variables. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Garry
About your 2 points below. 1. I wasn't aware there is such a distinction as workbook level names & sheet-level names. How do you determine which they are and change between them? I use Insert Name Define... to create the names. There isn't an option to specify workbook or worksheet names. 2. I appreciate the pointer that I don't need to use a variable, but if the names are localised to the sheet would I need to add this line? If Left$(n.RefersTo, 8) = "=Lookup!" Then I only put that in because there are 3 names referring to an external workbook and needed to work only with the Lookup sheet names. Where you added the line 'do stuff the stuff I need to do is the equivalent of Set X = Worksheets("Lookup").Range(X) I suppose in your code this would be Set n = Worksheets("Lookup").Range(n) This is really the problem I'm having. All the rest is interesting, but not essential. -- Ian -- "GS" wrote in message ... Jim Cone wrote : Why do you need 90 + Set statements? If you already have named ranges why not use them... If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then 'do something End If -- Jim Cone Portland, Oregon USA http://tinyurl.com/XLCompanion . . . "IanC" wrote in message news:mxxao.84806$y_2.69065@hurricane... Is it possible to use variables in a "Set" statement? For example, I have the following lines: With Worksheets("Lookup") Set General_kV = ("Lookup").Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian Couple things occur to me after reading your approach... 1. I'm not sure why you're using workbook level names for ranges on a specific sheet, but I recommend you change them to sheet-level names so they're exclusive to the sheet. 2. Once the defined name ranges are localized to Sheets("Lookup"), your For...Next loop seems a reasonable approach on its own, but I'd ditch loading the names into a variable since your loop can work directly on them without the added overhead. Example... Dim n as Object, wksSource As Worksheet Set wksSource = ThisWorkbook.Sheets("Lookup")'just for clarity For Each n in wksSource.Names If Left$(n.RefersTo, 8) = "=Lookup!" Then With wksSource.Range(n) 'do stuff End With End If Next n Also, if you have a specific list of named ranges that you want to work with then you could store them into a delimited string and use the InStr() function to validate that n is one you want, OR you could just iterate the string var in a loop and only operate on the desired named ranges in your list. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Jim, Mike & Garry
Thanks for all your comments which were interesting to read, but didn't address the central question. While some of your comments may be valid, and may serve to make my eventual code run faster or use less resources, the main question remains: Can I define set statements using something like Set X = Worksheets("Lookup").Range(X) where X is automatically replaced with the name of a range? If it's possible, then how? If not the specifics, perhaps you could point me in the right direction. If it's NOT possible, please put me out of my misery :-) Many thanks. -- Ian -- "IanC" wrote in message news:mxxao.84806$y_2.69065@hurricane... Is it possible to use variables in a "Set" statement? For example, I have the following lines: With Worksheets("Lookup") Set General_kV = ("Lookup").Range("General_kV") Set General_mA = .Range("General_mA") Set General_Timer = .Range("General_Timer") (...etc. approx 90 other Set statements). End With I can recover the named ranges from the Lookup worksheet but I don't know how (or if) it's possible to apply them to a variable in the Set statement. What I'm hoping for is something along the lines of the following code but I need both instances of X to be substituted with General_kV, General_mA, General_Timer, etc. Sub SetRanges() Set nms = ThisWorkbook.Names For r = 1 To nms.Count X = nms(r).Name If Left(nms(r).RefersTo, 8) = "=Lookup!" Then Set X = Worksheets("Lookup").Range(X) End If Next Set nms = Nothing End Sub Any ideas? -- Ian -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
On 18 Ago, 08:19, "IanC" wrote:
shorter (and easier to write) by adding 90+ Set statements then using * * General_kV.Copy .Range("A6") instead of using * * Worksheets("Lookup").Range("General_kV").Copy .Range("A6") in several locations. I'm simply trying to reduce the code further. No SET statment required if use named ranges, try: [General_KV].Copy .Range("A6") Bye! Scossa |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
on 8/18/2010, IanC supposed :
Hi Garry About your 2 points below. 1. I wasn't aware there is such a distinction as workbook level names & sheet-level names. How do you determine which they are and change between them? I use Insert Name Define... to create the names. There isn't an option to specify workbook or worksheet names. How local names (sheet-level) are defined in this dialog is they are preceeded by the sheetname and an apostrophe. So... Defined name "General_kV" would be entered as "Lookup!General_kV" to make it sheet-level. This type of defined name will appear in the list in exactly the same fashion. So.., names in the list without the active sheet's name prepended are global (workbook-level) defined names. Use of global names is discouraged as much as possible when a local defined name can work. One main reason is that when you copy sheets to other workbooks the global defined names follow along and could cause name conflicts if the same name was defined in the target workbook. Local defined names do not behave this way as they are unique to the sheet they were defined on. Also, local defined names are reusable on many sheets within a workbook whereas global defined names are unique only to the workbook they are defined in (and so is why name conflicts can occur when we copy/move sheets to another workbook. 2. I appreciate the pointer that I don't need to use a variable, but if the names are localised to the sheet would I need to add this line? If Left$(n.RefersTo, 8) = "=Lookup!" Then I only put that in because there are 3 names referring to an external workbook and needed to work only with the Lookup sheet names. So then, are you saying the three defined names don't exist in the same workbook your code is in? Your code suggests that they do as you define your set statement to 'ThisWorkbook.Names', which suggests these are defined in the workbook containing your code. Your comment is confusing! 'n' is a required object var for use in the For Each loop used to iterate the names collection so you can dtermine if the defined name refs a range on Sheets("Lookup"). If the names were local to the sheet then you would only have to iterate the names collection for Sheet("Lookup") for the desired names (or sheet names if that's what they ref). **Again, it's confusing due to your previous comment about an external workbook. Where you added the line 'do stuff the stuff I need to do is the equivalent of Set X = Worksheets("Lookup").Range(X) I suppose in your code this would be Set n = Worksheets("Lookup").Range(n) No, in my code 'n' refers to the defined name object in the names collection. You should use a different var to hold a range object ref (such as you're already doing with 'X'), but I would make it a bit more descriptive about what it refers to (ie: rngTemp), especially if you're reloading it with numerous values in the same procedure. Otherwise, 'n' is only a temp object var used to hold a ref to each name in the names collection for the purpose of stepping through each name in the names collection. So, to answer your Q with respect to my code sample you would use: Set X = wksSource.Range(n.Name) to set an object var to the range defined by that name's Name property. This is really the problem I'm having. All the rest is interesting, but not essential. -- Ian -- -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Thanks, Scossa.
That did the trick, though I don't understand why. It doesn't work without [ ] round the name, not does it work with just General_kV. I need to specify [Lookup!General_kV].Copy and this works fine. Over the course of this thread I've also come to realise that when I create all these Set statements, I am using more system resources - resources that are not released when I close the workbook. No wonder I kept getting a message saying there are insufficient resources the display the workbook fully after a period of development work on the workbooks! Presumably your method doesn't reserve system resources in this manner, so I shouldn't enounter the same problem. Thanks for all you help. -- Ian -- "Scossa" wrote in message ... On 18 Ago, 08:19, "IanC" wrote: shorter (and easier to write) by adding 90+ Set statements then using General_kV.Copy .Range("A6") instead of using Worksheets("Lookup").Range("General_kV").Copy .Range("A6") in several locations. I'm simply trying to reduce the code further. No SET statment required if use named ranges, try: [General_KV].Copy .Range("A6") Bye! Scossa |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Garry
"GS" wrote in message ... on 8/18/2010, IanC supposed : Hi Garry About your 2 points below. 1. I wasn't aware there is such a distinction as workbook level names & sheet-level names. How do you determine which they are and change between them? I use Insert Name Define... to create the names. There isn't an option to specify workbook or worksheet names. How local names (sheet-level) are defined in this dialog is they are preceeded by the sheetname and an apostrophe. So... Defined name "General_kV" would be entered as "Lookup!General_kV" to make it sheet-level. This type of defined name will appear in the list in exactly the same fashion. So.., names in the list without the active sheet's name prepended are global (workbook-level) defined names. Use of global names is discouraged as much as possible when a local defined name can work. One main reason is that when you copy sheets to other workbooks the global defined names follow along and could cause name conflicts if the same name was defined in the target workbook. Local defined names do not behave this way as they are unique to the sheet they were defined on. Also, local defined names are reusable on many sheets within a workbook whereas global defined names are unique only to the workbook they are defined in (and so is why name conflicts can occur when we copy/move sheets to another workbook. If I understand correctly - When I define a name, I can define it as MyRange or Lookup!MyRange and both will work, but by using Lookup!MyRange I could have another MyRange in another worksheet (eg Checklist!MyRange) without any conflict? 2. I appreciate the pointer that I don't need to use a variable, but if the names are localised to the sheet would I need to add this line? If Left$(n.RefersTo, 8) = "=Lookup!" Then I only put that in because there are 3 names referring to an external workbook and needed to work only with the Lookup sheet names. So then, are you saying the three defined names don't exist in the same workbook your code is in? Your code suggests that they do as you define your set statement to 'ThisWorkbook.Names', which suggests these are defined in the workbook containing your code. Your comment is confusing! My comment is confusing because I'm confused. My VBA programming is all self-taught (with a lot of help from this forum), but much of it is trial and error. I find solutions that work, but they are not necessarily the preferred methods. As far as the range naming goes, if I go to the "Define Name" dialog, "General_kV" is shown as referring to "=Lookup!$A$120:$C$128". One of the "external" names is "Equipment" and is shown as referring to "='C:\[Test Equipment List.xls]Sheet1'!$A$5:$A$30". When I hit F5 in the workbook to bring up the list of used names, "General_kV" appears, but "Equipment" doesn't. I defined both from within my workbook. Hopefully you are no longer confused as I don't know how else to explain it :-( No, in my code 'n' refers to the defined name object in the names collection. You should use a different var to hold a range object ref (such as you're already doing with 'X'), but I would make it a bit more descriptive about what it refers to (ie: rngTemp), especially if you're reloading it with numerous values in the same procedure. Otherwise, 'n' is only a temp object var used to hold a ref to each name in the names collection for the purpose of stepping through each name in the names collection. So, to answer your Q with respect to my code sample you would use: Set X = wksSource.Range(n.Name) to set an object var to the range defined by that name's Name property. What I had been looking for was something whereby I could parse the list of names and automatically create Set statements such that in my line... Set X = Worksheets("Lookup").Range(X) ....both instances of X would be replaced by the range name. It looks like this isn't possible, but it's academic now as Scossa has provided a neat solution that does away with the Set statements altogether. Thanks for all your input. It's been a useful learning experience. -- Ian -- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
IanC explained on 8/20/2010 :
Hi Garry "GS" wrote in message ... on 8/18/2010, IanC supposed : Hi Garry About your 2 points below. 1. I wasn't aware there is such a distinction as workbook level names & sheet-level names. How do you determine which they are and change between them? I use Insert Name Define... to create the names. There isn't an option to specify workbook or worksheet names. How local names (sheet-level) are defined in this dialog is they are preceeded by the sheetname and an apostrophe. So... Defined name "General_kV" would be entered as "Lookup!General_kV" to make it sheet-level. This type of defined name will appear in the list in exactly the same fashion. So.., names in the list without the active sheet's name prepended are global (workbook-level) defined names. Use of global names is discouraged as much as possible when a local defined name can work. One main reason is that when you copy sheets to other workbooks the global defined names follow along and could cause name conflicts if the same name was defined in the target workbook. Local defined names do not behave this way as they are unique to the sheet they were defined on. Also, local defined names are reusable on many sheets within a workbook whereas global defined names are unique only to the workbook they are defined in (and so is why name conflicts can occur when we copy/move sheets to another workbook. If I understand correctly - When I define a name, I can define it as MyRange or Lookup!MyRange and both will work, but by using Lookup!MyRange I could have another MyRange in another worksheet (eg Checklist!MyRange) without any conflict? Correct! 2. I appreciate the pointer that I don't need to use a variable, but if the names are localised to the sheet would I need to add this line? If Left$(n.RefersTo, 8) = "=Lookup!" Then I only put that in because there are 3 names referring to an external workbook and needed to work only with the Lookup sheet names. So then, are you saying the three defined names don't exist in the same workbook your code is in? Your code suggests that they do as you define your set statement to 'ThisWorkbook.Names', which suggests these are defined in the workbook containing your code. Your comment is confusing! My comment is confusing because I'm confused. My VBA programming is all self-taught (with a lot of help from this forum), but much of it is trial and error. I find solutions that work, but they are not necessarily the preferred methods. As far as the range naming goes, if I go to the "Define Name" dialog, "General_kV" is shown as referring to "=Lookup!$A$120:$C$128". One of the "external" names is "Equipment" and is shown as referring to "='C:\[Test Equipment List.xls]Sheet1'!$A$5:$A$30". When I hit F5 in the workbook to bring up the list of used names, "General_kV" appears, but "Equipment" doesn't. I defined both from within my workbook. Hopefully you are no longer confused as I don't know how else to explain it :-( No, in my code 'n' refers to the defined name object in the names collection. You should use a different var to hold a range object ref (such as you're already doing with 'X'), but I would make it a bit more descriptive about what it refers to (ie: rngTemp), especially if you're reloading it with numerous values in the same procedure. Otherwise, 'n' is only a temp object var used to hold a ref to each name in the names collection for the purpose of stepping through each name in the names collection. So, to answer your Q with respect to my code sample you would use: Set X = wksSource.Range(n.Name) to set an object var to the range defined by that name's Name property. What I had been looking for was something whereby I could parse the list of names and automatically create Set statements such that in my line... Set X = Worksheets("Lookup").Range(X) ...both instances of X would be replaced by the range name. It looks like this isn't possible, Also correct! In the case of 'Set' X is an object. In the case of 'Range(X)' X is a string. No-no supreme! but it's academic now as Scossa has provided a neat solution that does away with the Set statements altogether. Exactly what others have been saying as well. Good for Scossa to give it to you the way he did!<g Thanks for all your input. It's been a useful learning experience. -- Ian -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Set" statement with variables?
Hi Garry
<snip but it's academic now as Scossa has provided a neat solution that does away with the Set statements altogether. Exactly what others have been saying as well. Good for Scossa to give it to you the way he did!<g I have to admit I'd been using Set statements because it avoids a lot of typing <bg That said, Scossa's solution of [General_KV].Copy .Range("A6") is a lot shorter than Worksheets("Lookup").Range("General_kV").Copy .Range("A6") and as I already have General_KV.Copy .Range("A6") in place, extending this to [Lookup!General_kV].Copy .Range("A6") isn't a great deal of work, especially with copy/paste. -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming | |||
embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
Call a sub statement in "Personal Macro Workbook" from "ThisWorkbo | Excel Programming | |||
vba: How do I write a "For Each Statement" nested in a "With Statement"? | Excel Programming |