CODEWITHSUNDEEP
excelexcel-formulavba
Kenny Bones
Kenny Bones

Reputation: 5129

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

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?

Upvotes: 3

Views: 30628

Answers (2)

Tomamais
Tomamais

Reputation: 11

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

Upvotes: 1

Doc Brown
Doc Brown

Reputation: 20044

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).

Upvotes: 1

Related Questions