Apply ADDRESS formula in VB
in [Programming]
|
From: monden2 on 13 Apr 2010 10:29 Hey Everyone, I got a quickie here. I have made this spreadsheet code using a lot of info on here. Now I wan to use it in VB. =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)) What I want to do, is have the outcome of this formula (i.e. $B$2) to be the ControlSource cell for my TextBox. It has to be flexible, meaning that if any variable change, and because of that the ADDRESS changes, it will automatically update. Thanks in advance!
From: Bob Phillips on 13 Apr 2010 11:42 Why not just bind the textbox to B2? -- HTH Bob "monden2" <monden2(a)discussions.microsoft.com> wrote in message news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com... > Hey Everyone, > > I got a quickie here. I have made this spreadsheet code using a lot of > info > on here. Now I wan to use it in VB. > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)) > > What I want to do, is have the outcome of this formula (i.e. $B$2) to be > the > ControlSource cell for my TextBox. It has to be flexible, meaning that if > any > variable change, and because of that the ADDRESS changes, it will > automatically update. > > Thanks in advance!
From: monden2 on 14 Apr 2010 05:30 Hey Bob, Because the ControlSource depens on the outcome of the formula. The formula is a part of a data validation sheet. In this case, the data in cell A13, A14 and A15 will refer to a specific cell. This formula encorporate will show the address of this cell, which of course varies when the data in cell A1 or A2 or A3 changes. For example. 13 14 15 A 'Account' 'Internal' returns value in Sheet3!B20 -or- 13 14 15 A 'Account' 'External' returns value in Sheet6!B22 -or- 13 14 15 A 'Account' 'Other' returns value in Sheet2!B21 This ADDRESS formula will then have a value 'Sheet3'!$B$20, 'Sheet6'!$B$22 and 'Sheet2'!$B$21 respectively (the ' are for formating purposes because, as far as I read, thats how it should be typed in the ControlSource box). What I want is for my userform Textbox's Controlsource to pick up this value and use it as its reference, so that when the users puts data in the textbox, it will automatically put this data in that cell, to which the ADDRESS formula is referring. Thanks for the help! :D "Bob Phillips" wrote: > Why not just bind the textbox to B2? > > -- > > HTH > > Bob > > "monden2" <monden2(a)discussions.microsoft.com> wrote in message > news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com... > > Hey Everyone, > > > > I got a quickie here. I have made this spreadsheet code using a lot of > > info > > on here. Now I wan to use it in VB. > > > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)) > > > > What I want to do, is have the outcome of this formula (i.e. $B$2) to be > > the > > ControlSource cell for my TextBox. It has to be flexible, meaning that if > > any > > variable change, and because of that the ADDRESS changes, it will > > automatically update. > > > > Thanks in advance! > > > . >
From: monden2 on 14 Apr 2010 08:14 Hey everyone, If anyone was busy making a code for me on this, I am sorry to say that management wanted an additional table. Thus, the ADDRESS formula had to be updated. This is the new one: =IF($A$11="Short Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Long Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),"")) Awaiting your response(s) Thanks! :D "monden2" wrote: > Hey Bob, > > Because the ControlSource depens on the outcome of the formula. The formula > is a part of a data validation sheet. In this case, the data in cell A13, A14 > and A15 will refer to a specific cell. This formula encorporate will show the > address of this cell, which of course varies when the data in cell A1 or A2 > or A3 changes. > > For example. > > 13 14 15 > A 'Account' 'Internal' returns value in Sheet3!B20 > > -or- > > 13 14 15 > A 'Account' 'External' returns value in Sheet6!B22 > > -or- > > 13 14 15 > A 'Account' 'Other' returns value in Sheet2!B21 > > This ADDRESS formula will then have a value 'Sheet3'!$B$20, 'Sheet6'!$B$22 > and 'Sheet2'!$B$21 respectively (the ' are for formating purposes because, as > far as I read, thats how it should be typed in the ControlSource box). > > What I want is for my userform Textbox's Controlsource to pick up this value > and use it as its reference, so that when the users puts data in the textbox, > it will automatically put this data in that cell, to which the ADDRESS > formula is referring. > > Thanks for the help! :D > > > > "Bob Phillips" wrote: > > > Why not just bind the textbox to B2? > > > > -- > > > > HTH > > > > Bob > > > > "monden2" <monden2(a)discussions.microsoft.com> wrote in message > > news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com... > > > Hey Everyone, > > > > > > I got a quickie here. I have made this spreadsheet code using a lot of > > > info > > > on here. Now I wan to use it in VB. > > > > > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)) > > > > > > What I want to do, is have the outcome of this formula (i.e. $B$2) to be > > > the > > > ControlSource cell for my TextBox. It has to be flexible, meaning that if > > > any > > > variable change, and because of that the ADDRESS changes, it will > > > automatically update. > > > > > > Thanks in advance! > > > > > > . > >
From: Bob Phillips on 14 Apr 2010 09:04 I would put something in the worksheet to get that cell reference, and then use say INDIRECT(B2) in the ControlSource for the textbox. -- HTH Bob "monden2" <monden2(a)discussions.microsoft.com> wrote in message news:CEC4D1A7-DD67-4471-9B05-49D1B4D9C984(a)microsoft.com... > Hey everyone, > > If anyone was busy making a code for me on this, I am sorry to say that > management wanted an additional table. Thus, the ADDRESS formula had to be > updated. This is the new one: > > =IF($A$11="Short > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),IF($A$11="Long > Term",ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0)+15,MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)),"")) > > Awaiting your response(s) > > Thanks! :D > > "monden2" wrote: > >> Hey Bob, >> >> Because the ControlSource depens on the outcome of the formula. The >> formula >> is a part of a data validation sheet. In this case, the data in cell A13, >> A14 >> and A15 will refer to a specific cell. This formula encorporate will show >> the >> address of this cell, which of course varies when the data in cell A1 or >> A2 >> or A3 changes. >> >> For example. >> >> 13 14 15 >> A 'Account' 'Internal' returns value in Sheet3!B20 >> >> -or- >> >> 13 14 15 >> A 'Account' 'External' returns value in Sheet6!B22 >> >> -or- >> >> 13 14 15 >> A 'Account' 'Other' returns value in Sheet2!B21 >> >> This ADDRESS formula will then have a value 'Sheet3'!$B$20, >> 'Sheet6'!$B$22 >> and 'Sheet2'!$B$21 respectively (the ' are for formating purposes >> because, as >> far as I read, thats how it should be typed in the ControlSource box). >> >> What I want is for my userform Textbox's Controlsource to pick up this >> value >> and use it as its reference, so that when the users puts data in the >> textbox, >> it will automatically put this data in that cell, to which the ADDRESS >> formula is referring. >> >> Thanks for the help! :D >> >> >> >> "Bob Phillips" wrote: >> >> > Why not just bind the textbox to B2? >> > >> > -- >> > >> > HTH >> > >> > Bob >> > >> > "monden2" <monden2(a)discussions.microsoft.com> wrote in message >> > news:97A17B9B-A050-4B5D-A537-921767BA7D2C(a)microsoft.com... >> > > Hey Everyone, >> > > >> > > I got a quickie here. I have made this spreadsheet code using a lot >> > > of >> > > info >> > > on here. Now I wan to use it in VB. >> > > >> > > =ADDRESS(MATCH(Sheet2!$A$23,INDIRECT("'"&Sheet2!$A$19&"'!$B:$B"),0),MATCH(Sheet2!$A$14,INDIRECT("'"&Sheet2!$A$19&"'!$A$1:$V$1"),0)) >> > > >> > > What I want to do, is have the outcome of this formula (i.e. $B$2) to >> > > be >> > > the >> > > ControlSource cell for my TextBox. It has to be flexible, meaning >> > > that if >> > > any >> > > variable change, and because of that the ADDRESS changes, it will >> > > automatically update. >> > > >> > > Thanks in advance! >> > >> > >> > . >> >
|
Next
|
Last
Pages: 1 2 Prev: Copy sheets to new workbook - subscript error Next: Excel 2007 dynamic chart range |