Very Simple Looping Question

[alnr]

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

 

[metropol]

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?

 

[VoG]

Hello and welcome to MrExcel.

Why do you need to do this - what is it meant to achieve?

 

[alnr]

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

 

[im2bz2p345]

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

 

[VoG]

Try this - it might work:

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

 

[im2bz2p345]

Try this - it might work:

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

 

[alnr]

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.

 

[mikerickson]

Have you tried

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?

 

[im2bz2p345]

Have you tried

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