• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rang & .Column

hostile

New Member
Hi, Im pretty new to this but have been successful getting Range(“A1” & .Row).Value to copy and accumulate to say cell B1. Based on a Target,Range value, using a Worksheet Change event.

However, I cant seem to get Column to work in the same logical way.

Im trying to get any value that shows up in say Column A1:A10 to Reproduce in the adjacent cell of the value shown, and accumulate.

So I tried something like
Range(“B” & .Column).Value = Range(“A” & .Column).Value + Range(“B” & .Column).Value and it didnt really work.

I say really because it did sort of work. It placed a value in an adjacent cell in column B but not next the cell thar got its value in Column A. In fact it for some reason chose row 6.

I have a list of values that is 37800 lines so mapping the values returned, to each adjacent cell in the column, I dont think would work either. It would probably stack out. Any help would be great.
__________________________________________________________________
Mod edit : post moved to appropriate forum …
 
Hi !

Using Range like this is a no sense !

As a start, must read VBA inner help of Cells and Offset properties …
 
Hi Marc,

Thanks for the response. Im not sure what you mean or how it helps.

Here is the code.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("F3")) Is Nothing Then
  With Target
  If IsNumeric(.Value) Then
  If .Value > 0 Then
  Range("V" & .Row).Value = Range("V" & .Row).Value + .Value
  Range("U" & .Row).Value = Range("U" & .Row).Value + Range("Q" & .Row).Value
  Range("T" & .Row).Value = Range("T" & .Row).Value + Range("P" & .Row).Value - Range("M" & .Row).Value * .Value - Range("O" & .Row).Value
 
  If Range("Q2") = "OH" Then
  Range("X3").Value = Range("Q" & .Row).Value + Range("X3").Value
  End If
 
  If Range("Q2") = "TX" Then
  Range("Y3").Value = Range("Q" & .Row).Value + Range("Y3").Value
  End If
 
  Range("AB" & .Column).Value = Range("AA" & .Column).Value + Range("AB" & .Column).Value
 
Else
  Application.EnableEvents = True
End If
End If
End With
End If
End Sub

Everything works but the last part.

If I select 99556 from a data validation list, it updates the cell next to 99556 in column AA, and column AB populates with the value in AA like it should, on row 6.

If I select 99554 from the data validation list, which is the first option in column AA on row 5, the value shows up in AA but does not record in AB.

If I select 99558, which is the 3rd value in column Z, the value shows up in column AA on row7, but again does not populate in the adjacent cell of AB.


Why it only populates on row 6 makes no sense to me. If you can help great. If not, anyone else have any ideas?
 
It seems you don't ever read VBA help 'cause you should have already fix it !
Yes, the no sense is the way you try to use it …

From this : Range("AB" & .Column)
What "AB" refers to ?
 
Are you looking for an argument? I'm just looking for guidance. If you want to play word games, I'm sure someone over at Ebay would enjoy it. I don't see the value in your non-responsive opinion. However, I am leaning towards your tag line quote as being a direct indication of your sense of inferiority. I dont really care what you think of yourself, I am only interested in finishing this part of my project. To answer your question, AB refers to AA as indicated. AA is populated according to a formula that matches the value selected in the data validation list that works much like an index match statement. You get it? Maybe you didnt notice the "Im new at this" comment at the beginning of the thread.. So I will give you the benefit of the doubt Mr. Excel Ninja.
 
If you had read VBA help as advised, you already understood and fixed ! :confused:

So without reading, I try to explain what is at very beginner level ! :rolleyes:

« AB refers to AA » WOWNO ‼‼ AB refers to column AB and nothing else …

From Range("AB" & .Column) for example if .Column value is 20,
so it refers to AB20 meaning column AB and row 20 ‼ No sense, catch it ?!

So to play with row and column, use Cells and Offset properties
as well explained in VBA inner help ! Burning a couple of neurones … :DD

_____________________________________________________________
Two things are infinite : the Universe and human stupidity;
and I'm not sure about the Universe ! Albert Einstein
 
Hi ,

Chandoo has set up this forum to help those who are looking for help in all matters Excel.

Please try to upload your file once more , and if you still have problems , send it to me at :

narayank1026 [at] gmail [dot] com

and I will upload it to this thread

Narayan
 
In simple terms, let's assume you change just cell F3.

Your code checks to see if your changed cell(s) includes F3:
Code:
If Not Intersect(Target, Range("F3")) Is Nothing Then

In this example, F3 is the cell being changed, so your code then looks at the column number of the changed cells (using Target.Column). Column F is the 6th column, so
Code:
Range("AB" & .Column)
is equivalent to
Code:
Range("AB6")
which is why you see the behaviour you describe.

I confess I don't see why you switched to using .Column from .Row - can you elaborate?
 
Back
Top