Solved

Is there a better way?

Posted on 2014-03-17
6
233 Views
Last Modified: 2014-03-17
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
Comment
Question by:BostonBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39935321
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39935425
Instead of Cells(r1, "AE").value
Use Range("AE"&r1).value
0
 
LVL 30

Accepted Solution

by:
gowflow earned 250 total points
ID: 39935431
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 250 total points
ID: 39935432
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
 
LVL 30

Expert Comment

by:gowflow
ID: 39935442
Good point nutsch
String is where your problem is !!!

gowflow
0
 

Author Closing Comment

by:BostonBob
ID: 39935592
thanks guys.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question