IF function and Cycle

Question

I have a question regarding excel vba and the use of the "IF" function with the "For" cycle. I will try to be as clear as possible.

here is the code:

sub cicleFor()
For i= 1 to 2
Calculate
Range("E3").Select
Selection.Copy    Sheets("Sheet2").Select    Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Sheets("Sheet1").Select
Range("C14").Select
Next

It is pretty simple! I take E3, copy paste into another cell (A2) for two times. I use calculate at the start because the number in E3 will change each time

What I would like to have is the following:

to use an "IF" function that, if A2 is full, goes to A3 and so on, for i = 1 to 100.

since I have used a for function, i want A2,A3,A4,...A100 to be filled with the result of E3 of the sheet1.

I am not an expert as you can see!

if you have any hint, I will be grateful!

Thank you!

You might want to make calculations manual, and only calculate *after* the loop completed. Or, if you need a specific cell calculated, qualify that `Calculate` call with a `Range` object. Also read [this](https://stackoverflow.com/q/10714251/1188513) — Mathieu Guindon, Feb 13 '18 at 19:00
Is there a reason why you wouldn't just populate the cells with the formula `=Sheet1!$E$3`? — Lee Mac, Feb 13 '18 at 19:01

DisplayName · Accepted Answer · 2018-02-14T09:42:41.720

1

EDIT to add the case of multiple cells

maybe you're after this

Sub cicleFor()
    For i = 1 To 100
        Calculate
        Sheets("Sheet2").Cells(1 + i, 1).Value = Sheets("Sheet1").Range("E3").Value
    Next
End Sub

if you need to copy/paste the content of more than one cell then you want to use Resize(nRows, nColumns) property of Range object to properly adjust the "target" range size to fit the "source" one

for instance to copy/paste the content of range "E3:H3" (i.e. four columns) you want to use:

Sub cicleFor()
    For i = 1 To 100
        Calculate
        Sheets("Sheet2").Cells(1 + i, 1).Resize(,4).Value = Sheets("Sheet1").Range("E3:H3").Value
    Next
End Sub

edited Feb 14 '18 at 09:42

answered Feb 13 '18 at 18:49

DisplayName

13,283
2
11
19

thank you! this is what I wanted! only one thing: what if, instead of copying only "E3", I want to copy a line (e.g. "F3:H3")?? – Steven Ingo Feb 14 '18 at 09:36
@StevenIngo, you are welcome. So which answer did actually solve your question? Mine, or Tim Williams' one? – DisplayName Feb 14 '18 at 11:36

Tim Williams · Answer 2 · 2018-02-13T20:21:11.170

0

Your code could be much shorter - no need for select/copy/paste if you only want to transfer the values. Use End(xlUp) to locate the last used cell.

Sub cicleFor()

    For i= 1 to 2
        Calculate
        Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _
              Sheets("Sheet1").Range("E3").Value

    Next
End Sub

edited Feb 13 '18 at 20:21

answered Feb 13 '18 at 18:45

Tim Williams

154,628
8
97
125

You've landed in the LQ queue... No explanation or anything? – Cindy Meister Feb 13 '18 at 20:15
thank you! this is what I wanted as above! only one thing: what if, instead of copying only "E3", I want to copy a line (e.g. "F3:H3")?? – Steven Ingo Feb 14 '18 at 09:38

IF function and Cycle

2 Answers2