macro to repeat formula until empty cell is found

Question

Hi,

I'm looking for a macro that will return a specific formula in a cell until the moment that an empty cell is found.

I have 2 columns:

In column A there are the values and in column B I need a formula.

The length of the file varies from week to week.

I prefer not to use a script that determines end of the file at the beginning of the macro but would rather work with a "Do Until Is Empty".

The formula in column B should be

            ActiveCell.FormulaR1C1 = _

            "=IF(RC[-1]=1,""A"",IF(RC[-1]=2,""B"",IF(OR(RC[-1]=3,RC[-1]=4),""C"",IF(RC[-1]=5,""D"",IF(OR(RC[-1]=6,RC[-1]=7),""E"")))))"

Answer 1

Hi,

This does it the way you want which isn't the way I'd do it. I'd find the last row and fill down but of course it's up to you.

Sub somesub()

Dim x As Long

'Starting on row 2 so change to suit

x = 2

Do While Cells(x, 1) <> ""

Cells(x, 2).FormulaR1C1 = _

             "=IF(RC[-1]=1,""A"",IF(RC[-1]=2,""B"",IF(OR(RC[-1]=3,RC[-1]=4),""C"",IF(RC[-1]=5,""D"",IF(OR(RC[-1]=6,RC[-1]=7),""E"")))))"

x = x + 1

Loop

End Sub

Answer 2

The thing is I need to filter on the result file and copy this result list to another sheet. If I then use the end of file determination without saving the file first it'll always copy the formula until the original amount of lines from the first sheet.

So for me the end of file only works for the first part of the macro.

The option you provided with the loop works perfectly for me.

Thanks...

Answer 3

Cells(x, 2).FormulaR1C1 = _

             "=IF(RC[-1]=1,""A"",IF(RC[-1]=2,""B"",IF(OR(RC[-1]=3,RC[-1]=4),""C"",IF(RC[-1]=5,""D"",IF(OR(RC[-1]=6,RC[-1]=7),""E"")))))"

Hi.  You have a solution.    Just an idea...

=CHAR(64+RC[-1]-(RC[-1]>3)-(RC[-1]>6))