Solved

Is there a better way?

Posted on 2014-03-17
6
235 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 31

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 31

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Part One of the two-part Q&A series with MalwareTech.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

636 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