Concatenation Problem

[icdastar]

Hi,

I'm having an issue with concatnation. I'm trying to take an active cell and add a "-" sign behind it. This is my code, but I keep getting an "object required" error.


Dim b As Variant
Set b = ActiveCell.Value
Dim neg As String
Set neg = "-"
Dim A As Range
Set A = ActiveCell.Select
A = b & neg


Please help! Thanks!

 

[jcrater]

Const NEG as string = "-"

Dim a as String
Dim b as String

a = activecell.value
b = a & NEG

 

[icdastar]

Thanks! Do I have to designate that b should be replace what was in the active cell or will it automatically assume that?

 

[jcrater]

I don't understand the question.

If you want to replace the value in the active cell, do
Activecell.Value = Activecell.Value & NEG

 

[TonyJollans]

Hi icdastar,

The reason you get "Object Required" errors is because you are misusing the Set command. You use Set to assign object pointers to object variables and you use Let (optional and normally omitted) to assign other values to other variables. Usage must be consistent, either

Set ObjectVariable = Object

or

[Let] NonObjectVariable = Value

Applied to your code, we get ..

            Dim b As Variant
            [red]Let[/red] b = ActiveCell.Value
            Dim neg As String
            [red]Let[/red] neg = "-"
            Dim A As Range
            Set A = ActiveCell [green]' (no .Select)[/green]
            A = b & neg

As already pointed out, this is much too complicated for a simple concatenation, for which you only need the single statement given, but I hope the explanation helps a bit.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

The shortest oneliner:
ActiveCell = ActiveCell & "-"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244