VBA loop through one column then pass next

[mamilinko]

Hi Everybody,

I have created a code that goes through all rows of a column and apply some conditions and create values in other columns, vode works fine but i want code to loop through other columns, see my code below here i loop through column C but after column C is done i want to apply same loop to column D and so on.... So everything will stay same just column c needs to change each time, is there anyway to do this? Any help or clue will be appreciated much. Many thanks,

 sub trial ()
dim ep as integer
dim tp as integer
dim i as integer
dim j as integer
dim strt as integer
dim endt as integer
dim gval as integer

ep = 10
tp =10
worksheets("sheet1" ).select

for i =  ep to ep+tp+4
strt = i-10
endt = strt +9
for j = strt to endt
if range("C" & i).value < 50 then 
range("F" & j).value = range("E" & j).value *2
else: range("F" & j).value = range("E" & j).value

end if
gval = application.worksheetfunction.sum(range(cells(strt,6),cells(endt,6)))
range("G" & i).value = gval
next j
range("F5:F600"). clear
next i
end sub
[\code]

 

[James006]

Hi,

You basically need to have another loop ... say with variable k ... and have your cells modified to look like Cells(i,k) ...
Difficult to guess exactly what the macro does ... since you are already modifying the Columns E,F, & G when dealing with Column C ...

Sub trial()
Dim ep As Integer
Dim tp As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim strt As Integer
Dim endt As Integer
Dim gval As Integer


ep = 10
tp = 10
Worksheets("sheet1").Select


For k = 3 To 10 ' for Column C to J
  For i = ep To ep + tp + 4
    strt = i - 10
    endt = strt + 9
      For j = strt To endt
        If Range("C" & i).Value < 50 Then
          Range("F" & j).Value = Range("E" & j).Value * 2
        Else
          Range("F" & j).Value = Range("E" & j).Value
        End If
        gval = Application.WorksheetFunction.Sum(Range(Cells(strt, 6), Cells(endt, 6)))
        Range("G" & i).Value = gval
      Next j
    Range("F5:F600").Clear
  Next i
Next k
  
End Sub

HTH

 

[mamilinko]

Hi James,

Many thanks for your help, indeed now i see it is simple but couldnt think of it in a hurry for a while, well indeed this is not real code that use i prepared this one for forum to explain it eaisly so it wasnt designed good. So i refined the code with your help and it works fine, thank you very much. I share the code below in case sb benefit.

 Sub trial()

Dim ep As Integer
Dim tp As Integer
Dim i As Integer
Dim j As Integer
Dim strt As Integer
Dim endt As Integer
Dim gval As Integer
Dim k As Integer
Dim kkk As Integer

ep = 10
tp = 10

Worksheets("sheet1").Select

For k = 3 To 6
kkk = k + 9


For i = ep + 5 To ep + tp + 4

strt = i - 10
endt = strt + 9



For j = strt To endt
If Cells(i, k).Value < 50 Then

Range("H" & j).Value = Range("J" & j).Value * 2
Else: Range("H" & j).Value = Range("J" & j).Value
End If

gval = Application.WorksheetFunction.Sum(Range(Cells(strt, 8), Cells(endt, 8)))

Cells(i, kkk).Value = gval
Next j
Range("H5:H500").ClearContents

Next i
Next k

End Sub

 

[James006]

Glad you could fix your problem ...

Thanks for sharing your solution for the benefit of future readers ...