Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with a "If" "Else" VB code
Hello all,
Newbe here, Could someone help me with the following code. If a number is not there, then go on to the next step. But I'm misisng somthing. Thanks Sub R_2_R() Application.ScreenUpdating = False With ActiveSheet Cells.Select Cells.EntireColumn.AutoFit Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:="rq by ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:="rcn ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns("C:C").Select Selection.Style = "Currency" Range("D2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:="dpd ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Columns("D:D").Select Selection.TextToColumns Destination:=Range("D:d"), DataType:=xlFixedWidth, _ FieldInfo:=Array(0, 3), TrailingMinusNumbers:=True Range("E2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Replace What:="bk ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=".pdf", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("A1").Select Dim RngToFilter As Range Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=031" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="031", Replacement:="031 Check", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=032" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="032", Replacement:="032 Check", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=033" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="033", Replacement:="033 Check", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=614" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="614", Replacement:="614 Check", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=800 wire" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="800 wire", Replacement:="800 Wire", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=900 ach" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="900 ach", Replacement:="900 ACH", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=900 cc" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="900 cc", Replacement:="900 Credit", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=631" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="631", Replacement:="631 Wire", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=710" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="710", Replacement:="710 Netting", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=711" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="711", Replacement:="711 Netting", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=712" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="712", Replacement:="712 Netting", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=713" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="713", Replacement:="713 Netting", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=731" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="731", Replacement:="731 Wire", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=914" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="914", Replacement:="914 Check", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=961" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="7961", Replacement:="961 Wire", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False Set RngToFilter = .Range("A:E") RngToFilter.AutoFilter Field:=5, Criteria1:="=933" With .AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then .AutoFilterMode = False Else Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:="933", Replacement:="933 Credit Card", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If End With Range("A1").Select .AutoFilterMode = False End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |