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
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
I4, which is not what I want, I just want those two singlecells filled.