Applying this code to additional columns ?.

alexsunny123 · Jan 21st 2021

Hello,

Code works for columns C & D, want to make the code work for columns H & I.

Code

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim x As Range, y As Long
If Not Intersect(Target, Columns("C:D")) Is Nothing Then
Select Case Target.Column
Case Is = 4
y = Cells(Rows.Count, 4).End(3).Row
For Each x In Range(Cells(2, 4), Cells(y, 4))
If x.Value <> "" And x.Offset(, -1) <> "" Then
x.Offset(, -2) = x / x.Offset(, -1)
Else
x.Offset(, -2) = ""
End If
Next x
Case Is = 3
y = Cells(Rows.Count, 3).End(3).Row
For Each x In Range(Cells(2, 3), Cells(y, 3))
If x.Value <> "" And x.Offset(, 1) <> "" Then
x.Offset(, -1) = x.Offset(, 1) / x
Else
x.Offset(, -1) = ""
End If
Next x
End Select
End If
End Sub

Display More

thanks

alexsunny

**royUK** · Jan 21st 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Jan 21st 2021

Change the Intersect section as below to H:I

I've added code to ensure only one cell is selected to avoid errors if the selection spans both columns.

I've simplified the code

Code

Option Explicit
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim x As Range, y As Long
Dim col As Integer
If Target.coont > 1 Then Exit Sub
If Not Intersect(Target, Columns("H:I")) Is Nothing Then
col = Target.Column
y = Cells(Rows.Count, col).End(3).Row
For Each x In Range(Cells(2, col), Cells(y, col))
If x.Value <> "" And x.Offset(, -1) <> "" Then
x.Offset(, -2) = x / x.Offset(, -1)
Else
x.Offset(, -2) = ""
End If
Next x
End If
End Sub

Display More

Make sure that you read and understand the Forum Rules before posting again.

Share