Find row number from value in combobox by searching sheet

[sabrinamay16]

I need to find the row number on a sheet by searching for a value from the combo box. I have created a calender that lists dates that are free in the combo box and then details need to put inputted in the same row. I've written this code and used a messagebox to test that the row is correct, but it just shows 0. This is the code i've written, not sure if im on the right lines..

Please Login or Register  to view this content.

[Zealotwraith]

I don't see value being assigned for the value r,

so when i = r it doesn't get a value,

if you meant to do r = i, it has to be done in the for loop because after the for loop the value of i disappears.

[Palmetto]

Hello sabrinamay16,

Welcome to the forum.

When reading the forum rules perhaps you overlooked the one requiring code tags?
All VBA code must be wrapped in code tags.

Please edit your post to add them. Simply highlight all the lines of VBA code then press the button with the # symbol on it. Thanks.

[sabrinamay16]

Please Login or Register  to view this content.

I've switched it round to this and it returns the value 0 still

[Palmetto]

I would opt for using the FIND METHOD rather than looping.
In the example code in the above link you would only need to use

Please Login or Register  to view this content.

[sabrinamay16]

I've had a look at the link and have changed my code to just this:

Please Login or Register  to view this content.

It returning and error saying 'run time error 91. object variable or with block variable not set.
If i dont pick a value in the combox and leave it blank, it returns the value 2 in the message box..

[HSV]

The following works fine with me Sabrina.

Please Login or Register  to view this content.

[Norie]

Is the combobox populated from a contiguous range?

If it is then you could use the ListIndex to find the row.

If it's not another way to find the row might be to use Application.Match.

Please Login or Register  to view this content.

[sabrinamay16]

The dates shown in the combobox are the only dates with blank cells to the right of them from the calendar sheet. i need code that finds the row that the date selected is in to then add data to the columns next to it

I've tried that code but it returns an error 'subscript out of range'

[sabrinamay16]

I've changed it to this and i think it will work, but atm i tested that it returns the value from the combo box using msgbox and it returns blank each time! any clue why?? this is the code:

Please Login or Register  to view this content.

[Norie]

That's because I spelt 'Calendar' kind of right.

[sabrinamay16]

haha I didn't notice it either! I've managed to get it to return the value of the combobox to search it and the code doesnt crash, but it doesnt show a messagebox or anything now

Please Login or Register  to view this content.

ive tried 'msgbox r' to see what it returns, but it crashes

[Norie]

VBA/Excel can be funny when dealing with dates.

Why not attach a workbook?

[sabrinamay16]

should say if in the code not ' f

[sabrinamay16]

can i not change it to search for the date as a string? could make it easier

what do you mean by a workbook?

[sabrinamay16]

ooo wait can you attach your workbook here? i'm new to this..

[Norie]

You do have an Excel workbook/file where you are using this code, I hope?

I doubt using a string would work, unless the dates on the worksheet are strings.