CODEWITHSUNDEEP
excelvba
Andi Domi
Andi Domi

Reputation: 751

Convert a function from absolute to relative reference

I have a VBA function that I would like to change from absolute to relative reference.

I have three variables and I compare them through if and then do a while loop and I output the result into a cell.

I would like to do it for every cell in the column (comparing all the cells in a column, do the while loop and then output the result in every cell in another column).

I have searched Google but it only shows me how to convert an Excel formula from absolute to relative not a VBA function.

Here it is my code:

Public Sub code()

'eshte nr i zerove te hequra me nje 1 perpara ne rastin tone 1000
'value = 1000
Dim z As Long
z = Range("AS3").Value

'x eshte j3 - zerot nga mbrapa ne rastin tone 5
'value=5
Dim x As Long
x = Range("AN3").Value

'y eshte karakteri i fundit nga codi i gjeneruar eshte 4
'value=4
Dim y As Long
y = Range("AQ3").Value


'nese 5 eshte e ndryshme nga 1
If x <> 1 Then
'ateher
'nderkohe qe y(4) eshte i ndryshem nga x-1(4)
   If y = x - 1 Then
        Range("AT3").Value = 120

    Else

        While y <> (x - 1)
        'codi behet 0/1000
        Range("AT3").Value = Range("H3").Value / z
        'code = seri / z
        'dhe me pas i shtojme nje 1
        y = y + 1
        Wend

    End If

Else
    'Range("AU3").Value = Range("K3").Value
End If

End Sub

Upvotes: 0

Views: 1395

Answers (1)

gtwebb
gtwebb

Reputation: 3011

Since it looks like you will eventually want to change multiple cells (based on your commented out line at the end) I don't think a user defined function will fit your need.

I would add a variable 

dim rowNum as long

and then you could use this for multiple rows like this

rowNum=3
y = Range("AQ" & rowNum).Value

otherwise you can use offset functions

range("A3").offset(1,0).value 'this offsets by one column so takes the value from cell "B3"

Upvotes: 1

Related Questions