Receiving Error When Looping Through Case Select.

[drmingle]

Objective: reference 3-digit number in column "D" on a row-by-row basis and assign a target price value based on condition

Sub assignTargetPrice()'


Dim x As Integer
Dim targetPrice As Integer
Dim i As Integer




targetPrice = Cells(i, 186)


x = Range("D2:D500").Select


Do While Cells(i, 186).Value <> ""


Select Case x ''
Case 291
    targetPrice = 31031.28
Case 292
    targetPrice = 28775.79
Case 293
    targetPrice = 25939.4
Case 190
    targetPrice = 29253.81
Case 191
    targetPrice = 28509.11
Case 192
    targetPrice = 26666.49
Case 202
    targetPrice = 26326.24
Case 203
    targetPrice = 22807.46
Case 480
    targetPrice = 268106.64
Case 481
    targetPrice = 25614.51
Case 482
    targetPrice = 22548.06
Case 469
    targetPrice = 21262.22
Case 470
    targetPrice = 17289.14
Case 186
    targetPrice = 34019.91
Case 187
    targetPrice = 35513.02
Case 188
    targetPrice = 32426.66
Case 189
    targetPrice = 37470.33
Case 204
    targetPrice = 37187.39
Case 205
    targetPrice = 36755.98
Case 206
    targetPrice = 32637.69
Case 207
    targetPrice = 52168.85
Case 208
    targetPrice = 44757.54
Case 870
    targetPrice = 58388.84
Case 871
    targetPrice = 41668.25
Case 872
    targetPrice = 34510.54
Case 177
    targetPrice = 31648.51
Case 178
    targetPrice = 29107.64
Case 179
    targetPrice = 25580.05
Case 193
    targetPrice = 29663.99
Case 194
    targetPrice = 27137.66
Case 195
    targetPrice = 25320.21


End Select
i = i + 1
Loop


End Sub

I am receiving a '1004' error on this line:

targetPrice = Cells(i, 186)

Any help would be appreciated...

 

[hateme28]

where do you get the error, it worked for me. could you provide an example of your data?

 

[Michael M]

Shouldn't this

Set targetprice = Range("GH" & i)

Set targetprice = Range("GD" & i)

 

[hateme28]

My apologies, my last post contained errors. this should be correct. I tested it and it looks at column D and outputs data to column GH.

Sub assignTargetPrice()
Dim x As Long, targetprice As Range, i As Integer
i = 1
x = Range("D" & i)
Set targetprice = Range("GH" & i)
Do While cells(i, 4).Value <> ""
    x = Range("D" & i)
    Set targetprice = Range("GH" & i)
    Select Case x
    Case 291
        targetprice = 31031.28
    Case 292
        targetprice = 28775.79
    Case 293
        targetprice = 25939.4
    Case 190
        targetprice = 29253.81
    Case 191
        targetprice = 28509.11
    Case 192
        targetprice = 26666.49
    Case 202
        targetprice = 26326.24
    Case 203
        targetprice = 22807.46
    Case 480
        targetprice = 268106.64
    Case 481
        targetprice = 25614.51
    Case 482
        targetprice = 22548.06
    Case 469
        targetprice = 21262.22
    Case 470
        targetprice = 17289.14
    Case 186
        targetprice = 34019.91
    Case 187
        targetprice = 35513.02
    Case 188
        targetprice = 32426.66
    Case 189
        targetprice = 37470.33
    Case 204
        targetprice = 37187.39
    Case 205
        targetprice = 36755.98
    Case 206
        targetprice = 32637.69
    Case 207
        targetprice = 52168.85
    Case 208
        targetprice = 44757.54
    Case 870
        targetprice = 58388.84
    Case 871
        targetprice = 41668.25
    Case 872
        targetprice = 34510.54
    Case 177
        targetprice = 31648.51
    Case 178
        targetprice = 29107.64
    Case 179
        targetprice = 25580.05
    Case 193
        targetprice = 29663.99
    Case 194
        targetprice = 27137.66
    Case 195
        targetprice = 25320.21
    End Select
    i = i + 1
Loop
End Sub