Hi!

I have first name in A1 and last name in A2

When I use the syntax
Sheets("Extract").Range("A" & ERow).Value = ActiveCell.Offset(0,
-2).Value & ActiveCell.Offset(0, -3).Value

Value of ERow is 1
Offset -2 is the first name
Offset -3 is the last name

VBA does not support "&" to get both the names in one cell of a
different sheet?

Please help

I bet that the activecell isn't what you think it should be.

Maybe adding:
msgbox activecell.address(external:=true)
to your code will help you debug the problem.

Maxi wrote:
>
> Hi!
>
> I have first name in A1 and last name in A2
>
> When I use the syntax
> Sheets("Extract").Range("A" & ERow).Value = ActiveCell.Offset(0,
> -2).Value & ActiveCell.Offset(0, -3).Value
>
> Value of ERow is 1
> Offset -2 is the first name
> Offset -3 is the last name
>
> VBA does not support "&" to get both the names in one cell of a
> different sheet?
>
> Please help

--

Dave Peterson

maybe not the ideal way, but

Declare 2 variables

Set Var1 = ActiveCell.Offset(0,-2)
Set VAr2 = ActiveCell.Offset(0,-3)

Sheets("Exrtract").Range("A" & ERow) = Var1 & Var2


HTH

"Maxi" wrote:

> Hi!
>
> I have first name in A1 and last name in A2
>
> When I use the syntax
> Sheets("Extract").Range("A" & ERow).Value = ActiveCell.Offset(0,
> -2).Value & ActiveCell.Offset(0, -3).Value
>
> Value of ERow is 1
> Offset -2 is the first name
> Offset -3 is the last name
>
> VBA does not support "&" to get both the names in one cell of a
> different sheet?
>
> Please help
>
>

Thanx Steve, it worked

One more question. How do I remove the last character of var2
variable?

For instance:
var1 has Justin
var2 has Kemp,

I need only Justin Kemp after concatenation.

Thanx

On Jul 2, 8:42 pm, steve_doc <steve...@discussions.microsoft.com>
wrote:
> maybe not the ideal way, but
>
> Declare 2 variables
>
> Set Var1 = ActiveCell.Offset(0,-2)
> Set VAr2 = ActiveCell.Offset(0,-3)
>
> Sheets("Exrtract").Range("A" & ERow) = Var1 & Var2
>
> HTH
>
>
>
> "Maxi" wrote:
> > Hi!
>
> > I have first name in A1 and last name in A2
>
> > When I use the syntax
> > Sheets("Extract").Range("A" & ERow).Value = ActiveCell.Offset(0,
> > -2).Value & ActiveCell.Offset(0, -3).Value
>
> > Value of ERow is 1
> > Offset -2 is the first name
> > Offset -3 is the last name
>
> > VBA does not support "&" to get both the names in one cell of a
> > different sheet?
>
> > Please help- Hide quoted text -
>
> - Show quoted text -

> VBA does not support "&" to get both the names in one cell of a
> different sheet?

Yes, it does. However, your code will fail if ActiveCell is in columns
A-C, because it can't go 3 columns left.

Are you getting an error message? If so, what kind?

Hth,
Merjet

I was able to remove the last character... Thanx for your help

On Jul 2, 8:48 pm, Maxi <maheshchindar...@gmail.com> wrote:
> Thanx Steve, it worked
>
> One more question. How do I remove the last character of var2
> variable?
>
> For instance:
> var1 has Justin
> var2 has Kemp,
>
> I need only Justin Kemp after concatenation.
>
> Thanx
>
> On Jul 2, 8:42 pm, steve_doc <steve...@discussions.microsoft.com>
> wrote:
>
>
>
> > maybe not the ideal way, but
>
> > Declare 2 variables
>
> > Set Var1 = ActiveCell.Offset(0,-2)
> > Set VAr2 = ActiveCell.Offset(0,-3)
>
> > Sheets("Exrtract").Range("A" & ERow) = Var1 & Var2
>
> > HTH
>
> > "Maxi" wrote:
> > > Hi!
>
> > > I have first name in A1 and last name in A2
>
> > > When I use the syntax
> > > Sheets("Extract").Range("A" & ERow).Value = ActiveCell.Offset(0,
> > > -2).Value & ActiveCell.Offset(0, -3).Value
>
> > > Value of ERow is 1
> > > Offset -2 is the first name
> > > Offset -3 is the last name
>
> > > VBA does not support "&" to get both the names in one cell of a
> > > different sheet?
>
> > > Please help- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -