Hi every ones,
I have attached to the Water Board to enter a huge amount of data in Excel. But some of the character values are repeated in the same column. So, I decided to enter them by entering a SINGLE value.
But the current file is worked for Column M only.
VBA Code :
>>> use code - tags <<<
Constants:
Column M (1:DM, 2:AG, 3:IW, 4:WSW, 5:CW)
Column N (1:DW, 2:AW, 3:TW, 4:TW/DW, 5:ATW, 6:O)
Column Q (1:Ag, 2:In, 3:Ur, 4:Ru, 5:Other)
Example :
If I enter 1 in column M, it should be change to DM
If I enter 4 in column M, it should be change to WSW,
If I enter 1 in column Q, it should be change to Ru
If I enter 3 in column Q, should be change to Ur
And so on as in the excel sheet.
I have attached to the Water Board to enter a huge amount of data in Excel. But some of the character values are repeated in the same column. So, I decided to enter them by entering a SINGLE value.
But the current file is worked for Column M only.
VBA Code :
>>> use code - tags <<<
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const ColRangesList As String = "M,N,Q,R,U,X"
Const RowRangesList As String = "AR5:AV5,AR6:AW6,AR7:AV7,AR8:AU8,AR9:AV9,AR11:AU11"
Dim RowOffset As Long
Dim ColOffset As Long
RowOffset = 1
ColOffset = 0
If Target.Cells.CountLarge = 1 Then
Dim ColRanges() As String: ColRanges = Split(ColRangesList, ",")
Dim RowRanges() As String: RowRanges = Split(RowRangesList, ",")
Dim CosRange As Range, RowsRange As Range, cel As Range
Dim n As Long
For n = 0 To UBound(ColRanges)
Set CosRange = Columns(ColRanges(n))
Set RowsRange = Range(RowRanges(n))
If Not Intersect(Target, CosRange) Is Nothing Then
Set cel = RowsRange.Find(Target.Value, RowsRange.Cells(RowsRange.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlWhole)
If Not cel Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Target.Value = cel.Offset((RowOffset), ColOffset).Value
Application.EnableEvents = True
Application.ScreenUpdating = True
'Exit For
End If
End If
Next n
End If
End Sub
Constants:
Column M (1:DM, 2:AG, 3:IW, 4:WSW, 5:CW)
Column N (1:DW, 2:AW, 3:TW, 4:TW/DW, 5:ATW, 6:O)
Column Q (1:Ag, 2:In, 3:Ur, 4:Ru, 5:Other)
Example :
If I enter 1 in column M, it should be change to DM
If I enter 4 in column M, it should be change to WSW,
If I enter 1 in column Q, it should be change to Ru
If I enter 3 in column Q, should be change to Ur
And so on as in the excel sheet.
Attachments
Last edited by a moderator: