Variable in VBA to shorten repeated code

[ChristineJ]

I need to use Range("F12").Offset(L, K).Value repeatedly in my VBA code, although the cell reference (F12 in this example) will differ. "L" and "K" will always be the same.

Is it possible to make all that repeats (all but the cell reference) a variable with a short name to shorten the code?

 

[mumps]

Please post your code and explain in detail what you are trying to do.

 

[Siddharth Rout]

I need to use Range("F12").Offset(L, K).Value repeatedly in my VBA code, although the cell reference (F12 in this example) will differ. "L" and "K" will always be the same.

Is it possible to make all that repeats (all but the cell reference) a variable with a short name to shorten the code?

In that case declare a range object and initialize it as the begining of the code

Dim ws As Worksheet
Dim rng As Range

'~~> Change as applicable
Set ws = Sheet1
'~~> Set your range here
Set rng = ws.Range("F12")
'
'
'
rng.Offset(L, K).Value = "Something"
'
'
'
rng.Offset(L, K).ClearContents
'
'
'
'And so on

The good thing with this method is that if you want to change the range from say F12 to A1 then you will have to do it at only one location.

 

[ChristineJ]

Thanks to you both for your replies. I am using this code to evaluate user (student) input. The code below evaluates the offset of cell E12. This is just one of many cells to be evaluated, all with different math to get to the right answer. The ".Offset(L, K).Value" appears many times in the code and I was wondering it a single character could be substituted for it to make all of this code shorter. Not essential, but nice to have. Thanks.

If Range("A12").Offset(L, K).Value + Range("B12").Offset(L, K).Value + Range("C12").Offset(L, K).Value * Range("D12").Offset(L, K).Value = Range("E12").Offset(L, K).Value Then
Range("F12").Offset(L, K).Value = "Correct"
Else
Range("F12").Offset(L, K).Value = "The amount is incorrect, so the formula will not be evaluated at this time."
End If

 

[mumps]

What do the variables L and K represent? Can you post some sample data?

 

[rlv01]

Is it always columns A thru F? If so then something like this is possible to shorten line length.

    With Range("A12")
        If .Offset(L, K).Value + .Offset(L, K + 1).Value + .Offset(L, K + 2).Value * .Offset(L, K + 3).Value = .Offset(L, K + 4).Value Then
            .Offset(L, K + 5).Value = "Correct"
        Else
            .Offset(L, K + 5).Value = "The amount is incorrect, so the formula will not be evaluated at this time."
        End If
    End With

 

[JGordon11]

you could use a custom function

Function CalcCheck(r As Range) As String
    If r(1, 1) + r(1, 2) + r(1, 3) * r(1, 4) = r(1, 5) Then
        CalcCheck = "Correct"
    Else
        CalcCheck = "The amount is incorrect, so the formula will not be evaluated at this time."
    End If
End Function

and invoke it like this

Set a = Range("A12").Offset(L, K)
a.Offset(0, 5) = CalcCheck(a)