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

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