Find cell value and replace multiple instances

[creases]

Hi,

I've written this to code but the issue I'm having is that the code appears to replace only single instance of each unique text and not all of them. The columns are fixed, but the rows will be dynamic and there will be other tables (separated by a line break) above and below. The other issue I'm having is that I will have Run-time errors when the find text (12", 16" or 24") are not found. I don't think the 'On Error GoTo' I added in is working the way I need it to. Thank you in advance for any help or advice.

Current Data:

MARK	TYPE	SERIES	DEPTH	QTY.	LENGTH
BK1	BLOCK	A	14"	2	12"
BK1	BLOCK	B	14"	4	12"
BK1	BLOCK	C	14"	6	12"
BK2	BLOCK	D	14"	8	16"
BK2	BLOCK	E	14"	10	16"
BK3	BLOCK	F	14"	12	24"
BK3	BLOCK	G	14"	14	24"

Desired Result:

MARK	TYPE	SERIES	DEPTH	QTY.	LENGTH
BK1	BLOCK	A	14"	LF	2'
BK1	BLOCK	B	14"	LF	4'
BK1	BLOCK	C	14"	LF	6'
BK2	BLOCK	D	14"	LF	11'
BK2	BLOCK	E	14"	LF	13'
BK3	BLOCK	F	14"	LF	24'
BK3	BLOCK	G	14"	LF	28'

Current Code:
Sub Block()

'Change 12" Blocking to LF
On Error GoTo NotFound:
Cells.find(What:="12""", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "=(1)*RC[-1]"
Selection.NumberFormat = "0""'"""
ActiveCell.Value = ActiveCell.Value
Selection.Offset(0, -1).Select
ActiveCell.Value = "LF"
NotFound:

'Change 16" Blocking to LF
On Error GoTo NotFound2:
Cells.find(What:="16""", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "=(1.33)*RC[-1]"
Selection.NumberFormat = "0""'"""
ActiveCell.Value = ActiveCell.Value
Selection.Offset(0, -1).Select
ActiveCell.Value = "LF"
NotFound2:

'Change 24" Blocking to LF
On Error GoTo NotFound3:
Cells.find(What:="24""", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "=(2)*RC[-1]"
Selection.NumberFormat = "0""'"""
ActiveCell.Value = ActiveCell.Value
Selection.Offset(0, -1).Select
ActiveCell.Value = "LF"
NotFound3:

End Sub

 

[Marc L]

Hi,​

​

just following the VBA help example of Range.Find method avoids this bad coding from the Macro Recorder and On Error as well …​

​

 

[Marc L]

According to VBA help a demonstration as a beginner starter to paste to the worksheet module :​

Sub Demo1()
    Dim V, R%, Rf As Range
        V = [{"12""",1;"16""",1.33;"24""",2}]
        Application.ScreenUpdating = False
    With UsedRange.Columns(6)
           .WrapText = False
        For R = 1 To UBound(V)
               Set Rf = .Find(V(R, 1), , xlValues, xlWhole)
            If Not Rf Is Nothing Then
                Do
                    Rf.NumberFormat = "0'_W"
                    Rf(1, 0).Resize(, 2).Value2 = Array("LF", Rf(1, 0).Value2 * V(R, 2))
                    Set Rf = .FindNext(Rf)
                Loop Until Rf Is Nothing
            End If
        Next
    End With
        Application.ScreenUpdating = True
End Sub

 

[creases]

Hi,​

​

just following the VBA help example of Range.Find method avoids this bad coding from the Macro Recorder and On Error as well …​

​

Hi, where can I find this VBA help example? Is there a post on this forum? I searched but couldn’t find what you were referring to.

 

[Marc L]

​

In VBE side whatever by the help search box or just placing the text cursor on a code statement and hitting F1 key …​

Or in MsDocs website …​