VBA - changing the value of the Active cell

[dave_m]

Hi,

I've had a look around the previous posts and couldn't find an answer to this problem, so was hoping someone could help?
I've got three columns of numbers, A, B and C, and i am wanting to change the values of the numbers in columns A and C, if certain criteria are met in column A. I'm not interested in the original values of A and C. I'm pretty new to VBA, and the code i've written is below. If anyone can help i'd be most gratefull.

----------------------------
Cells(1, 1).Select
For i = 2 To 65536
If Cells(i, 1) >= RiffLoc Then
ActiveCell.Value = (ActiveCell.Value + RiffLength)
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = (ActiveCell.Value - RiffHeight)
ActiveCell.Offset(0, -2).Select
End If
Next i
-----------------------------------------------------

Hope this post contains makes sense and contans enough info..

Thanks in advance,

Dave

 

[lozzablake]

Hi, you do not need to select or use activecell. check the values directly:

For i = 2 To 65536
If Cells(i, 1) >= RiffLoc Then
cells(i,1)= cells(i,1) + RiffLength
cells(i,3)=cells(i,3)-RiffHeight

End If
Next i

 

[dave_m]

Thats worked a treat.

Cheers

Dave

 

[milesUK]

Dave, Your code first selects cell A1 making it Active. All the other ActiveCell lines of your code then refer to cells A1 or A3 only

Cells(1, 1).Select 'SELECTS A1
For i = 2 To 65536 
  If Cells(i, 1) >= RiffLoc Then 
    ActiveCell.Value = (ActiveCell.Value + RiffLength) 'REFERS TO A1!
    ActiveCell.Offset(0, 2).Select 'SELECTS A3
    ActiveCell.Value = (ActiveCell.Value - RiffHeight) 'REFERS TO A3!
    ActiveCell.Offset(0, -2).Select ' SELECTS A1 !!!
  End If 
Next i

This is my interpreataion of what you may be after

For i = 2 To 65536 
  If Cells(i, 1) >= RiffLoc Then 
    Cells(i, 1).Value = (Cells(i, 1).Value + RiffLength)
    Cells(i, 3).Value = (Cells(i, 1).Value - RiffHeight)
  End If 
Next i