PDA

View Full Version : [SOLVED] combining several VBA subs



prometheus
04-21-2016, 07:06 AM
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
04-21-2016, 07:41 AM
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
04-21-2016, 07:46 AM
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
04-21-2016, 08:14 AM
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
04-21-2016, 08:23 AM
--solved --- thanks paul :)

forgot the end if in above code