VBA: How to combine string from two different cells into one?

[mtbthepro]

Hi there,
I would like to know if there is a way to combine two strings together into one cell. I am talking about putting numbers together, there is a set of Account#which are on different lines and they are displayed like |Account#:12542-|5651256|" The pipe (|) is there to show the column line that runs in between them. I would like that line to disappear and the number after the "-" to be in the same cell as "Account#: " so end result should look something like "|Account#:12542-5651256|"
I know there is a way to do it which is called concatenate and I have tried it a couple of different ways but it doesn't work.
If you can help me with this I would greatly appreciate it.




example one

      For N = nlast To 1 Step -1
                        If ws.Cells(N, 1).Value Like "*ACCOUNT*" Then
                            ws.Cells(N, 1).Value = str1
                            ws.Rows(N).EntireRow.Font.Bold = True
                            ws.Rows(N).Offset(0, 1) = str2
                            ws.Rows(N) = CONCATENATE(str1 & str2)  
                        End If
                    Next N

example two

        st= .Cells(.Rows.Count, "A")
                    For i = i To 1 Step -1
                If (ws.Cells(i, 1).Value Like "*ACCOUNT*") Then
                    st1= RTrim(.Cells(i, "A") & .Cells(i, "B"))
                        End If
                    Next i

 

[mtbthepro]

See if this macro does what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CombineAccountNumberParts()
  Dim R As Long, Data As Variant
  Data = Range("A1:B" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "ACCOUNT[#]:*" Then
      Data(R, 1) = Data(R, 1) & Data(R, 2)
      Data(R, 2) = ""
    End If
  Next
  Range("A1").Resize(UBound(Data), 2) = Data
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Yes sir that did work. Can you explain what the function UBound does?

 

[Rick Rothstein]

Yes sir that did work. Can you explain what the function UBound does?

This line of code...

Data = Range("A1:B" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)

creates a two-dimensional array in memory from the cells in columns A and B from Row 1 to row number which has the last piece of data in it (no matter what column that last data item is in). The function UBound(Data), which also could have been written UBound(Data,1) if I wanted to specify the optional second argument, returns the number of row elements stored in the array. I know there are two column indexes because of how I loaded the array, but if I had used some non-specific method to specify the number of columns that were used to load the array, I could use the UBound function to tell me like this UBound(Data,2).

 

[mtbthepro]

This line of code...

Data = Range("A1:B" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)

creates a two-dimensional array in memory from the cells in columns A and B from Row 1 to row number which has the last piece of data in it (no matter what column that last data item is in). The function UBound(Data), which also could have been written UBound(Data,1) if I wanted to specify the optional second argument, returns the number of row elements stored in the array. I know there are two column indexes because of how I loaded the array, but if I had used some non-specific method to specify the number of columns that were used to load the array, I could use the UBound function to tell me like this UBound(Data,2).

That helps a bit to understand the code, Now to make it work for all sheet, this is what you would do right?

Sub CombineAccountNumberParts()
  Dim R As Long, Data As Variant, ws As Workbook
   For Each ws In Sheets
  Data = ws.Range("A1:B" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
  For R = 1 To UBound(Data)
    If ws.Data(R, 1) Like "ACCOUNT[#]:*" Then
      ws.Data(R, 1) = ws.Data(R, 1) & ws.Data(R, 2)
      ws.Data(R, 2) = ""
    End If
  Next R
  Range("A1").Resize(UBound(Data), 2) = Data
  Next ws
End Sub

 

[Rick Rothstein]

That helps a bit to understand the code, Now to make it work for all sheet, this is what you would do right?

Sub CombineAccountNumberParts()
  Dim R As Long, Data As Variant, ws As Workbook
   For Each ws In Sheets
  Data = ws.Range("A1:B" & [B][COLOR="#008000"]ws.[/COLOR][/B]Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
  For R = 1 To UBound(Data)
    If [COLOR="#FF0000"]ws.[/COLOR]Data(R, 1) Like "ACCOUNT[#]:*" Then
      [COLOR="#FF0000"]ws.[/COLOR]Data(R, 1) = [COLOR="#FF0000"]ws.[/COLOR]Data(R, 1) & ws.Data(R, 2)
      [COLOR="#FF0000"]ws.[/COLOR]Data(R, 2) = ""
    End If
  Next R
  [COLOR="#008000"]ws.[/COLOR]Range("A1").Resize(UBound(Data), 2) = Data
  Next ws
End Sub

The Data variable is a VB array... it is held in memory and has no direct connection to the worksheet, so it does not take a worksheet reference... I marked them in red and they should be removed. Also, you missed two ranges that need a worksheet reference... I marked them in green and you need to add them.

 

[mtbthepro]

The Data variable is a VB array... it is held in memory and has no direct connection to the worksheet, so it does not take a worksheet reference... I marked them in red and they should be removed. Also, you missed two ranges that need a worksheet reference... I marked them in green and you need to add them.

So the array needs to get stored somewhere...also For Each ws In Worksheet is giving me an error runtime error 13 type mismatch

 

[Rick Rothstein]

So the array needs to get stored somewhere...also For Each ws In Worksheet is giving me an error runtime error 13 type mismatch

Should be Worksheets (plural, which is the name of the collection), not Worksheet (singular, which is a worksheet object).

 

[mtbthepro]

Should be Worksheets (plural, which is the name of the collection), not Worksheet (singular, which is a worksheet object).

Well thank you, sir, much appreciate the help but why does ws.cells.find give me compile error now. Says method or data member not found.

 

[Rick Rothstein]

Well thank you, sir, much appreciate the help but why does ws.cells.find give me compile error now. Says method or data member not found.

In looking at your code, you declared ws as Workbook... should be Worksheet.

 

[mtbthepro]

In looking at your code, you declared ws as Workbook... should be Worksheet.

Interesting but still didn;t help, I am a little confused now. Why does it matter if its a workbook or a worksheet....now the error is with ws.data says it not found.

 

[Rick Rothstein]

Interesting but still didn;t help, I am a little confused now. Why does it matter if its a workbook or a worksheet....now the error is with ws.data says it not found.

ws.data? I told you in Message #14 that data did not take a worksheet (or even workbook) reference. As for why it makes a difference about worksheet or workbook... it is because they are completely different things with different properties and methods, so you have to use the correct data type for the object you declare it as so that the correct properties and methods are available when you go to use them. Maybe you should post your code as you now have it so we can see what you have at the moment.