Whats wrong with this for searching a range in a worksheet?

Hi

Im not the best on coding, and have done this before, but cant seem to get it to work!! Usually i just use simple codes that allow lists to generate based on other values, but now i need to search for a specific row in a sheet to add a value to that row in a specific column.

My code is...

lastrow = Worksheets("Data2").Range("D1").End(xlDown).Row

Should it be

worksheet("Data2")

or

Range.("D1")

Could someone let me know whats wrong or missing please, ive tried a few different codes but keep getting the run-time error '9' message

Thank you Dan

If you have error 9 then your sheet name is not "Data2". Perhaps "Data 2" with space?

Nope its definatley Data2

Capital D no space

lastrow = Worksheets("Data2").Range("D1").End(xlDown).Row
For I = 1 To lastrow
If UserForm1.ComboBox4.Text = Range("D" & I).Value Then
ask = I
Exit For
End If
Next I
Range("P" & ask).Value = Range("P" & ask).Value + UserForm1.TextBox65.Value

this is the full code

Yeah one, i didnt know that could make a difference, il try it now....

May be you need:

lastrow = ActiveWorkbook.Worksheets("Data2").Range("D1").End(xlDown).Row

or ThisWorkbook if the code is in the same workbook.

Ok i will change it to that, thank you, it does work when only that workbook is open and no more runtime error 9, but..... now on this line

Range("P" & ask).Value = Range("P" & ask).Value + UserForm1.TextBox65.Value

i have error 1004 and range global, should the "P" be changed to "16" usually when im transferring data from a userform i will have to change cell D6 to (6, 4) so would p need changing to 16

You must check if ask is number greater than 0:

If ask > 0 then Range("P" & ask).Value = Range("P" & ask).Value + UserForm1.TextBox65.Value

When you call Range property you must use the column letter. When you call Cells you may use column letter or number equally.

Ok well the errors are gone now, but the value of TextBox65 is not being added into the Data2 sheet, is there an easier method to search the row for the value in Combobox4 and add the value from Textbox65 into the corresponding cell in column P?

Say D10 = 01/12/2013 and my combobox4 value = 01/12/2013 then in my textbox65 is $4000 i want $4000 in P10?.....

Ah ok..... il give that a try...

Yes, its looking at a named range in that data2 tab

Weekcommencing

its just the range

Formula:

=Data2!$D$2:$D$58

then named "WEEKCOMMENCING" in the name box

Bang on!!! I had to do one little thing because i wasnt removing the old value to change to the new rate

idx = Combobox4.ListIndex

If idx = -1 Then Exit Sub

ask = idx + 2

With ThisWorkbook.Worksheets("Data2")
      .Range("P" & ask).Value = Val(UserForm1.TextBox65.Value)
End With

Thanks yet again Norie

Basically i copied this code

idx = Combobox4.ListIndex

If idx = -1 Then Exit Sub

ask = idx + 2

With ThisWorkbook.Worksheets("Data2")
      .Range("P" & ask).Value = Val(UserForm1.TextBox65.Value)
End With

and changed it to

idx1 = Combobox5.ListIndex

If idx1 = -1 Then Exit Sub

ask = idx1 + 2

With ThisWorkbook.Worksheets("Data2")
      .Range("P" & ask).Value = Val(UserForm1.TextBox66.Value)
End With

As it wouldnt let me duplicate idx, but doesnt seem to work...

Hi Norie

Im wondering if you would be able to give me a point in the right direction? furthering on from the above, if i have two ranges in one column, and i only want what is in the text box to go to those specific cells in column P relating to the range, is it possible to do this?

Say Range 1 = A1 to A20 and Range 2 = A21 to A40, Textbox1 relates to Range1 and Textbox 2 relates to Range2, If i enter something in Textbox2 i cant suss how to get it into P21 to P40 depending on the combox value.

Is this possible?

Dan