none
VBA Excel : Inserting value of variable after cell current contents

    Question

  • I have a big question that would help me out hugely if someone can suggest a solution for this problem (mega thanks if someone can get this working been racking my brain on this for months now).

    Is it possible to say you had a variable just anything such as:
    
    Dim varName as String
    varName = "HP 5740"
    Is there anyway of making len (I think I'd need to use to do this, but any other suggestion would be great), to go to the end of the cells contents and insert the variable value in there (being column K), going down all the worksheets contents until it comes to the last value in column J and stopping there?

    If someone could help me on this I'd really appreciate it.

    Thanks,
    Jeremy.
    Thursday, May 07, 2009 2:40 PM

Answers

  • Do you mean append variable contents to end of activecells contents?

    varName = "HP 5740"
    Activecell.value = activecell.value & varName

    Cheers
    Friday, May 08, 2009 3:42 PM

All replies

  • Hi,

    You can use code like this to get the next empty cell in a column.

    Either start at the top and going down

    Range("J1").End(xlDown).Offset(1)= varName

    of starting at the bottom and coming up

    Cells(rows.Count,"J").end(xlup).offset(1)=varname


    The End method performs, in code, the action you would do with keyboard strokes END DOWN_ARROW
    Cheers
    Thursday, May 07, 2009 3:45 PM
  • Hi,

    You can use code like this to get the next empty cell in a column.

    Either start at the top and going down

    Range("J1").End(xlDown).Offset(1)= varName

    of starting at the bottom and coming up

    Cells(rows.Count,"J").end(xlup).offset(1)=varname


    The End method performs, in code, the action you would do with keyboard strokes END DOWN_ARROW
    Cheers
    Sorry I think I confused the question at the end I mean basically:

    The cell has a value in it finding the end value of the activecell and inserting a variables value into that.

    So I'd have to get some kind of function to find the end character and add the variable in there.

    I have this gut feeling this would work if i explained it well enough sorry for the confusion I will explain it further for you, sorry about this just its really making me think allot haha all good though!

    I did find this macro someone had made ages ago cant remember the link now, but it basically found the last character in a cell (in my case at the start it would be empty).

    It then removed the last character in that cell, would it be possible to do the reverse where it inserted a value instead of removing it, I could then replace that value with the variable and make it work like that.

    Hope this clarify's the situation.

    Any help would be greatly appreciated.
    This works removes the last character in a cell: ActiveCell = Left(ActiveCell, Len(ActiveCell) - 1)
    Friday, May 08, 2009 3:38 PM
  • Do you mean append variable contents to end of activecells contents?

    varName = "HP 5740"
    Activecell.value = activecell.value & varName

    Cheers
    Friday, May 08, 2009 3:42 PM
  • Thats it precisely I thought it was going to be something very complex but thats ace!!

    Thanks ever so much,
    Jeremy.
    Friday, May 08, 2009 3:59 PM