VBA - changing the value of the Active cell

dave_m

New Member
Joined
Jan 7, 2005
Messages
10
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
 

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
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
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top