Solved

Is there a better way?

Posted on 2014-03-17
6
232 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 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