Very Simple Looping Question

alnr

New Member
Joined
Mar 25, 2009
Messages
3
Hi-
I obviously don't know any VBA, but need to know how to do this simple task. I need to select a cell in a row, activate it (F2 for example), hit enter, move down to the next cell, and do the same thing down to a specific row number.
Any help would be greatly appreciated.
Thanks
Al
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi-
I obviously don't know any VBA, but need to know how to do this simple task. I need to select a cell in a row, activate it (F2 for example), hit enter, move down to the next cell, and do the same thing down to a specific row number.
Any help would be greatly appreciated.
Thanks
Al

What is the purpose for this?
 
Upvote 0
Hello and welcome to MrExcel.

Why do you need to do this - what is it meant to achieve?
 
Upvote 0
Thank you.
Well, it's the only solution I could come up for the problem I have. I'll try my best to explain. I receive a data file from an external source on occasion with 1 field (column A) that I copy/paste into my excel file. I use this field to perform a VLOOKUP on. However, the formula will not calculate on this field I'm guessing because of the cell formats. I have tried every which way to change the formats (before copy/paste, after copy paste, etc) to no avail. However, when I select the cell and press enter the VLOOKUP will calculate. Yes, auto calc is on. I figured if I could paste a little code in the file I can quickly work around this as there may be several thousand rows of data. Hopefully that made sense.
Thanks
Al
 
Upvote 0
Hello guys,

I remember I was once trying to find a way to re-calculate every cell's data too.. without having to select each individual cell, hit F2, and then press Enter.

I will give an example that might help clarify when you would need to do this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>46+67</TD><TD>=</TD><TD> </TD><TD>=46+67</TD><TD> </TD><TD>=46+67</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>46+68</TD><TD>=</TD><TD> </TD><TD>=46+68</TD><TD> </TD><TD>=46+68</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>46+69</TD><TD>=</TD><TD> </TD><TD>=46+69</TD><TD> </TD><TD>=46+69</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>46+70</TD><TD>=</TD><TD> </TD><TD>=46+70</TD><TD> </TD><TD>=46+70</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>46+71</TD><TD>=</TD><TD> </TD><TD>=46+71</TD><TD> </TD><TD>=46+71</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>46+72</TD><TD>=</TD><TD> </TD><TD>=46+72</TD><TD> </TD><TD>=46+72</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>46+73</TD><TD>=</TD><TD> </TD><TD>=46+73</TD><TD> </TD><TD>=46+73</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>46+74</TD><TD>=</TD><TD> </TD><TD>=46+74</TD><TD> </TD><TD>=46+74</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>46+75</TD><TD>=</TD><TD> </TD><TD>=46+75</TD><TD> </TD><TD>=46+75</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>46+76</TD><TD>=</TD><TD> </TD><TD>=46+76</TD><TD> </TD><TD>=46+76</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>46+77</TD><TD>=</TD><TD> </TD><TD>=46+77</TD><TD> </TD><TD>=46+77</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=CONCATENATE(B1,A1)</TD></TR><TR><TD>D2</TD><TD>=CONCATENATE(B2,A2)</TD></TR><TR><TD>D3</TD><TD>=CONCATENATE(B3,A3)</TD></TR><TR><TD>D4</TD><TD>=CONCATENATE(B4,A4)</TD></TR><TR><TD>D5</TD><TD>=CONCATENATE(B5,A5)</TD></TR><TR><TD>D6</TD><TD>=CONCATENATE(B6,A6)</TD></TR><TR><TD>D7</TD><TD>=CONCATENATE(B7,A7)</TD></TR><TR><TD>D8</TD><TD>=CONCATENATE(B8,A8)</TD></TR><TR><TD>D9</TD><TD>=CONCATENATE(B9,A9)</TD></TR><TR><TD>D10</TD><TD>=CONCATENATE(B10,A10)</TD></TR><TR><TD>D11</TD><TD>=CONCATENATE(B11,A11)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Note: Column F was simply a .. Copy -> Paste Special -> Values .. of column D.

This is an example given with a very limited set of data; imagine there being 1000+ lines of code. There has be a better solution than the manual method.

~ Busypee :)
 
Upvote 0
Try this - it might work:

Code:
Sub try()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .Value = .Value
End With
End Sub
 
Upvote 0
Try this - it might work:

Code:
Sub try()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .Value = .Value
End With
End Sub


Thanks for the effort VoG, but nothing changes when I run that macro.

I also tried calling Application.CalculateFull by pressing
Ctrl+Alt+Shift+F9 without any luck.

~ Busypee :confused:
 
Upvote 0
Apparently it wasn't that simple after all. Can anyone help please? Maybe if I word it better....

Imagine me hitting F2 then Enter, F2 then Enter, F2 then Enter...like 2000 times. I'm actually getting pretty fast at it, but I know there has to be a way to automate it. My fingers would appreciate it.
 
Upvote 0
Have you tried
Code:
Sub blah()
  Calculate
End Sub
Also, do you have Change event code that is trigged when one cell is changed, but not if multiple cells change?
 
Upvote 0
Have you tried
Code:
Sub blah()
  Calculate
End Sub
Also, do you have Change event code that is trigged when one cell is changed, but not if multiple cells change?

Hello mikeerickson,

I tried to use your macro in my simple example above, but it did not do anything.

I'm honestly very surprised there is not a simpler way of selecting every cell and pressing "Enter."


I'm not familiar with VBA enough, but could a code be produced that selects every cell with a value, replicates pressing "F2", and then the "Enter" key?

This would solve the problem,

~ Busypee :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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