combining several VBA subs

[prometheus]

Hi all

im building a sheet where i have several working VBA subs each one work on a different colums

example

Sub calc_diff()
Dim i As Integer
i = 1
Do While Cells(i, 37).Value <> ""
Cells(21 + i, 43).Value = Abs((Cells(19 + i, 41) - Cells(19 + i, 42)))
i = i + 1
Loop
End Sub

Sub calc_obs()
Dim i As Integer
i = 1
Do While Cells(i, 37).Value <> ""
Cells(21 + i, 44).Value = Abs((Cells(21 + i, 43) - Cells(21 + i, 30)))
i = i + 1
Loop
End Sub

I have about ten codes to add together in one loop , how to do this ?


the routine i need to build is like for row 21 : calculate column a , calculate column b , then jump to row 22 calculate column a , calculate column b

im unable to find the right code..

help appreciated

[Paul_Hossler]

The easy way based on the two examples would be something like this

Sub calc_all() 
     
    Dim i As Integer 
    i = 1 
     
    Do While Cells(i, 37).Value <> "" 
        Cells(21 + i, 43).Value = Abs((Cells(19 + i, 41) - Cells(19 + i, 42))) 
        Cells(21 + i, 44).Value = Abs((Cells(21 + i, 43) - Cells(21 + i, 30))) 
         
         
        i = i + 1 
    Loop 
End Sub

[prometheus]

HI Paul

looks good.. how do i nest if then code in there also ?


example how to add this code:

Sub gdh()' =IF(AI22<0;-(10^(ABS(AI22)/3)-1);10^(ABS(AI22)/3)-1)
Dim i As Integer
i = 1
If Cells(21 + i, 35) < 0 Then
Do While Cells(i, 35).Value <> ""
Cells(21 + i, 39).Value = -(10 ^ (Abs(Cells(21 + i, 35) / 3 - 1)))
i = i + 1
Loop
Else
Do While Cells(i, 35).Value <> ""
Cells(21 + i, 39).Value = (10 ^ (Abs(Cells(21 + i, 35) / 3 - 1)))
i = i + 1
Loop
End If
End Sub

[prometheus]

i came up with this code but get error, loop without do

Sub calc_all()     
Dim i As Integer
i = 1
Do While Cells(i + 21, 31).Value <> ""
If Cells(21 + i, 37) < 0 Then
Cells(21 + i, 41).Value = -(10 ^ (Abs(Cells(21 + i, 37) / 3 - 1)))
Else
Cells(21 + i, 41).Value = (10 ^ (Abs(Cells(21 + i, 37) / 3 - 1)))
End If
If Cells(21 + i, 39) < 0 Then
Cells(21 + i, 42).Value = -(10 ^ (Abs(Cells(21 + i, 39) / 3 - 1)))
Else
Cells(21 + i, 42).Value = (10 ^ (Abs(Cells(21 + i, 39) / 3 - 1)))
End If
Cells(21 + i, 43).Value = Abs((Cells(19 + i, 41) - Cells(19 + i, 42)))
Cells(21 + i, 44).Value = Abs((Cells(21 + i, 43) - Cells(21 + i, 30)))
Cells(21 + i, 45).Value = 3 * Application.WorksheetFunction.Log10(1 + (Cells(21 + i, 44)))
If Cells(21 + i, 42) < Cells(21 + i, 30) Then
Cells(21 + i, 46).Value = (Cells(21 + i, 42)) + (Cells(21 + i, 44) * 0.1)
Else
Cells(21 + i, 46).Value = (Cells(21 + i, 42)) - (Cells(21 + i, 44) * 0.1)
i = i + 1
Loop
End Sub

[prometheus]

--solved --- thanks paul

forgot the end if in above code