Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
(This is a super-simple softball lofted to the group...)
I've got a (column) range in each of 2 sheets, call them TestSrc & TestDest. I want to have the cells in TestDest dependent on the other sht's range, so that whenever a value's entered in, for example, TestSrc!B2, it instantly shows up in TestDest!C2. (I know I could manually-select the destination-cell and enter "=TestSrc!B2", but there's a couple-hundred 'source-cells' & I'd like to automate it.) Also: the range of cells in TestSrc sheet is in one column but is not contiguous. I feel comfortable w/ building the looping code for this, but am stumbling over the very basic syntax of how to set one variable on one sht equal to a var on another--and do it so that any changes on source sht would instantly show up on the other w/out any copying, pasting, etc. What I've tried: Sub LinkCellsDiffShts() Dim SrcRge As Range Dim DestRge As Range Set SrcRge = Worksheets("TestSrc").Range("B2") Set DestRge = Worksheets("TestDest").Range("C2") Sheets("TestDest").Range(DestRge). _ Value = Sheets("TestSrc").Range(SrcRge).Value End Sub I'm getting error msges from this, & don't know how to fix it. Any help would be greatly appreciated [:-)) terry b. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
terry b,
DestRge.Value = SrcRge.Value Jim Cone San Francisco, USA "terry b" wrote in message ups.com... (This is a super-simple softball lofted to the group...) I've got a (column) range in each of 2 sheets, call them TestSrc & TestDest. I want to have the cells in TestDest dependent on the other sht's range, so that whenever a value's entered in, for example, TestSrc!B2, it instantly shows up in TestDest!C2. (I know I could manually-select the destination-cell and enter "=TestSrc!B2", but there's a couple-hundred 'source-cells' & I'd like to automate it.) Also: the range of cells in TestSrc sheet is in one column but is not contiguous. I feel comfortable w/ building the looping code for this, but am stumbling over the very basic syntax of how to set one variable on one sht equal to a var on another--and do it so that any changes on source sht would instantly show up on the other w/out any copying, pasting, etc. What I've tried: Sub LinkCellsDiffShts() Dim SrcRge As Range Dim DestRge As Range Set SrcRge = Worksheets("TestSrc").Range("B2") Set DestRge = Worksheets("TestDest").Range("C2") Sheets("TestDest").Range(DestRge). _ Value = Sheets("TestSrc").Range(SrcRge).Value End Sub I'm getting error msges from this, & don't know how to fix it. Any help would be greatly appreciated [:-)) terry b. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("B:B")) Is Nothing Then With Target Worksheets("TestDest").Range(.Address).Offset(0, 1).Value = ..Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "terry b" wrote in message ups.com... (This is a super-simple softball lofted to the group...) I've got a (column) range in each of 2 sheets, call them TestSrc & TestDest. I want to have the cells in TestDest dependent on the other sht's range, so that whenever a value's entered in, for example, TestSrc!B2, it instantly shows up in TestDest!C2. (I know I could manually-select the destination-cell and enter "=TestSrc!B2", but there's a couple-hundred 'source-cells' & I'd like to automate it.) Also: the range of cells in TestSrc sheet is in one column but is not contiguous. I feel comfortable w/ building the looping code for this, but am stumbling over the very basic syntax of how to set one variable on one sht equal to a var on another--and do it so that any changes on source sht would instantly show up on the other w/out any copying, pasting, etc. What I've tried: Sub LinkCellsDiffShts() Dim SrcRge As Range Dim DestRge As Range Set SrcRge = Worksheets("TestSrc").Range("B2") Set DestRge = Worksheets("TestDest").Range("C2") Sheets("TestDest").Range(DestRge). _ Value = Sheets("TestSrc").Range(SrcRge).Value End Sub I'm getting error msges from this, & don't know how to fix it. Any help would be greatly appreciated [:-)) terry b. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Hey, Jim,
Thanks for simplifying things! Your suggestion does what I was trying to do in line 6, but evidently when I included the 'Sheets' & 'Range' properties I was clouding things up for no good reason.... One other question: When referring to a variable such as TestSrc, can you use "Range(TestSrc)"? Whenever I try to do that, I get this: "Run-time error '1004': Method 'Range' of object '_Global' failed"....Can't you use a variable name as an argument in 'Range'? Again, thanks much for the help. terry b. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Hey, Bob [:-))
thanks for taking the time to reply! Way back when you were posting (relatively-simple) answers to my questions about the Offset function (exceltip.com, months ago), I found it pretty easy to follow what you sd. But when you start talking about "worksheet event code" and whatnot, I see that there's still a mountain of concepts for me to grok, before I'm able to follow what you're trying to explain.... Have an excellent day! terry b. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
terry b.
Yes you can use a variable when specifying a range. Unfortunately you don't identify the data type of the variable you are trying to use. I try to use some sort of data type prefix on all variables, so that when posting to a newsgroup or looking at the variable in the 787th line of the module, I or others will know what it is. The are conventions for doing this, but something like these can help... lng for Long sng for Single dbl for Double rng for Range shp for Shape cmd for CommandBar str for String obj for Objects you don't have a prefix for To answer your question... If "TestSrc" is a range object established with code something like...Set TestScr = Worksheets(1).Range("B5:B10") then you use it by itself: TestScr.Interior.ColorIndex = 40 If "TestScr" is a String something like... TestScr = "Fudge" then it cannot be used to identify a range. However if "TestScr" is a Name that refers to a Range that was established by using the Name box in the top left corner of a worksheet OR "TestSCr" is a name that refers to a Range that was established by something like... ActiveWorkbook.Names.Add Name:="TestScr", RefersTo:="=Sheet1!$A$1:$C$20" Then use the name by enclosing it in quotation marks, your example would become...Range("TestSrc") I went on much too long, but hope it helps. Regards, Jim Cone San Francisco, USA "terry b" wrote in message oups.com... Hey, Jim, Thanks for simplifying things! Your suggestion does what I was trying to do in line 6, but evidently when I included the 'Sheets' & 'Range' properties I was clouding things up for no good reason.... One other question: When referring to a variable such as TestSrc, can you use "Range(TestSrc)"? Whenever I try to do that, I get this: "Run-time error '1004': Method 'Range' of object '_Global' failed"....Can't you use a variable name as an argument in 'Range'? Again, thanks much for the help. terry b. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Hello, Jim
I don't mind the detail; it all helps! One last thing: What about the issue of the reference between the source cell & the destination cell being a "live" one? What I mean is, if there's value in sheet "TestSrc" cell B2, and I go to sheet "TestDest" cell C2 and enter, "=TestSrc!B2", the destination cell immediately takes on the value of the source cell, and 5 minutes later if I alter the source cell, the dest. cell is automatically-altered--without having to enter the function again. It's a "live" connection.... BUT if I write a snippet of code like: Sub LinkCellsDiffShts() Dim rngSource As Range Dim rngDestination As Range Set rngSource = Worksheets("TestSrc").Range("B2") Set rngDestination = Worksheets("TestDest").Range("C2") rngDestination.Value = rngSource.Value End Sub yes, the destination cell takes on the source value, but if I go back 5 minutes later and alter the source, the destination doesn't change. Unless I run the code again. How does a person code a "live" dependence between the cells? terry b. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
terry b.
Question: "How does a person code a "live" dependence between the cells?" You use code to add the formula to the destination cell. rngDestination.Formula = "=TestSrc!B2" Regards, Jim Cone "terry b" wrote in message ups.com... Hello, Jim I don't mind the detail; it all helps! One last thing: What about the issue of the reference between the source cell & the destination cell being a "live" one? What I mean is, if there's value in sheet "TestSrc" cell B2, and I go to sheet "TestDest" cell C2 and enter, "=TestSrc!B2", the destination cell immediately takes on the value of the source cell, and 5 minutes later if I alter the source cell, the dest. cell is automatically-altered--without having to enter the function again. It's a "live" connection.... BUT if I write a snippet of code like: Sub LinkCellsDiffShts() Dim rngSource As Range Dim rngDestination As Range Set rngSource = Worksheets("TestSrc").Range("B2") Set rngDestination = Worksheets("TestDest").Range("C2") rngDestination.Value = rngSource.Value End Sub yes, the destination cell takes on the source value, but if I go back 5 minutes later and alter the source, the destination doesn't change. Unless I run the code again. How does a person code a "live" dependence between the cells? terry b. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Hello, Jim
Hard-coding the formula into the destination cell is understood. BUT this brings me back to why I wanted to use variables to refer to the source- and destination-cells. Because the 1st link is [shts change every time] B2 C2, next is B6 C3, next is B10 C4, next B14 C5, and so on for well over 50 entries. So I needed to use variables to refer to source & destination, so that I could use For...Next or Do...Loop, and step the respective variables the necessary increments. Do you see what I'm saying? If I specifically-coded the first destination-cell "=TestSrc!B2", then how would I increment the cell upward every time w/out having to write the specific-code 50+ different times? terry b. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
terry b.
I think what we have here is "mission creep"? <g '-------------------------------------------------- Sub TestSomeMore() Dim lngFirst As Long Dim lngSecond As Long Dim objSht1 As Excel.Worksheet Dim objSht2 As Excel.Worksheet Set objSht1 = Worksheets(1) Set objSht2 = Worksheets(2) lngSecond = 2 For lngFirst = 2 To 22 Step 4 objSht2.Cells(lngSecond, 2).Formula = "= " & objSht1.Name & "!" & _ objSht1.Cells(lngFirst, 2).Address lngSecond = lngSecond + 1 Next 'lngFirst End Sub '------------------------------------- Regards, Jim Cone "terry b" wrote in message oups.com... Hello, Jim Hard-coding the formula into the destination cell is understood. BUT this brings me back to why I wanted to use variables to refer to the source- and destination-cells. Because the 1st link is [shts change every time] B2 C2, next is B6 C3, next is B10 C4, next B14 C5, and so on for well over 50 entries. So I needed to use variables to refer to source & destination, so that I could use For...Next or Do...Loop, and step the respective variables the necessary increments. Do you see what I'm saying? If I specifically-coded the first destination-cell "=TestSrc!B2", then how would I increment the cell upward every time w/out having to write the specific-code 50+ different times? terry b. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Hey, Jim!
Thanks very, very much for putting up with this 'mission creep' <pun. You've been beyond generous w/ your time, & have given me much to think over.... Have an excellent Sunday night [:-)) terry b. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
newbie ?: set variables, different shts, equal to each other
Terry,
You can, but isn't TestSrc and TestDest worksheet names in this case? -- HTH RP (remove nothere from the email address if mailing direct) "terry b" wrote in message oups.com... Hey, Jim, Thanks for simplifying things! Your suggestion does what I was trying to do in line 6, but evidently when I included the 'Sheets' & 'Range' properties I was clouding things up for no good reason.... One other question: When referring to a variable such as TestSrc, can you use "Range(TestSrc)"? Whenever I try to do that, I get this: "Run-time error '1004': Method 'Range' of object '_Global' failed"....Can't you use a variable name as an argument in 'Range'? Again, thanks much for the help. terry b. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Compare Rows on 2 shts .... | Excel Discussion (Misc queries) | |||
Newbie Macro Query - Clearing Variables and Assigning a Variable | Excel Programming | |||
Randomize three variables subject to sum always equal to 1 | Excel Programming | |||
Randomize three variables subject to sum always equal to 1 | Excel Programming | |||
Copying shts from one wbk to another | Excel Programming |