Use Variable Inside Formula Added By Macro Code

excelfinder · Jun 3rd 2008

I want to find an ID number from location "thecell" which is set as a Range variable. This is the code where I receive an error:

Code

ActiveCell.Formula = "Mid(" & thecell & ", Find(""id="", " & thecell & ", 1) + 3, 2)"

I receive a "compile error: expected: end of statement" message with highlights from the double quotes after the first thecell& until the double quotes before the second &thecell.

**norie** · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Could you post an example of the formula as it would appear on the worksheet?

Wigi · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Give a specific example since this is not very clear to me.

In any case, there should be an = sign in front of MID.

Robert B · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Hi and welcome

do you actually need to put a formula in the activecell; could you use something like

Code

dim ID as long


ID = mid(thecell,3,2) ' if the ID number including prefix varies use LEN(thecell)-2 instead of 2
activecell = ID

or have I misunderstood ytour intention?

Robert

**norie** · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

excelfinder

Are you sure you receive that error?

Because it compiles fine for me.:confused:

Code

thecell = "A1"
ActiveCell.Formula = "=Mid(" & thecell & ", Find(""id="", " & thecell & ", 1) + 3, 2)"

Wigi · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Quote from norie
Are you sure you receive that error?
Code
[B]thecell = "A1"[/B]
ActiveCell.Formula = "=Mid(" & thecell & ", Find(""id="", " & thecell & ", 1) + 3, 2)"

Quote from excelfinder

I want to find an ID number from location "thecell" which is set as a Range variable.

excelfinder · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

norie
#1 This is how I would like the formula to appear on the worksheet:
mid(THECELL,find("id=",THECELL,1) +3, 2).
#2 That's the error I receive but I noticed I am using

Code

Set thecell=ActiveCell

because I receive a "Run-time error '91': Object variable or With block variable not set" error otherwise.

robert
I don't necessarily need the formula in the activecell but I would prefer it to double check the data. I would need to use find in that formula which may work but regardless I received an "Error: Type Mistmatch" when replacing the line of code.

Wigi
I took out the = sign in one of my attempts trying everything. This formula results in the same compile error as soon as I move off of the line. I can't even run the macro.

Code

ActiveCell.Formula = "=Mid(" & thecell & ", Find(""id="", " & thecell & ", 1) + 3, 2)"

**norie** · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

excelfinder

It really would help to see more than one line of code or even some sample data.

Wigi pointed out my error regarding the variable being a range.

But than can easily be fixed.

For example.

Code

Set thecell = Range("A1")
ActiveCell.Formula = "=Mid(" & thecell.Address & ", Find(""id="", " & thecell.Address & ", 1) + 3, 2)"

Wigi · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Hi

consider

Code

Dim thecell As Range
    
    Set thecell = Range("A1")
    
    ActiveCell.Formula = "=Mid(" & thecell.Address & ",FIND(""id=""," & thecell.Address & ",1)+3,2)"

Wigi

excelfinder · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

This is everything leading up to the error:

Code

Dim thecell as Range
Cells.Find(What:="var1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Offset(1, 0).Activate
Set thecell = ActiveCell
Range("A1").Select
Selection.End(xlToRight).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "ID"
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=Mid(" & thecell.Address & ",  Find(""id="", " & thecell.Address & ", 1) + 3, 2)"

Adding ".Address" basically fixed it. However, is it possible for the address not to be static (no $ before column/row)? I have multiple lines where I would like to copy the code down so I want to make sure the cell location changes appropriately.

Wigi · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Use the AddressLocal property instead of the Address property.

The parameters for AddressLocal can be (0, 0):

Code

thecell.AddressLocal(0, 0)

Wigi

**norie** · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

If you are trying to put this formula in multiple cells then there are probably far better ways.

excelfinder · Jun 3rd 2008

Re: Vba Use Variable Inside Formula

Perfect. Thank you for the help!

Use Variable Inside Formula Added By Macro Code

Participate now!

Share