Excel VBA - цикл по диапазону и установка формулы в каждой ячейке

У меня есть рабочая книга, в которой у меня есть один рабочий лист, содержащий много данных. Моя цель — создать макрос, который вставляет формулу на отдельный лист для копирования данных с первого листа. Назовем первый лист «Numbers1», а второй лист «TidyNumbers1».

На листе «TidyNumbers1» я хочу перебрать каждую ячейку из столбцов от A до M и строк с 1 по 60. Итак, у меня есть макрос, который пока выглядит так:

   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

Но формула должна выглядеть так для столбца A, строки 2:

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

И формула в столбце А, строка 3 должна выглядеть так:

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

Формула в столбце B, строка 2 должна выглядеть так:

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

Другими словами, формула проверяет, является ли значение в столбце E, строке % чем-либо, кроме 0, и копирует его, если выполняются условия.

Но я вижу, что мне нужно перевести мою целочисленную переменную Row с буквами, потому что формуле, вероятно, нужна «A» вместо 1. Кроме того, я получаю ошибку 1004 (ошибка, определяемая приложением или объектом), если я просто пытаюсь использовать:

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

Я ясно вижу, что целочисленная строка должна быть переведена в буквы, если это возможно. Или если у кого-то есть другие предложения, которые могут сработать. Также мне непонятно почему возникает ошибка 1004. Я могу определить строковую переменную и присвоить ей точно такое же значение, и ошибки не будет. Так что, наверное, об этом ноет панель формул, я думаю?


excel excel-formula vba
person Kenny Bones    schedule 11.07.2011    source источник
comment
Вы хотите заменить свою переменную col буквой, а не строкой (как вы написали)   -  person Doc Brown    schedule 11.07.2011
comment
Ну конечно :) Первый пост обновлен. Мне удалось найти способ перевода целого числа буквой. Думаю, это работает. Но я все еще получаю эту ошибку 1004   -  person Kenny Bones    schedule 11.07.2011

Ответы (2)


arrow_upward
1
arrow_downward

Вот мой бывший пост, содержащий функции для преобразования номеров столбцов в буквы и наоборот:

VBA Поиск следующего столбца на основе входное значение

РЕДАКТИРОВАТЬ: к вашей ошибке 1004: попробуйте что-то вроде этого:

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

(используйте ; вместо , и "" для одной кавычки в базовой строке, """" для двух кавычек).

person Doc Brown    schedule 11.07.2011
comment
Думаю, теперь я как-то контролирую это. В любом случае мне нужны только столбцы от A до M. Но настоящая проблема сейчас — написать формулу в ячейках. Где я получаю ошибку 1004. - person Kenny Bones; 11.07.2011

arrow_upward
1
arrow_downward

Не проще ли получить адрес ячейки с помощью функции Cells.Address?

Например:

MsgBox Cells(1, 5).Address

Показывает "$E$1"

Наилучшие пожелания

person Tomamais    schedule 18.07.2011