Replace Number In A Column With A Different Number

[Dragon1]

I am new to VBA and have been looking at Google posts for over two weeks trying to find a VBA that shows how to replace, for example, the number "105" with "99" in a column. I am pretty sure this is simple but I have just missed something being I am so new.

Data Range: H1:H350,000
Goal: Replace the number 105 with 99
File Name: Code A
Worksheet Name: 1811
Excel Version: 2016


Thanks in advance for your help.

[Logit]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Target.Worksheet.Range("H1:H350000")) Is Nothing Then
If Range("H1:H350000").Value = "105" Then
Range("H1:H350000").Value = "99"
End If
End If

End Sub




Paste the above into the worksheet module for sheet 1811.

[jolivanes]

From a regular module (Module1?)

Sub Or_This()
'Thanks to Kenneth Hobson for this "Speed Up" code part
Dim glb_origCalculationMode%
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = "Changing values in a very large range..."
.EnableCancelKey = xlErrorHandler
End With


With Range("H1:H350000")
.Replace 105, 99
End With


With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

[keebellah]

To keep in mind if the macro is being called form another macro or function



Sub Or_This()
'Thanks to Kenneth Hobson for this "Speed Up" code part
Dim glb_origCalculationMode%
glb_origCalculationMode = Application.Calculation

'There can also come in handy if the macro has been called from another one
'and you want to keep the ScreenUpdating, DisplayAlerts etc as when the macro was invoked
'Note by Hans Hallebeek (keebellah)
Dim glb_origScreenUpdating%
glb_origScreenUpdating = Application.ScreenUpdating

Dim glb_origDisplayAlerts%
glb_origDisplayAlerts = Application.DisplayAlerts

Dim glb_EnableEvents%
glb_EnableEvents = Application.EnableEvents

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = "Changing values in a very large range..."
.EnableCancelKey = xlErrorHandler
End With


With Range("H1:H350000")
.Replace 105, 99
End With


With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = glb_origScreenUpdating
.EnableEvents = glb_EnableEvents
.DisplayAlerts = glb_origDisplayAlerts
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

[Dragon1]

The coding you provided worked like a charm. I very much appreciate your assistance. Thanks.

[Dragon1]

I very much appreciate your update as a great reference if, in the future, I have a macro being called from another macro or function. Thanks.