For....Each.....not checking through range

Zakkala

Active Member
Joined
Nov 12, 2004
Messages
254
Hi there,

I have the following code, which is not working. All it does is step the cursor down 1 cell from where it starts on the worksheet, rather than sorting through the defined range and then selecting the cell I want. I'm not sure why as I've used almost exactly the same code successfully on a different worksheet in the same workbook:

Code:
Dim mycheckrng as Range

myupdatedate = "04/06/2008"

Set mycheckrng = Range("D5:AR5")

For Each c In mycheckrng

    If c.Value = myupdatedate Then

        c.Select

    End If

Next c

mycsdcdateref = ActiveCell

ActiveCell.Offset(1, 0).Select

Any suggestions greatfully received.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
When this code runs it will go through all the cells but when it finishes it will only have selected the last cell it found. You won't be able to see what it selected before.

Instead of "c.select" try "c.font.bold=true" and you should see that the cells you need will have been changed.
 
Upvote 0
Hi Goeff,

I've tried that, but unfortunately nothing turns blue!

I have used the code below to do the same on another worksheet and it's sorted through the range there without a problem, ending up where I need it.

Really don't know what I've done!

Cheers,

Mel.
 
Upvote 0
Could it be something to do with date formats, dates posted as text, or something similar that would cause a mismatch?
 
Upvote 0
I think it does have to do with the date formats...

myupdatedate = "04/06/2008"

that is just a text string, not a date...

try
Dim myupdatedate as Date
myupdatedate = DateValue("04/06/2008")


Also, what exactly do you want the code to do? Surely there's an easier way without selecting and activating cells...
 
Upvote 0
Thanks for the suggestion, but unfortunately it's not the date format - all checked and double checked.

The code is looking through a row of cells, each of which contains a date. I want it to find the specified date (myupdatedate) and then step into the blank cell below in prepartion to paste some data. I've managed it on another worksheet, I just don't know what this one is playing up - if there is an easier way, I'd be happy to try it!
 
Upvote 0
Thanks for the suggestion, but unfortunately it's not the date format - all checked and double checked.

I think you've misunderstood what I'm saying...

myupdatedate = "04/06/2008"

By using that, your variable myupdatedate is NOT an actual date. It's just a text string. So if your dates in the range are actual valid excel dates, NONE of them will match myupdatedate

Try it and see.
Pick a cell in your range that you know IS 4/6/2008. Say it's F5 for example
PUt this code inside your code.

myupdatedate = "04/06/2008"
MsgBox Range("F5").Value = myupdatedate

Does that give you TRUE or FALSE ?
 
Upvote 0
Hiya,

No, I haven't misunderstood - I used your original suggestion of:

Dim myupdatedate as Date
myupdatedate = DateValue("04/06/2008")

to check it as suggested and it made no difference.

I have tried changing text to dates, dates to text and pretty much every way I can think of.
 
Upvote 0
Zakkala

As Geoff pointed out in his first post this code doesn't really do anything.

Sure it loops through all the cells but then it doesn't actually do anything within the loop.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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