Input an F2 to all cells in range in column A

[zakynthos]

I have a problem with Vlookups refreshing in a large data set.

I note that the formula refreshes only when I input an edit (F2) into the cell being looked up.

How would you write VBA to input an F2 to all cells in the range
(example A1:A10000)?

many thanks!

 

[Ruddles]

You don't really want to do that - you want to recalculate the worksheet:-

Application.Calculate

Have you got calculation set to manual?

 

[Richard Schollar]

I restrict calculation to specified ranges all the time where my datasets are very large. You can simulate an F2 by replacing = with =:

Range("A1:A1000").Replace "=", "="

 

[zakynthos]

Thanks, I agree I don't really want to input lots of F2's manually. I'd rather Excel did what it was supposed to do - calculation options are set to automatic and using an 'application calculate' macro doesn't solve the problem - just doesn't work.

It only refreshes when I enter an F2 into the lookup value in column A.

Do you know anything else I could try, or failing that - VBA to input the a load of F2's to all cells in col A in the range?

Thanks

 

[zakynthos]

Thanks for this alternative.

However, I'm using Excel 2007 and have tried this:

Range("A1:A1000").Replace "=", "="

but it doesn't work with this version as far as I can see - I've run it and there's no refresh - still only if I manually enter an F2 into the lookup value in column A.

Any other suggestions welcome.

 

[jasonb75]

Surely it would be preferable to find why the formula is not calculating and fix that instead of looking for a crude workaround.

 

[Richard Schollar]

Try passing the LookAt argument as well:

Range("A1:A1000").Replace "=", "=", LookAt:=xlPart

 

[zakynthos]

Thanks for your help again and also for the advice on checking why formula wasn't working - I did try this and also looked at why a few cells were refreshing instantly but most didn't unless F2 + enter was pressed - no luck there, so it remained a mystery to me!!!!

So if anyone can explain why Excel 2007's not doing it's thing when confronted with large data sets of vlookups I'd bereally interesed to know.

If anyone else has this problem, I've done a bit of research and adapted some code and this does solve the problem at last!

Sub Text2Number()


Dim i As Long

For i = 1 To 10000


Cells(i, 1).Select

ActiveCell.Value = ActiveCell.Value

Next i

End


End Sub

 

[jasonb75]

Sub [COLOR=red]Text2Number[/COLOR]()
 ....
End Sub

The name of your procedure shows some previously missing information, that would suggest the fix would be.

Select column A in excel then on the excel ribbon Data > Text to columns > Finish.

 

[zakynthos]

Thanks for your help!