(Solved) – Add/Subtract long variable from column reference


I saw a post here earlier which reminded me of a (very basic) problem I have run into every once in a while. I have no idea what a good title would be, I’m pretty sure mine isn’t it.

Anyway, let’s say I get a column as an integer (let’s say it’s 4), save it in a variable and then want to write “Test” in the column which is 4 columns to the left and to the right of E1 (meaning A1 and I1). I can do it in two lines of code, either with Offset or Cells. But how can I do this in one line?

Sub Test()

    Dim ws As Worksheet
    Dim colnumber As Long

    Set ws = ThisWorkbook.Sheets(1)
    colnumber = 4

    ws.Range("E1").Offset(0, colnumber).Value = "Test"
    ws.Range("E1").Offset(0, -colnumber).Value = "Test" 'works

    ws.Cells(2, 5   colnumber).Value = "Test2"
    ws.Cells(2, 5 - colnumber).Value = "Test2" 'works

    ws.Range("A3,I3").Value = "Test3" 'works

    ws.Range(ws.Cells(4, 5 - colnumber), ws.Cells(4, 5   colnumber)).Value = "Test4" 'fills up Range("A4:I4")
End Sub

The ‘ws.Range(“A3,I3”).Value’ is just there for me to show it is possible to write in two non-contiguous cells the same thing in one line of code. What I struggle with is to do this when using Cells or something similar (which I think I have to because I want to add/subtract the variable colnumber) and in the last line of code it of course fills up the whole range from A4 to I4, which is not what I want, I just want those two singlecells filled.

Leave a Reply

Your email address will not be published. Required fields are marked *