CODEWITHSUNDEEP
vbaexcel
jcaud3
jcaud3

Reputation: 13

Loop IF statement through every column

I downloaded survey results and put them into Excel.

I can apply this macro to "A" column.

Private Sub CommandButton1_Click()

counter = 2 'moves output

For Each n In [A7:A50]  'loops through cell in specified range

    If n < 400 Then
        Sheets("Output").Cells(counter, "B") = 0 'Output to other sheet, = points awarded
    ElseIf n > 400 Then
        Sheets("Output").Cells(counter, "B") = 3 'Output to other seet, = points awarded
    End If

    counter = counter + 1 'moves counter up 1

Next

End Sub

"B-R" all have the same conditions as "A" Column. Instead of typing this code 

Sheets("Output").Cells(counter, "B") = 3 'Output to other seet, = points awarded

over and over with just switching "A" and the output column, is it possible to loop my counter and my if statement.

My script looks at column/rows A7:A50 in "Results Worksheet", produces points in Column B of the "Output Worksheet."

I haven't been able to make it look at Column B (results worksheet) then output to Column C in (output worksheet), then look at Column C (results worksheet), then output to column D (output worksheet).

Upvotes: 0

Views: 354

Answers (2)

mkinson
mkinson

Reputation: 172

Edited answer to hopefully solve your issue. There are a few things I see.

First, I believe that you need to activate each sheet before your result goes in. Second, use .value after identifying which cell you want the output to go into. Third the Cells() function uses integers, so "B" should be 2.

Try using the below code and see if that works for you.


Private Sub CommandButton1_Click()
    Dim r as Long
    Dim c as Long
    Dim lastrow as Integer
    Dim lastcol as Integer
    Dim cpath as String

    cpath = Worksheets("SHEETNAME") 'less typing later on
    cpath2 = Worksheets("Output")
    lastrow = WorksheetFunction.CountA(Range("A:A"))
    lstcolm = WorksheetFunction.CountA(cpath.Rows(1).EntireRow)
    cpath.Cells(2,1).Activate   'assuming you have a header row


    For c = 1 to lastcol
    For r = 2 to lastrow

If cpath.cells(r,c).value < 400 Then

cpath2.Activate
    cpath2.Cells(r, 2).value = 0
ElseIf n > 400 Then

cpath2.Activate
    cpath2.Cells(r, 2).value = 3
End If

cpath.activate

next r
next c 

End Sub

Upvotes: 1

PSotor
PSotor

Reputation: 346

Maybe this will help you:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim i As Integer

Set rng = Range("A:R") ' define your range

For i = 1 To rng.Columns.Count ' loop through the columns

    counter = 2 'moves output

    For Each n In Range(Cells(7, i), Cells(50, i)) 'loops through cell in specified column i 

    If n < 400 Then
        Sheets("Output").Cells(counter, i + 1) = 0 'Output to other sheet, = points awarded
    ElseIf n > 400 Then
        Sheets("Output").Cells(counter, i + 1) = 3 'Output to other seet, = points awarded


    End If

    counter = counter + 1 'moves counter up 1

    Next
Next i

End Sub

I have added a loop that goes through each column in Range A:R and applies your macro for each of the column. I have replaced the "B" in the if statements so the results will be applied to desired column:

evaluate column "A" -> update column "B",

evaluate column "B" -> update column "C"...

Does this help?

Upvotes: 1

Related Questions