how to shorten this one ??

[Jefferson2512]

Private Sub awp()
    Set wks = Sheet9
    If wks.Range("I3").Offset(0, 0).Text = "Yes" Then
    obYes.Value = True
    ElseIf wks.Range("I3").Offset(0, 0).Text = "No" Then
    obNo.Value = True
    ElseIf wks.Range("I3").Offset(0, 0).Text = "N/A" Then
    obNa.Value = True
    End If
    If wks.Range("J3").Offset(0, 0).Text = "Yes" Then
    obYes1.Value = True
    ElseIf wks.Range("J3").Offset(0, 0).Text = "No" Then
    obNo1.Value = True
    ElseIf wks.Range("J3").Offset(0, 0).Text = "N/A" Then
    obNa1.Value = True
    End If
    If wks.Range("K3").Offset(0, 0).Text = "Yes" Then
    obYes2.Value = True
    ElseIf wks.Range("K3").Offset(0, 0).Text = "No" Then
    obNo2.Value = True
    ElseIf wks.Range("K3").Offset(0, 0).Text = "N/A" Then
    obNa2.Value = True
    End If
    If wks.Range("L3").Offset(0, 0).Text = "Yes" Then
    obYes3.Value = True
    ElseIf wks.Range("L3").Offset(0, 0).Text = "No" Then
    obNo3.Value = True
    ElseIf wks.Range("L3").Offset(0, 0).Text = "N/A" Then
    obNa3.Value = True
    End If
    If wks.Range("M3").Offset(0, 0).Text = "Yes" Then
    obYes4.Value = True
    ElseIf wks.Range("M3").Offset(0, 0).Text = "No" Then
    obNo4.Value = True
    ElseIf wks.Range("M3").Offset(0, 0).Text = "N/A" Then
    obNa4.Value = True
    End If
    If wks.Range("N3").Offset(0, 0).Text = "Yes" Then
    obYes5.Value = True
    ElseIf wks.Range("N3").Offset(0, 0).Text = "No" Then
    obNo5.Value = True
    ElseIf wks.Range("N3").Offset(0, 0).Text = "N/A" Then
    obNa5.Value = True
    End If
    If wks.Range("O3").Offset(0, 0).Text = "Yes" Then
    obYes6.Value = True
    ElseIf wks.Range("O3").Offset(0, 0).Text = "No" Then
    obNo6.Value = True
    ElseIf wks.Range("O3").Offset(0, 0).Text = "N/A" Then
    obNa6.Value = True
    End If
    If wks.Range("P3").Offset(0, 0).Text = "Yes" Then
    obYes7.Value = True
    ElseIf wks.Range("P3").Offset(0, 0).Text = "No" Then
    obNo7.Value = True
    ElseIf wks.Range("P3").Offset(0, 0).Text = "N/A" Then
    obNa7.Value = True
    End If
    If wks.Range("Q3").Offset(0, 0).Text = "Yes" Then
    obYes8.Value = True
    ElseIf wks.Range("Q3").Offset(0, 0).Text = "No" Then
    obNo8.Value = True
    ElseIf wks.Range("Q3").Offset(0, 0).Text = "N/A" Then
    obNa8.Value = True
    End If
    If wks.Range("R3").Offset(0, 0).Text = "Yes" Then
    obYes9.Value = True
    ElseIf wks.Range("R3").Offset(0, 0).Text = "No" Then
    obNo9.Value = True
    ElseIf wks.Range("R3").Offset(0, 0).Text = "N/A" Then
    obNa9.Value = True
    End If
    If wks.Range("S3").Offset(0, 0).Text = "Yes" Then
    obYes10.Value = True
    ElseIf wks.Range("S3").Offset(0, 0).Text = "No" Then
    obNo10.Value = True
    ElseIf wks.Range("S3").Offset(0, 0).Text = "N/A" Then
    obNa10.Value = True
    End If
    If wks.Range("T3").Offset(0, 0).Text = "Yes" Then
    obYes11.Value = True
    ElseIf wks.Range("T3").Offset(0, 0).Text = "No" Then
    obNo11.Value = True
    ElseIf wks.Range("T3").Offset(0, 0).Text = "N/A" Then
    obNa11.Value = True
    End If
    If wks.Range("U3").Offset(0, 0).Text = "Yes" Then
    obYes12.Value = True
    ElseIf wks.Range("U3").Offset(0, 0).Text = "No" Then
    obNo12.Value = True
    ElseIf wks.Range("U3").Offset(0, 0).Text = "N/A" Then
    obNa12.Value = True
    End If
    If wks.Range("V3").Offset(0, 0).Text = "Yes" Then
    obYes13.Value = True
    ElseIf wks.Range("V3").Offset(0, 0).Text = "No" Then
    obNo13.Value = True
    ElseIf wks.Range("V3").Offset(0, 0).Text = "N/A" Then
    obNa13.Value = True
    End If
    If wks.Range("W3").Offset(0, 0).Text = "Yes" Then
    obYes14.Value = True
    ElseIf wks.Range("W3").Offset(0, 0).Text = "No" Then
    obNo14.Value = True
    ElseIf wks.Range("W3").Offset(0, 0).Text = "N/A" Then
    obNa14.Value = True
    End If
    If wks.Range("X3").Offset(0, 0).Text = "Yes" Then
    obYes15.Value = True
    ElseIf wks.Range("X3").Offset(0, 0).Text = "No" Then
    obNo15.Value = True
    ElseIf wks.Range("X3").Offset(0, 0).Text = "N/A" Then
    obNa15.Value = True
    End If
    If wks.Range("Y3").Offset(0, 0).Text = "Yes" Then
    obYes16.Value = True
    ElseIf wks.Range("Y3").Offset(0, 0).Text = "No" Then
    obNo16.Value = True
    ElseIf wks.Range("Y3").Offset(0, 0).Text = "N/A" Then
    obNa16.Value = True
    End If
    If wks.Range("Z3").Offset(0, 0).Text = "Yes" Then
    obYes17.Value = True
    ElseIf wks.Range("Z3").Offset(0, 0).Text = "No" Then
    obNo17.Value = True
    ElseIf wks.Range("Z3").Offset(0, 0).Text = "N/A" Then
    obNa17.Value = True
    End If
    If wks.Range("AA3").Offset(0, 0).Text = "Yes" Then
    obYes18.Value = True
    ElseIf wks.Range("AA3").Offset(0, 0).Text = "No" Then
    obNo18.Value = True
    ElseIf wks.Range("AA3").Offset(0, 0).Text = "N/A" Then
    obNa18.Value = True
    End If
    If wks.Range("AB3").Offset(0, 0).Text = "Yes" Then
    obYes19.Value = True
    ElseIf wks.Range("AB3").Offset(0, 0).Text = "No" Then
    obNo19.Value = True
    ElseIf wks.Range("AB3").Offset(0, 0).Text = "N/A" Then
    obNa19.Value = True
    End If
    If wks.Range("AC3").Offset(0, 0).Text = "Yes" Then
    obYes20.Value = True
    ElseIf wks.Range("AC3").Offset(0, 0).Text = "No" Then
    obNo20.Value = True
    ElseIf wks.Range("AC3").Offset(0, 0).Text = "N/A" Then
    obNa20.Value = True
    End If
    If wks.Range("AD3").Offset(0, 0).Text = "Yes" Then
    obYes21.Value = True
    ElseIf wks.Range("AD3").Offset(0, 0).Text = "No" Then
    obNo21.Value = True
    ElseIf wks.Range("AD3").Offset(0, 0).Text = "N/A" Then
    obNa21.Value = True
    End If
    If wks.Range("AE3").Offset(0, 0).Text = "Yes" Then
    obYes22.Value = True
    ElseIf wks.Range("AE3").Offset(0, 0).Text = "No" Then
    obNo22.Value = True
    ElseIf wks.Range("AE3").Offset(0, 0).Text = "N/A" Then
    obNa22.Value = True
    End If
    If wks.Range("AF3").Offset(0, 0).Text = "Yes" Then
    obYes23.Value = True
    ElseIf wks.Range("AF3").Offset(0, 0).Text = "No" Then
    obNo23.Value = True
    ElseIf wks.Range("AF3").Offset(0, 0).Text = "N/A" Then
    obNa23.Value = True
    End If


    
End Sub

 

[jasonb75]

Remove .Offset(0, 0) from each line.

With what you have given us to work with, that is about as good as it will get.

edit:-

I just noticed your other thread with an incredibly similar subject matter.

Flagged as duplicate. https://www.mrexcel.com/forum/excel-questions/1110678-each-loop.html

 

[mohadin]

Hi
may be you mean this

Private Sub awp()
    Set wks = Sheet9
    For col = 9 To 33
    wks.Cells(3, col).Select
    If wks.Cells(3, col).Offset(0, 0).Text = "Yes" Then
    obYes.Value = True
    ElseIf wks.Cells(3, col).Offset(0, 0).Text = "No" Then
    obNo.Value = True
    ElseIf wks.Cells(3, col).Offset(0, 0).Text = "N/A" Then
    obNa.Value = True
    End If
Next
End Sub

 

[RoryA]

Not only is this basically a duplicate, but it has also been cross-posted at at least one other forum.

Please take a minute to read the forum rules, and follow them in future. Thanks.

Thread closed.