Help with Formula

[SUZZI]

Hi

i have data in col1 and col2 i need to search for first
letter of the col1 if its "TOTAL" then need to
concatinate col1 with col2 in col1. I do not know how
many lines col1 would be.

Can anyone help please

col1 col2

UNKNOWN
BROWN HAPPY
HAPPY DIRT
TOTAL POS T OFFICE PURCHASES
PENS CAMEL
DISKS SONY
TOTAL POS T OFFICE PURCHASES

i have started the code but i am struck/getting error
here is the code i have worked on

Sub Colctotsearch()
Dim i As Integer
Dim j As Integer
Dim sSearchWord As String
sSearchWord = "TOTAL"
Range("C10:C71").Select ' for now i presume 71 lines
'Range("c10:C71").Select

On Error GoTo End_of_Input_Data

For i = 1 To 1000

Selection.Find(What:=sSearchWord,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False,
SearchFormat:=False).Activate

j = ActiveCell.Row

Range("H2").Select
' I AM STRUCK HERE......
'ActiveCell.FormulaR1C1 = "=R" + j + "]C3&R["
+ j + 1 + "]C3"
ActiveCell.FormulaR1C1 = "=R[j]C3&R[j+1]C3"
Range("H1").Select
Selection.Copy
Range("C" & j).Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H11,C&j").Select
Range("C&j").Activate
Application.CutCopyMode = False
Selection.ClearContents

Range("C" & (j + 1) & ":" & "C" & (j +
100)).Select
'Range(sStart & (j + 1) & ":" & sEnd & (j +
100)).Select
Next i

End_of_Input_Data:

On Error GoTo 0
End Sub

Thanks

 

[Dick Kusleika]

Suzzi

Based on your description, not your code, try this

Sub ConcatTotal()

Dim cell As Range
Dim Rng As Range

Set Rng = Range("c1", Range("c65536").End(xlUp))

For Each cell In Rng.Cells
If UCase(Left(cell.Value, 5)) = "TOTAL" Then
cell.Value = cell.Value & cell.Offset(0, 1).Value
End If
Next cell

End Sub