Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Formula
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 -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "SUZZI" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |