Cell Formula in VBA

[Dkso]

Hi,

I need a Macro button, to put a formula into a cell. This Cell will
always be 1(ONE) Cell above a users id but can be in different places
so it must be a macro.

The formula is used to perform a Lookup on the Cell below into a Range
held in a different spreadsheet.
What I have is below and it works EXCEPT the formula that is put into
each Cell has the word "Address" in it and not the actual referance
for the Cell

Is there a way to have this changed to the Cell Referance that I need
or is there a simpler way of doing it (probably knowing me)!

Thanks for any help provided....

Dean

VBA coding

Sub UserName()
ActiveCell.FormulaR1C1 = ActiveCell.Address ******Get ActiveCell
Referance
a = Right(ActiveCell.Address, 1)******Split off Right charater
a = a + 1*Add 1
Add = Left(ActiveCell.Address, Len(ActiveCell.Address) - 1)****Get
rest of Cell Referance
Address = Add & a*******Add both bits back together
ActiveCell.FormulaR1C1 = _
"=PROPER(VLOOKUP(Address,'List of User Names.xls'!Names,2,FALSE)&"
"&VLOOKUP(Address,'List of User Names.xls'!Names,3,FALSE))"****Formula
to be placed in Cell

End Sub



Regards Dean
(e-mail address removed)
www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

 

[Frank Kabel]

Hi
the reason for this is that you pass Address as string and not as
variable. You may change this to
ActiveCell.FormulaR1C1 = _
"=PROPER(VLOOKUP(" & Address & ",'List of User
Names.xls'!Names,2,FALSE)&"
"&VLOOKUP(" & Address & ",'List of User Names.xls'!Names,3,FALSE))"

Note: You probably could make this formula much simpliere (not sure
what you're trying in your string concatenation steps before assigning
this formula). Can you post an example for a target cell and the
expected formula in this cell

 

[Tom Ogilvy]

All this
ActiveCell.FormulaR1C1 = ActiveCell.Address ******Get ActiveCell
Referance
a = Right(ActiveCell.Address, 1)******Split off Right charater
a = a + 1*Add 1
Add = Left(ActiveCell.Address, Len(ActiveCell.Address) - 1)****Get
rest of Cell Referance
Address = Add & a*******Add both bits back together

could be done with

Address = ActiveCell.Offset(1,0).Address



ActiveCell.formula = _
"=PROPER(VLOOKUP(" & Address & _
",'List of User Names.xls'!Names,2,FALSE)&" & """ """ & _
"&VLOOKUP(" & Address & _
",'List of User Names.xls'!Names,3,FALSE))"


Checking from the immediate window:

? "=PROPER(VLOOKUP(" & Address & _
",'List of User Names.xls'!Names,2,FALSE)&" & """ """ & _
"&VLOOKUP(" & Address & _
",'List of User Names.xls'!Names,3,FALSE))"

=PROPER(VLOOKUP(B21,'List of User Names.xls'!Names,2,FALSE)&"
"&VLOOKUP(B21,'List of User Names.xls'!Names,3,FALSE))

 

[Tom Ogilvy]

Your formula gives me an error Frank. I believe he wants a space
concatenated between the two Vlookups - in any event, you are at least
missing the operator at &" "&

 

[Frank Kabel]

Your formula gives me an error Frank. I believe he wants a space
concatenated between the two Vlookups - in any event, you are at least
missing the operator at &" "&

Hi Tom
correct. I was just too lazy to analyze his string statements and just
saw this as an obvious error - should have tested it nethertheless

Saw your reply and that looks good - Some simplification

 

[Tom Ogilvy]

No criticism intended my friend, just trying to save the user some
frustration.

 

[Frank Kabel]

No criticism intended my friend, just trying to save the user some
frustration.

Hi Tom
not viewed as criticism (what a difficult word to write for a
non-native..). Appreciate this kind of comments as otherwise the OP
rans into trouble

Best regards
Frank

 

[Dean Southgate]

Thanks all, for your help. As I expected you have solved my query and
shorted the code in the process.

Thanks again

Dean