asked on

Is there a better way?

Hi All,

I have the following script and it works just fine:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r1 As String
r1 = Target.row

Dim a1 As String
a1 = Target.Address

Worksheets("Brs").Range(a1).offset(0, 24).value = Worksheets("CurrC").Cells(r1, "G").value

'The only problem is that I find it tedious to count out the left hand side of the equation 'since I have many to do.

'I would much rather use something like this and use the column identifier (in this case AE) rather than counting out every little thing:

Worksheets("Brs").Cells(r1, "AE").value = Worksheets("CurrC").Cells(r1, "G").value

'but I cannot seem to get it to work. So in short it is the left hand side of this equation that I am trying to make better.

End if

End Sub

Any ideas?

thanks!

nutsch

One idea, you should change your variable naming conventions, it's very confusing to have a1 in vba be a variable rather than a cell reference.

The line of code you've highlighted should work, but it will hard code the column to AE, or 31, rather than 24 columns to the right of the changed cell.

As I'm not sure exactly what you're trying to accomplish, I'll have a hard time helping more than that, unless you detail more what you start with and what you want to end up with.

Regards,

Thomas