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:
Desired Result:
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
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