Offset cell addresses in a variable

[ChristineJ]

I have a variable named addresses that returns cell addresses, such as J11, A1, B3 or M5, W16, C121, X55, F99
I'd like to add code to offset each of these cell addresses. If offset one row and one column, J11, A1, B3 would instead be K12, B2, C4. The number of rows and columns that it would offset would vary.

I had been using a function that one of the members provided for when the cell addresses string appeared as a cell value.

I'd like to get the same result for when that cell addresses string is stored in a variable. Thanks!

Function MCELL(r As Long, c As Long, rng As String) As String  
MCELL = Range(rng).Offset(r, c).Address(0, 0)
End Function

Offset cell addresses

Cell T31 = E9, X5, A1 I am using the following formula in cell U31 to add two column letters and one row to each cell reference in T31: =IF(T31="","",SUBSTITUTE(TEXTJOIN(", ",1,@CELL("address",OFFSET(INDIRECT(TRIM(MID(SUBSTITUTE(T31,", ",REPT("...

www.mrexcel.com

 

[Snakehips]

CJ might your cell addresses in 'addresses' contain '$" ?

 

[ChristineJ]

CJ might your cell addresses in 'addresses' contain '$" ?

No, never. I already stripped out dollar signs, sheet names, etc. with previous VBA code. I am just left with the cell references separate by commas. Thanks!

 

[Snakehips]

Does this help?

Sub CJ()

Dim addresses As String
Dim c As Integer
Dim r As Integer
Dim n As Integer
 Dim addaray As Variant
 
'variable holding your original  addresses
addresses = "J11, A1, B3"

'split into to array
addaray = Split(addresses, ",")

'Offset values
c = 1   '<<<< Column Offset
r = 1   '<<< Row offset

'convert to offset addresses
For n = 0 To UBound(addaray)
addaray(n) = Range(addaray(n)).Offset(r, c).Address(0, 0)
Next
' re-join
addresses = Join(addaray, ",")
MsgBox "Now is " & addresses
End Sub

 

[ChristineJ]

Does this help?

Sub CJ()

Dim addresses As String
Dim c As Integer
Dim r As Integer
Dim n As Integer
 Dim addaray As Variant
 
'variable holding your original  addresses
addresses = "J11, A1, B3"

'split into to array
addaray = Split(addresses, ",")

'Offset values
c = 1   '<<<< Column Offset
r = 1   '<<< Row offset

'convert to offset addresses
For n = 0 To UBound(addaray)
addaray(n) = Range(addaray(n)).Offset(r, c).Address(0, 0)
Next
' re-join
addresses = Join(addaray, ",")
MsgBox "Now is " & addresses
End Sub

Works PERFECTLY! Thanks so much - this is the last step in a project I am working on. You have helped me several times with it - I appreciate it! CJ

 

[Snakehips]

Splendid! You are most welcome.