Placing a Variable in A Formula

[sdeaks]

Hi all,
this is my first post so a bit about myself here:
I am working as an intern for an accounting company and they are having me attempt to automate some of their very repetitive excel work.
This is my first serious foray into VBA code but I think I am getting the hang of it.

My current code is as follow

Dim Tablarray As Range
tablearray = Selection

Sheets("Bank Transactions-Machias").Select
Range("Table3[[#Headers],[Entered in TAM]]").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[Debit/Credit]],"tablarray",2,FALSE)"

The error comes in when the code tries to use "tablearray" in the vlookup saying "compile error, syntax error"

Can anyone tell me how to rewrite the vlookup so that the range is the selection?
if we can get that to work, can anyone also tell me how to "fix" (manually I would use $ signs or hit f4 if that makes it clearer) the selection so if the formula is filled down the same selection will be used in each cell?

Thanks for any help!

[pareshj]

Hi,

Try this,

"=VLOOKUP([@[Debit/Credit]]," & tablearray & ",2,FALSE)"

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[sdeaks]

To paresh and frog, thank you for the attempts. They unfortunately did not work. Just out of curiosity what do the "&"s do?

[sdeaks]

If it is of any help I have 2 columns selected for my table array in a different tab then the one the formula is running in

[AlphaFrog]

The &'s concatenate text to build the formula. You want your current selection's range address concatenated within the formula string.

When you say it doesn't work, be specific; code errors, wrong formula, formula returns the wrong result.

What is an example working VLOOKUP formula that you want to duplicate via code?


I changed two lines in your code. Did you make both changes?

Please Login or Register  to view this content.

EDIT:
New change in Blue

[pareshj]

Hi,

Please provide the output of the code and also the issues?
With & you can reference the value of variable where it got used.

[sdeaks]

Frog, thank you for editing the address to xlr1c1, it works for the most part now. It is running the vlookup wonderfully now, even defaulted to locking in the selection (which I wanted).
The new problem I am finding is that it takes the current tab selection as the input for the variable. So for example I have 2 tabs. In tab 1 I am running the vlookup from a table in tab 2.
The way the code is written, however, forces the "selection" for the table array to be the cell in which the formula is being inputted. It is circularly referencing itself. Do you have any idea as to how to make the "selection" come from the 2nd tab?

Dim Tablarray As Range
Set Tablarray = Selection

Sheets("Bank Transactions-Machias").Select
Range("Table3[[#Headers],[Entered in TAM]]").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[Debit/Credit]]," & Tablarray.Address(1, 1, xlR1C1, True) & ",2,FALSE)"

is how my code currently reads. Thank you for helping me out thus far. I appreciate it. I do not mean to be a bother.
I will be trying to research this on my own in the meantime but it never hurts to ask.

[AlphaFrog]

The way the code is written, however, forces the "selection" for the table array to be the cell in which the formula is being inputted. It is circularly referencing itself. Do you have any idea as to how to make the "selection" come from the 2nd tab?

You're welcome.

I do not understand what you want selected.


When posting code to the forum, surround it with CODE tags (See my signature block). It's a forum rule.

[sdeaks]

Sorry!!!
I do not know how to do that but I will poke around until I figure it out, new people problems. Is it the wrap code tags button?
as for what I want here is a sheet that should visual explain
Book1.xlsx

basically I am running the formula in tab 1, my table array come from tab 2, with the code as written (trying out code tags here)

Please Login or Register  to view this content.

the "tablarray" variable is thinking the selection is the active cell the formula is being written in. I want the selection to be the cells I select on the 2nd tab.

Hope that helped and that I did everything right

[AlphaFrog]

the "tablarray" variable is thinking the selection is the active cell the formula is being written in. I want the selection to be the cells I select on the 2nd tab.

If Sheet2 cells E1:F28 is selected when you start the macro, then this...
Tablarray.Address(1, 1, xlR1C1, True)
...puts Sheet2!E1:F28 within the formula.

Isn't that what you want? I don't see it putting the active cell address in the formula.

[sdeaks]

That is what I want but for some reason I am getting the cell the formula is running in as the selection. I think it may be because I am making the selection before running the macro. Then, as it runs, it chooses a new active cell (which is necessary to put the formula itself in the correct cell) and thinking that is then the selection.

as it reads (sheet name modified for privacy)

Please Login or Register  to view this content.

is what I get from running it. (The formula is running in cell k10056 by the way, so the selection used is the cell above it)

Please Login or Register  to view this content.

is what I want
I would use a static reference but the range changes a lot and in more ways than I could think to compensate for and really just needs a person to select it

[mikerickson]

Try removing all the quote marks (other than the first and last) from the code in the OP.

[AlphaFrog]

The code above should work as intended. The Tablarray variable doesn't change when you select a new sheet or range.

Do you have two versions of the code and possibly it's the wrong macro your executing?

[sdeaks]

Okay I think it is never registering my original selection because I make it before I run the macro, how do I set it so that I could run the macro then make my selection? I would try

Please Login or Register  to view this content.

but it doesn't (to my knowledge) allow a person to interact with the workbook while it is waiting for your entry

[AlphaFrog]

Okay I think it is never registering my original selection because I make it before I run the macro, how do I set it so that I could run the macro then make my selection? I would try

Please Login or Register  to view this content.

but it doesn't (to my knowledge) allow a person to interact with the workbook while it is waiting for your entry

Is the worksheet where you make the selection still the active sheet when you run the macro? It is expected to be.

[sdeaks]

My Hero! if I were a gullible princess I would kiss you and hope you turn into a prince.
Alas, all I have to offer is my sincere thanks. That worked.
Now I just need to teach the person who does this work how to do it which will be another adventure altogether.

Thank you many times over, you have been a great help. I hope we cross paths again (the other half being that it us both trying to help the same person.