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

Occasional Visitor

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 the proper row.  The targeted cell is two columns over.  How to I achieve this without too much trouble.  I have tried the Row function but have not been able to make it work in VBA.  Any suggestions would be appreciated.

2 Replies

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

@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?