How to DIM script itself?

[thk12205]

Excel Version: MS Office Professional Plus 2013

I am writing a macro that uses the same function "Cells(firstRow + j - 1, firstColFinal)" multiple times.
firstRow and firstColFinal are dynamic values, and j is from a For loop.

Is there a way to define a part of the script as "DIM X as Script" and simply put "X" in the code instead of this longish Cell(#,A)?
Or would I need to type this out each time I need to use it?

[georgiboy]

Maybe look at using something like:

    With Cells(firstRow + j - 1, firstColFinal)        
        .Font.Bold = True
        .Interior.Color = vbRed
    End With

[thk12205]

Not quite what I'm looking for, but thank you.

To clarify, what I'm going for is for the vba script itself. Just to visually clean it up.

Currently, the Module looks something like this:

Sub Button()
         firstRow = 1
         firstColFinal = 2

         for j = 1 to 10
                  If Cells(firstRow + j - 1, firstColFinal).value < 3 & Cells(firstRow + j -1, firstColFinal) => 1 Then
                           Cells(firstRow + j - 1, firstColFinal).value = Cells(firstRow + j - 1, firstColFinal).value + 1
                  Else
                           Cells(firstRow + j - 1, firstColFinal).value = 3
                  End If
End Sub

What I'd like to go for:

Sub Button()
         firstRow = 1
         firstColFinal = 2

         Dim X as Script
         X = Cells(firstRow + j - 1, firstColFinal).value

         for j = 1 to 10
                  If X < 3 & X => 1 Then
                           X = X + 1
                  Else
                           X = 3
                  End If
End Sub

The intent would be to clean up the macro and make it easier to keep track of each idea in the module.

[georgiboy]

Sub Button()    
    firstRow = 1
    firstColFinal = 2
    
    For j = 1 To 10
        With Cells(firstRow + j - 1, firstColFinal)
            If .Value < 3 & .Value >= 1 Then
                .Value = .Value + 1
            Else
                .Value = 3
            End If
        End With
    Next j
End Sub

or

Sub Button()    
    Dim rng As Range
    firstRow = 1
    firstColFinal = 2
    
    For j = 1 To 10
        Set rng = Cells(firstRow + j - 1, firstColFinal)
        If rng.Value < 3 & rng.Value >= 1 Then
            rng.Value = rng.Value + 1
        Else
            rng.Value = 3
        End If
    Next j
End Sub

[thk12205]

Ahh I see!

Thank you georgiboy.

[georgiboy]

You are welcome.

When you put it’s not what you are looking for I thought, it is but you just don’t know it yet.

[thk12205]

Haha, true. Thanks for spelling it out.

Out of curiousity, would VBA be able to input raw code into a defined variable like I'd previously thought?

[georgiboy]

Not that I know of, if it’s a big piece of code then you could write your own functions and call them from a sub.