Receiving Error When Looping Through Case Select.

D

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229

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

Code: 
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:
Code: 
targetPrice = Cells(i, 186)

Any help would be appreciated...
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Code: 
targetprice = Cells(i, 186)

The i isn't assigned. you will need a line before stating its value. ex. i = 1
 
Upvote 0
hateme28 said:
Code: 
targetprice = Cells(i, 186)

The i isn't assigned. you will need a line before stating its value. ex. i = 1
Click to expand...

I applied the statement directly above the snippet giving me a problem.

i = 1

resulted in the same '1004' error.

Any other ideas?
 
Upvote 0
sorry, I just glanced at this quickly. you declared targetprice as an integer. the code
Code: 
targetprice = Cells(i, 186)

</PRE>

is trying to assign targetprice to a range. (can't do that). I don't think you need that line to execute. also you have decimals you are trying to assign to targetprice (Integer). you need to declare targetprice as a double

Code: 
dim targetprice as double

hope this helps
 
Upvote 0
hateme28 said:
sorry, I just glanced at this quickly. you declared targetprice as an integer. the code
Code: 
targetprice = Cells(i, 186)

is trying to assign targetprice to a range. (can't do that). I don't think you need that line to execute. also you have decimals you are trying to assign to targetprice (Integer). you need to declare targetprice as a double

Code: 
dim targetprice as double

hope this helps
Click to expand...

While your modifications were correct to point out (and implement I might add), I have now migrated to a new error "13" type mismatch and on the same snippet of code targetPrice = Cells(i,186).
 
Upvote 0
This is what I updated to, but still does not work:

Code: 
Sub assignTargetPrice()'



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


i=2
x = Range("D" & i)
targetPrice = Range("GH" & i)




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
 
Upvote 0
do you want the targetprice to be the value within that cell? if so, just change that line to this:

Code: 
targetprice = range("GH" & i).value
 
Upvote 0
I should have given an example much earlier in this post, my apologies.

Given that a cell in column D has a value such as one of the "case" values (i.e. 291,203,480) then I want the corresponding targetPrice value to be placed in column "GH" of the same row.

So if "D2" matched 291 the code would assign a value of 31031.28 to cell "GH2".
or if "D14" matched 192 the code would assign a value of 26666.49 to cell "GH14".
 
Upvote 0
Ok, I think I understand now. this is what you want to do:


Code: 
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, 1).Value <> ""
    x = Range("a" & i)
    Set targetprice = Range("e" & 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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,465
Messages
6,124,977
Members
449,200
Latest member
Jamil ahmed

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top