Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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!
0
BostonBob
Asked:
BostonBob
2 Solutions
 
nutschCommented:
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
0
 
TommySzalapskiCommented:
Instead of Cells(r1, "AE").value
Use Range("AE"&r1).value
0
 
gowflowCommented:
Well lets dissect:

The good news is YES this is valid and you can use it !!!
Worksheets("Brs").Cells(r1, "AE").value = Worksheets("CurrC").Cells(r1, "G").value

However as I see in your code,

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 
...

Open in new window


This affectation is done in the worksheet change event so what is happening that is when your code hits this instruction
Worksheets("Brs").Range(a1).offset(0, 24).value = Worksheets("CurrC").Cells(r1, "G").value

as the cell is being changed it is calling again this sub and again and again that is probably giving you weired results.

The way around that is to prevent this from happeing and only executing your code form start to finish

like this

Private Sub Worksheet_Change(ByVal Target As Range)

'---> Disable Events
application.enableevents = false 

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 
...
...

'---> Enable Events
Application.enableevents = True

End Sub

Open in new window


by adding Application.EnableEvents  = false
in the beg of your code

and
application.EnableEvents = True
at the end of the code you prevent recursive loop of the same sub that could give wrong results.


gowflow
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nutschCommented:
You can use

Worksheets("Brs").Cells(r1, "AE").value 

Open in new window


but you need to declare your variable as long instead of string:
Dim r1 As Long
r1 = Target.row

Open in new window


Thomas
0
 
gowflowCommented:
Good point nutsch
String is where your problem is !!!

gowflow
0
 
BostonBobAuthor Commented:
thanks guys.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now