Solved

Is there a better way?

Posted on 2014-03-17
6
230 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
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 29

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 29

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
change VBA to show result in cells rather than debug.print 4 34
Formula 6 46
Excel 2016 Not Responding Issues 6 27
Update As Well As Add 6 37
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now