0

How do I select the value from a cell 25 columns to the left of my active cell? Same row.

Range("AF1").Select
Dim i As Integer
Dim j As Integer
For i = 26 To 2 Step -1
    value = Range(rc[-i]).Value

Is my current train of thought. I want to run through every cell until I'm 2 away.

Lobo de Colorado
  • 197
  • 2
  • 3
  • 11
  • It's been a while since I worked with VBA, but I believe "AF1" has an integer equivalent that you could do simple math on, e.g. X-25. However, I'm not sure I follow your question based on your example solution. Do you want to select the whole range or just the individual value? – Jacob Barnes Oct 23 '17 at 23:23
  • @JacobBarnes just the individual value. – Lobo de Colorado Oct 23 '17 at 23:24
  • 1
    `Range("AF1").OffSet(0, -25).Value` will be the value in the cell 25 columns to the left of cell AF1 or, because you have done a `Select` (bad idea doing that) you could use `Selection.Offset(0, -25).Value`. To use `-i`, you would adjust the offset to be `-i` instead of `-25` – YowE3K Oct 23 '17 at 23:24
  • @YowE3K So if I stick not selecting, but simple posting the range within the for-loop, I should be okay? – Lobo de Colorado Oct 23 '17 at 23:27
  • Yes, but it depends on what else you are going to do with the code - e.g. if the `1` in `AF1` isn't fixed, but is also going to be used in a loop, you might consider using `Cells(rowno, "AF").Offset(0, -i).Value`, or maybe just use `Cells(rowno, 32 - i).Value`, etc (where `rowno` is a variable). There's a lot of ways to do it, but `Select` and `Activate` are rarely a good thing to use - see [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/6535336) – YowE3K Oct 23 '17 at 23:31

1 Answers1

3

you would use the offset property. i.e.

Sub getOffset()
  Range("AF1").Select
  ActiveCell.Offset(rowOffset:=0, columnOffset:=-25).Activate
End Sub
R Hamilton
  • 263
  • 2
  • 14