2

I'm trying to Concatenate a range with a single value.

Sub Macro1()
     Dim rngOne As Range, strngOne as String, combos As Range

     'finds the last row and sets it as the ending range
     Dim LastRowColC As Integer
     LastRowColC = Range("C65536").End(xlUp).Row

     Set rngOne = Worksheets(1).Range("C3" & LastRowColC)
     strngOne = "00000"
     combos = rngOne & strngOne

     Range("D3").Select
     Insert combos
End Sub

Why does this not insert the variable "combos" into the cell?

More Explanation (Copied from comments)

Basically I want to take the values in each Cell of column C and add 00000 to the end of all of them. so if C1 was 50 I want the end result to copy over the 50 and replace C1 with 5000000, if C2 was 575 then replace that with 57500000, all throughout the range of data in C.

I would prefer to have it paste over the values in the same column, if that isn't possible. Then for the example you gave I'd want D1= AAA00000, D2=BBB00000, D3 =CCC00000, etc

Community
  • 1
  • 1
MadChadders
  • 127
  • 1
  • 11
  • 3
    I see many errors in the code. `A)` You are finding the last row incorrectly. You should never hard code the row values. xl2007+ has 1048576 rows.. You may want to see [this](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) link on how to get the last row. `B)` To construct your range, `Range("C3" & LastRowColC)` is an incorrect way. You want `Range("C3:C" & LastRowColC)` `C)` Next is the line `combos = rngOne & strngOne`. What are you trying to achieve here? – Siddharth Rout Nov 21 '14 at 20:58
  • Perhaps if you can explain this then it may be easier for us to understand. Let's say you have only 3 cells in Col C which are filled up `C1="AAA"`, `C2 = "BBB"` and `C3 = "CCC"` so what value do you want in `D3`? – Siddharth Rout Nov 21 '14 at 21:04
  • The range varies on Column C but it's never more than 65k values, so I started it there and had it go up to presumably the last data, I understand that error now. Thank you for the link. Basically I want to take the values in each Cell of column C and add 00000 to the end of all of them. so if C1 was 50 I want the end result to copy over the 50 and replace C1 with 5000000, if C2 was 575 then replace that with 57500000, all throughout the range of data in C. – MadChadders Nov 21 '14 at 21:06
  • see my last comment. So what should be the output in the above case in cell `D3` – Siddharth Rout Nov 21 '14 at 21:08
  • I would prefer to have it paste over the values in the same column, if that isn't possible. Then for the example you gave I'd want D1= AAA00000, D2=BBB00000, D3 =CCC00000, etc – MadChadders Nov 21 '14 at 21:11
  • It is possible to paste it in the same range. See the answer that I have posted. I have also shown an alternative way to achieve what you want. You may have to refresh the page – Siddharth Rout Nov 21 '14 at 21:20

1 Answers1

3

Is this what you are trying? I have given you two ways.

WAY 1

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Get last row in Col C
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        '~~> Construct your range
        Set rng = .Range("C3:C" & lRow)

        '~~> Multiply all the cells in the range with 100000
        '~~> so that 55 become 5500000, 123 becomes 12300000 and so on
        rng.Value = Evaluate(rng.Address & "*100000")
    End With
End Sub

WAY 2

Type 100000 in cell D1 and then run this macro

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Get last row in Col C
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        '~~> Construct your range
        Set rng = .Range("C3:C" & lRow)

        '~~> This cell has 100000
        .Range("D1").Copy

        '~~> Paste Special Value/Multiply
        rng.PasteSpecial Paste:=xlPasteValues, _
                         Operation:=xlMultiply, _
                         SkipBlanks:=False, _
                         Transpose:=False

        Application.CutCopyMode = False
    End With
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • yes that works, that will get me on the right track thank you so much for your help! Now I just need to figure out what to do when people add hyphens to their numbers so this can work for all the accounts but this is a life saver, I didn't even think of multiplying by 100000 but that makes perfect sense. – MadChadders Nov 21 '14 at 21:22
  • 1
    `what to do when people add hyphens to their numbers` Do you know that you can use an `If` formula in `Evaluate`? ;) – Siddharth Rout Nov 21 '14 at 21:23
  • One thing that can be done to improve that coding, instead of Set ws= Thisworkbook.Sheets("Sheet1") you can write Set ws = Worksheets(1) Then it runs it as the first worksheet even if the name gets changed ;) – MadChadders Nov 21 '14 at 21:28
  • Yup you can do that :) The code that I have is just an example. Notice the comment `'~~> Change this to the relevant worksheet` – Siddharth Rout Nov 21 '14 at 21:29
  • 1
    The reason why we have that line is so that you can avoid `.Select` and fully qualify your objects :) [Interesting read](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Siddharth Rout Nov 21 '14 at 21:32
  • I'll give you the scenario, sometimes people write numbers as 100000, 200000, etc and sometimes as 100000-23-45 but I need all the items to be in the format of 100000-00-0000 but it can't delete what they put in originally. So I guess if I remove the hyphens [maybe Replace(Replace(strMyString, " ", ""), "-", "") ] right away then multiplied by 1000000 and then used right to insert a hyphen in the 2 spots that would be the easiest? – MadChadders Nov 21 '14 at 21:32
  • How did you arrive at `100000-00-0000` for `100000-23-45`? if you remove the hyphens the number becomes `1000002345` and if you multiply by `100000` then the number becomes `100000234500000` – Siddharth Rout Nov 21 '14 at 21:35
  • sorry those are examples of what it should be 100000 should end up as 100000-00-0000, 100000-23-45 should end up as 100000-23-4500, 200000 should end up 200000-00-0000, 400000-10 should end up as 400000-10-0000, etc – MadChadders Nov 21 '14 at 21:38
  • 2
    I think your main query has been answered and this should go as a new question. :) Anyways I now need to hit the sack. It is already 3:00 AM in this part of the planet :P I would recommend asking a new question as your original question has been answered. Ensure that you ask a detailed question with all the scenario possible. Include few examples as well :) Gnite! – Siddharth Rout Nov 21 '14 at 21:40