Home Ask Login Register

Developers Planet

Your answer is one click away!

DanteeChaos February 2016

Cell reference based on FOR loop index

Here is some of my code:

Dim wbX As Workbook
Dim wbY As Workbook
Set wbX = Application.Workbooks.Open("C:\Converter\aaa.xls")
Set wbY = Application.Workbooks.Open("C:\Converter\bbb.xlsx")

For i = 1 To wbX.Sheets.Count
Range("Y" & i + 2).Select
ActiveSheet.Range("Y" & i + 2).Formula = "=RIGHT(("S" & i + 2); 4)"

The problem is that ("S" & i + 2) is not recognized as a cell - VBA spits out syntax errors.


katz February 2016

Maybe this example helps you:

Option Explicit

Sub test()
    Dim rngC As Range
    For Each rngC In Range("C2:C100")
        rngC.Offset(0, 4) = Right(rngC, 4)
End Sub

Paul Ogilvie February 2016

Your expression "Y" & i + 2 does not yield a valid cell reference because you concatenate a number to a string. You must convert the numeric expression to a string:

"Y" & Str(i + 2)

What I understand from your comment, the assignment should be written as:

"=LEFT(S" & Trim(Str(i + 2)) & "; 4)"    ' yields e.g.: =LEFT(S3; 4)

(The LEFT function gets the first characters from a string. This assumes the cells you reference contains strings, or that VB converts the value to a string first. And here you must use Trim(Str(i + 2)) because you are constructing a string to place as a formula in the cell.)

Post Status

Asked in February 2016
Viewed 3,825 times
Voted 9
Answered 2 times


Leave an answer

Quote of the day: live life