View Full Version : [SOLVED:] Replace Number In A Column With A Different Number
Dragon1
03-09-2017, 08:24 PM
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
03-09-2017, 08:35 PM
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
03-09-2017, 09:27 PM
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
03-10-2017, 01:31 AM
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
03-11-2017, 06:06 PM
The coding you provided worked like a charm. I very much appreciate your assistance. Thanks.
Dragon1
03-11-2017, 06:10 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.