Excel VBA - Loop through range and set formula in each cell

Question

I've got a workbook where I have one worksheet which contains a lot of data. My goal is to create a macro that inserts a formula in a separate sheet to copy the data from the first sheet. Lets call the first sheet "Numbers1" and the second sheet "TidyNumbers1".

In the sheet "TidyNumbers1" I want to loop through each cell from column A to M and rows 1 to 60. So I've got a macro that so far looks like this:

   Sub updateFormulasForNamedRange()
    Dim row, col, fieldCount As Integer
    colCount = 13
    RowCount = 60

    For col = 1 To colCount
        For row = 1 To RowCount
            Dim strColCharacter

            If col > 26 Then
                strColCharacter = Chr(Int((row - 1) / 26) + 64) & Chr(((row - 1) Mod 26) + 65)
            Else
                strColCharacter = Chr(row + 64)
            End If

            Worksheets("TidyNumbers1").Cells(row, col).Formula = "=IF(Numbers1!E" & col & "<>0;Numbers1!" & strColCharacter & row & ";"")"
        Next row
    Next col

End Sub

But the formula is supposed to looks like this for Column A, row 2:

IF(Numbers1!E2<>0;Numbers1!A2;"")"

And the formula in Column A, row 3 should look like this:

IF(Numbers1!E3<>0;Numbers1!A3;"")"

Formula in Column B, row 2 should look like this:

IF(Numbers1!E2<>0;Numbers1!B2;"")"

In other words, the formula looks to see if the value in Column E, row % is anything but 0 and copies it if conditions are met.

But, I see that I need to translate my integer variable Row with letters, because the formula probably needs "A" instead of 1. Also, I get a 1004 error (Application-defined or object-defined error) if I just try to use:

Worksheets("Numbers1").Cells(row, col).Formula = "=IF(Numbers1!E" & row & "<>0;Numbers1!" & col & row & ";"")"

I clearly see that the integer row should be translated to letters, if that's possible. Or if anyone has any other suggestions that might work. Also, the 1004 error is unclear to me why happens. I can define a string variable and set the exact same value to it, and there's no error. So it's probably the formula bar that whines about it I guess?

You want to replace your variable "col" with a letter, not "row" (as you wrote) — Doc Brown
– Doc Brown, Commented Jul 11, 2011 at 7:53
Ah of course :) First post updated. I managed to find a way of translating the integer with a letter. Think it works. But I still get this 1004 error — Kenny Bones
– Kenny Bones, Commented Jul 11, 2011 at 7:55

Community · Accepted Answer · 2017-05-23 10:25:18Z

1

Here is a former post of mine containing functions for conversion of column numbers to letters and vice versa:

VBA Finding the next column based on an input value

EDIT: to your 1004 error: Try something like this:

  =IF(Numbers1!E" & row & "<>0,Numbers1!A" & row & ","""")"

(use ; instead of ,, and "" for one quotation mark in a basic string, """" for two quotation marks).

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Jul 11, 2011 at 8:22

Doc Brown

20.2k7 gold badges55 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kenny Bones Over a year ago

I think I've got this somehow under control now. I only need columns A to M anyway. But the real problem right now is writing the formula in the cells. Where I get the 1004 error.

Jay D · Accepted Answer · 2012-05-22 02:10:42Z

1

Would not it be easier to get the cell address with the Cells.Address function?

For example:

MsgBox Cells(1, 5).Address

Shows "$E$1"

Best Regards

edited May 22, 2012 at 2:10

Jay D

3,3075 gold badges34 silver badges50 bronze badges

answered Jul 18, 2011 at 19:09

Tomamais

111 bronze badge

Collectives™ on Stack Overflow

Excel VBA - Loop through range and set formula in each cell

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related