Not pulling value from correct row 1

[alwayslrN]

I am using Excel 2002 SP3 and am very new to VBA. I have the following bit of code that changes the current cell in the range to zero if the current cell is greater than zero, otherwise it is "supposed" to be populating the current cell with the value in the same row but 5 columns back (column G). However, my current code is selecting the correct column, but is returning the value for row 2 of that column. How to get the information that I need to get to the correct (current) row?

Range("L2").Select
' Range(Selection, Selection.End(xlDown)).Select
' Application.CutCopyMode = False
' Selection.Copy

'payoffs have an end scheduled balance of 0 so default value to beginning schd balance
For Each PayoffCellRng In Range(Selection, Selection.End(xlDown))
If PayoffCellRng.Value <> 0 Then
PayoffCellRng.Value = 0
Else
PayoffCellRng.Value = ActiveCell.Offset(0, -5).Value
End If
Next PayoffCellRng

 

[SkipVought]

Hi,

ActiveCell never changes AND I would advise against using the Select or Activate methods for processing a range...

    For Each PayoffCellRng In Range([b]Range("L2"), Range("L2")[/b].End(xlDown))
       If PayoffCellRng.Value <> 0 Then
             PayoffCellRng.Value = 0
       Else
             PayoffCellRng.Value = [b]PayoffCellRng[/b].Offset(0, -5).Value
       End If
    Next PayoffCellRng

or

    For Each PayoffCellRng In Range([b]Range("L2"), Range("L2")[/b].End(xlDown))
       If PayoffCellRng.Value <> 0 Then
             PayoffCellRng.Value = 0
       Else
             PayoffCellRng.Value = [b]cells(PayoffCellRng.row, "G").value[/b]
       End If
    Next PayoffCellRng

Skip,

_[red][/red]

 

[alwayslrN]

Thanks skip - that worked.

Because I am finding I am learnig a lot more on this forum than the books (perhaps I have the wrong book) that I have, can you explain why I should not use Select or Active?

Also, I am overlooking on the board instructions how to get code inside the code box like you did. If you can tell me how to do that, in the future I will do the same to make things a little easier.

Thanks again

 

[PHV]

how to get code inside the code box
To get this:

this is my code

Type this:
[ignore]

this is my code

[/ignore]

 

[SkipVought]

I did not say to NEVER use Select or Activate methods. Generally speaking, use explicit range references to process ranges and then use the Select and/or Activate methods to position the display for the user once the procedure is complete.

Generally speaking, do not use the Selection object at the start of a procedure. Rather explicitly reference ther range you want the procedure to reference.

The only logical reason to use...

Range("L2").Select

is if that's where you want the ActiveCell to be when the procedure finishes.

How Can I Make My Code Run Faster? faq707-4105.

Skip,

_[red][/red]

 

[SkipVought]

Here's a coupla more thoughts.

The worst offending code does something like this...

do
  'some sort to logic happens here
  activecell.offset(1).select
loop until selection = ""

starts somewhere??? and proceeds row by row until there is no value. YUK!

I like to use NAMED RANGES. It helps to self document the process...

dim rPayoff as range
for each r in range("Payoff")
  'do stuff for each payoff value/row
  with rPayoff
    if .value = <> then
      .value = 0 
    else
      .value = cells(.row, "G").value
    end if
  end with
next

Skip,

_[red][/red]

 

[alwayslrN]

Thanks very much skip for your time and brief lesson, it is much appreciated.

P