How to select a cell relative to the active cell in VBA

I am writing a VBA program to add numbers to a cell on a separate sheet. The targeted cell will change every time I use the program. However, one way to identify the cell is to use Find to get to t...

excel

Chris-RessConsulting

Brass Contributor

Dec 03, 2018

Hello Gisèle et Yves Pinet,

You can use the "offset" property to change which cell you want to select based on where your active cell is.

For example, say your active cell is in A13 and you want to move it over 2 columns; all you need to write is:

Activecell.Offset(0,2).select

This will offset the active cell down 0 rows and to the right 2 columns. If you ever want to go the other way just put (-)negative signs in front of the numbers. Hope this helps!

Offset(#ofRows,#ofColumns)

Positive numbers:

Rows = down

Columns = to the right

Negative numbers:

Rows = up

Columns = to the left

Firefly8

Copper Contributor

Nov 16, 2022

Chris-RessConsulting :

I have this VB code:

Sub ExportQRCode()
Dim xHttp: Set xHttp = CreateObject("Microsoft.XMLHTTP")
Dim bStrm: Set bStrm = CreateObject("Adodb.Stream")
Dim Size: Size = 250 'dalam Pixels
Dim QR, Name, val, LeftCell
Dim Invalid: Invalid = "\/:*?" & """" & "<>|"
For Each val In Selection
Name = val.Value
LeftCell = ActiveCell.Offset(0, -1).Select
For intChar = 1 To Len(Name)
If InStr(Invalid, LCase(Mid(Name, intChar, 1))) > 0 Then
MsgBox "The file: " & vbCrLf & """" & Name & """" & vbCrLf & vbCrLf & " is invalid!"
Exit Sub
End If
Next
QR = "http://chart.googleapis.com/chart?chs=" & Size & "x" & Size & "&cht=qr&chl=" & Name
xHttp.Open "GET", QR, False
xHttp.Send
With bStrm
.Type = 1 '//binary
.Open
.write xHttp.responseBody
.savetofile ThisWorkbook.Path & Application.PathSeparator & LeftCell & ".png", 2 '//overwrite
.Close
End With
Next
End Sub

Where I save selected cells (column B: Unique Code, from table: Products) as a QR Code file with the name from that selection (eg. 63465353.png). I want the name of the file to be taken from column A where I have Product name (eg. Polo Shirt XS.png). Like you said, the left cell from ActiveCell is: ActiveCell.Offset(0, -1).Select, but for me is not working. Can you help please?

Forum Discussion

How to select a cell relative to the active cell in VBA

Resources