Forum Discussion

Gisèle et Yves Pinet's avatar
Gisèle et Yves Pinet
Copper Contributor
Dec 02, 2018

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 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.

excel

2 Replies

  • Chris-RessConsulting's avatar
    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's avatar
      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?

Resources