0

I'm not sure if something like this is possible I've been told by co-workers that it can't because of the nature of strings versus formulas. I would like to take the string:

'"=" & ValRange.offset(0,0).address

With a range declared in my VBA routine and convert is to a formula. In my routine I have the following code:

Sub GetRange()
Dim strSource As String
Dim HomeRange As Range
Dim ValRange As Range

Set ValRange = Range("k1")
Set HomeRange = Range("e6")

strSouce = HomeRange.Value
strSource = Replace(strSource, """", "")
Range("e7") = strSource

End Sub

As you can see I've tried stripping characters but it does not seem to work (I understand as the code currently is that it would strip all quotes but just wanted to use as example. Any thoughts on converting this to a formula is appreciated. Matt

Community
  • 1
  • 1
Matthew Somers
  • 27
  • 2
  • 9
  • What is the formula you want to convert this string to? An Excel formula or a VBA formula? – Ron Rosenfeld Mar 28 '15 at 23:11
  • My thought is that when the formula resolves in vba like you would do directly with strSource = "=" & ValRange.offset(0,0).address in VBA routine it would recognize that ValRange is a declared variable and resolve ok. Basically it seems like the additional quote ' at start is the problem. Just wondering if there was some replace(strSource,,) order that could change cell string into just strSource = "=" & ValRange.offset(0,0).address. and not strSource = ""=" & ValRange.offset(0,0).address" – Matthew Somers Mar 28 '15 at 23:42
  • Just to confirm I am trying to get the address in the cell not the value. – Matthew Somers Mar 28 '15 at 23:45
  • Address of what in what cell? I really don't understand the specifics of what you are trying to do. Please be specific as to exactly what you want to do. Provide specific examples and not generalities. Is the only string you want to convert something containing the VBA Offset function and returning an address? – Ron Rosenfeld Mar 28 '15 at 23:52
  • I suspect that, in order to accomplish what you want to do, you will need to be actually writing code that modifies or creates new VBA code. This is advanced programming, and also requires changing the Trust Center settings so as to allow this (it is a technique also used by certain computer viruses). See [Progamming in the VBA Editor](http://www.cpearson.com/Excel/vbe.aspx) by Chip Pearson for a detailed discussion. – Ron Rosenfeld Mar 29 '15 at 00:03
  • That sounds reasonable and yes probably a bit advanced for the project. Thanks for the follow-up. – Matthew Somers Mar 30 '15 at 05:32

1 Answers1

0

Let's say you ValRange is defined as cell A1. In cell A2, you have your row offset value (say 0), and in cell A3 you have your column offset value (say 2). In cell A4, enter =ADDRESS(ROW(OFFSET(A1,A2,A3)), COLUMN(OFFSET(A1,A2,A3))) You'll get $C$1 in cell A4 as your value.

So, the 2 offsets give you a cell that is the appropriate offset you want. Then you take the row, and column of that cell and send it to the address function to get your address.

To convert that address back to the value in the offset cell, use =INDIRECT(A4). This will give you the value of whatever is in cell C1.

Of course, you can just take these functions and call them from VB code if you'd prefer.

Samaga
  • 98
  • 5
  • Thanks...sorry I was just using offset as an example I wasn't really trying to figure out how to get the value...I was really just interested in converting a string '"=" & ValRange.offset(0,0).address to just "=" & ValRange.offset(0,0).address and have it resolve as a formula. I had really long strings so I reduced one to get the essence. Seems like you can't take a string and change it to formula because a string cannot hold a formula and you will always get an application error...although if I do strForm = "=" & ValRange.offset(0,0).address (a string) and set to a range it resolves ok. – Matthew Somers Mar 28 '15 at 23:34
  • [Take a look at this previous SO entry.](http://stackoverflow.com/questions/21133715/excel-convert-string-to-a-formula) – Samaga Mar 29 '15 at 00:48
  • Ok...thanks..I've used Evaluate before...works well...I was hoping to find out if a string can be converted into a formula. I'm still not sure why it can't but I guess I will lament and move on. – Matthew Somers Mar 29 '15 at 02:25