How To replace If Q

[John]

I have the following code which replace certain values in column B if they
exists by another value. I wish to add to this by only replacing these
values if Column in equals a certain value. For example if Column A = London
or Paris or Madrid, then if Column B = 228 change it to 301. As you will see
below I have quite a few numbers to replace but only if Column A = London or
Paris or Madrid. At the moment the code only changes if it finds a specified
number in B it changes to that also specified

Any help would be appreciated

Thanks



Sub Format_FasFaxNo()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Sales Mix").Select

Range("A1").Select


Columns("B:B").Select
Selection.Replace What:="228", Replacement:="301", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="229", Replacement:="302", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="230", Replacement:="303", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="231", Replacement:="304", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="232", Replacement:="305", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="233", Replacement:="306", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="234", Replacement:="307", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="235", Replacement:="308", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="236", Replacement:="309", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="237", Replacement:="310", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="238", Replacement:="311", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="239", Replacement:="312", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="240", Replacement:="313", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="241", Replacement:="314", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="242", Replacement:="315", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="243", Replacement:="316", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="244", Replacement:="317", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="245", Replacement:="318", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="246", Replacement:="319", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="247", Replacement:="320", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False







With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True


Sheets("Master").Select
Range("A1").Select

End Sub

 

[Bob Phillips]

Sub Format_FasFaxNo()
Dim iLastRow As Long

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Sales Mix").Select

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value >= 228 And Cells(i, "B").Value <= 247 Then
If Cells(i, "A").Value = "London" Or _
Cells(i, "A").Value = "Paris" Or _
Cells(i, "A").Value = "Madrid" Then
Cells(i, "B").Value = Cells(i, "B").Value + 73
End If
End If
Next i

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Master").Select
Range("A1").Select

End Sub

 

[John]

Thanks Bob, my only difficulty is that I have a series of numbers to change
and I can't be sure that they will always be source number + 73.

 

[Bob Phillips]

Then try something like

Sub Format_FasFaxNo()
Dim iLastRow As Long

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Sales Mix").Select

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "A").Value = "London" Or _
Cells(i, "A").Value = "Paris" Or _
Cells(i, "A").Value = "Madrid" Then
Select Case Cells(i, "B").Value
Case 228: Cells(i, "B").Value = 310
Case 229: Cells(i, "B").Value = 311
'etc.
End Select
End If
Next i

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Master").Select
Range("A1").Select

End Sub

 

[Gary Keramidas]

doesn't i have to be dimmed?

--


Gary

Sub Format_FasFaxNo()
Dim iLastRow As Long

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Sales Mix").Select

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B").Value >= 228 And Cells(i, "B").Value <= 247 Then
If Cells(i, "A").Value = "London" Or _
Cells(i, "A").Value = "Paris" Or _
Cells(i, "A").Value = "Madrid" Then
Cells(i, "B").Value = Cells(i, "B").Value + 73
End If
End If
Next i

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Master").Select
Range("A1").Select

End Sub

 

[Bob Phillips]

Not if there is no Option Explicit.

 

[John]

Thanks once again Bob, thats exactly what I'm looking for