Event after For Loop

[markymark2345]

Hi, I know this is really basic one but cant seem to figure it out how to simply select cell A1 after the loop runs

I have a date contained in column F starting in row 3 through to row X (defined as number of entries in row A) and to get these to format correctly i need to individually select each cell and move through the loop - code below achieves this.

where i am stuck, after the loop completes i simply want to select cell A1 in the active "Transactions" sheet

Hopefully can point me in the right direction. Sorry for such a newbie one

Please Login or Register  to view this content.

[BanginMyHeadOnMyDesk]

Hi,

I'm more than likely missing the point here, but does Range("A1").Select not work to do that?

I'm confused as why you're doing what the rest of your code is for... Are you doing the F2/Enter thing because the dates are not in data format but text and you're coercing them into dates with that?

If so then there could be a quicker and easier way than looping through them.

Beth.

[markymark2345]

Hi,

I'm more than likely missing the point here, but does Range("A1").Select not work to do that?

I'm confused as why you're doing what the rest of your code is for... Are you doing the F2/Enter thing because the dates are not in data format but text and you're coercing them into dates with that?

If so then there could be a quicker and easier way than looping through them.

Beth.

Hi Beth,
Yes re the dates, are formatted as "Custom" data such as 29-Sep-2019. if i change the excel formatting to date it actually doesnt achieve the change. The only way i have figured out to do it is manually selecting each date cell and then pushing enter (hence the loop macro) and this gets them to a usable date format.

if I add Range("A1").Select after the loop it selects this and starts working down row A instead of row F

thanks a million for your help!
Mark

[BanginMyHeadOnMyDesk]

I don't understand what you mean by "it starts working down column A". Is your code above called through a sheet change event or something? Why would selecting A1 start the loop again is what I mean.

Anyway, perhaps try this approach.

Please Login or Register  to view this content.

TextToColumns has a quirky side effect that will change text dates to date format dates and saves you having to loop through one by one

Beth.

[markymark2345]

I don't understand what you mean by "it starts working down column A". Is your code above called through a sheet change event or something? Why would selecting A1 start the loop again is what I mean.

Anyway, perhaps try this approach.

Please Login or Register  to view this content.

TextToColumns has a quirky side effect that will change text dates to date format dates and saves you having to loop through one by one

Beth.

Thank you so much Beth - that works perfectly.... (and alot quicker!) thank you!!

[BanginMyHeadOnMyDesk]

Happy to help

Beth.