do until loop with if statement that applies on different columns (vba)

[ddesantis]

Hi,
I'm scratching my head around what I think "a very simple piece" of code...but I'm a VBA beginner
I have to perform 2 tasks, and while I've completed the first one, the other is giving me a lot of troubles...

1st step: clear the content of a range of cells when I switch the xls validation list (yes,no). This works

Private Sub WorkSheet_Change(ByVal Target As Range)

If Target.Address = "$E$170" Then Range("$E$178:$E$187").ClearContents

If Target.Address = "$E$174" Then Range("$E$178:$E$187").ClearContents

2nd step:
- I have 2 ranges of value $D$178:$D$187 and $E$178:$E$187
- the range $D$178:$D$187 can assume the following values: Yes, No, Please select
- If any value in the range $D$178:$D$187 assume value "Yes" the corresponding value in the range $E$178:$E$187 has to assume value "Yes"
- If any value in the range $D$178:$D$187 assume value "No" the corresponding value in the range $E$178:$E$187 has to assume value "No"
- If any value in the range $D$178:$D$187 assume value "Please select" the corresponding value in the range $E$178:$E$187 will result in a validation list "Yes" or "No"

It's worth saying that the range $E$178:$E$187 has already a xls data validation list set (Yes,No)

Example
D180="Yes" then E180="Yes"

D183="No" then E183="No"

D185="Please select" then E185= validation list "Yes" or "No"

I tried as follows but xls crashed

Set Rng = Range("$E$178:$E$187")
Range("$D$178").Select
Do Until Selection.Value = "$D$188"
If Selection.Value = "Yes" Then
Rng = "Yes"
Else
Rng = "No"
End If
Loop

End Sub

my assumption is that in all other cases in the range $S$178:$E$187 (i.e. "Please select") the code doesn't apply and I can select "Yes" or "No" in the xls data validation list $E$178:$E$187

I managed to get the code above start working but I always got the following error
run time error '2147417848 (80010108)':
Method 'Value' of object 'Range' failed

even with
Select Case Range("$D$178").Text
Case "Yes"
Range("$E$178").Value = "Yes"
End Select
End Sub


Hope it's clear, I would appreciate any help you can provide!

 

[Fluff]

Hiya
Does this do the job?

Sub ddesantis()

    Dim i As Integer

    For i = 178 To 187
        If Range("D" & i).Value <> "Please select" Then
            Range("E" & i).Value = Range("D" & i).Value
        End If
    Next i
        

End Sub

 

[ddesantis]

hi Fluff,
thanks!! it works!

 

[Fluff]

Thanks for the feedback