Help with code!

[sagar]

Hi folk,

I was working on the following code which works very well but I feel it's becoming really too redundant. You can see that each line is repeated just by changing the range value in order to write in the next column (AA, AB, AC etc.). Since I'd like to use many columns I'd like to avoid writing an endless code. Do you have any idea of how to make it shorter? I hope this is clear... This is the code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
iRow = cell.Row
iCol = cell.Column

' Update Max and Min Back Values
If iCol = 6 And iRow > 4 And iRow < 1000 Then

Application.EnableEvents = False
If IsEmpty(Range("AA" & iRow).Value) And cell.Value = Range("AA1").Value Then Range("AA" & iRow).Value = Range("G" & iRow).Value
If IsEmpty(Range("AB" & iRow).Value) And cell.Value = Range("AB1").Value Then Range("AB" & iRow).Value = Range("G" & iRow).Value
If IsEmpty(Range("AC" & iRow).Value) And cell.Value = Range("AC1").Value Then Range("AC" & iRow).Value = Range("G" & iRow).Value


If Not IsEmpty(Range("AA" & iRow).Value) Then
If cell.Value = Range("AA1").Value And Range("AA" & iRow).Value < Range("G" & iRow).Value Then Range("AA" & iRow).Value = Range("G" & iRow).Value
If cell.Value = Range("AB1").Value And Range("AB" & iRow).Value < Range("G" & iRow).Value Then Range("AB" & iRow).Value = Range("G" & iRow).Value
If cell.Value = Range("AC1").Value And Range("AC" & iRow).Value < Range("G" & iRow).Value Then Range("AC" & iRow).Value = Range("G" & iRow).Value

End If

Application.EnableEvents = True
End If

Next cell

End Sub

[GaryRussell]

You can use the following syntax

Code: Select all

Range(Cells(r,c))

So for example

Code: Select all

for c=27 to 29
   If IsEmpty(Range(cells(iRow,c)).Value) And cell.Value = Range(cells(1,c)).Value Then Range(cells(iRow,c)).Value = Range(cells(iRow,7)).Value
next

[GaryRussell]

In fact you are only referring to single cells at a time so you don't need to use range at all. For example:-

Code: Select all

for c=27 to 29
   If IsEmpty(cells(iRow,c).Value) And cell.Value = cells(1,c).Value Then cells(iRow,c).Value = cells(iRow,7).Value
next

[sagar]

Thanks you so much Gary!