Кнопка вычитания столбца
У меня есть фрагмент кода, который использует оператор select для SQL для генерации данных на основе определенного диапазона дат. Он отлично работает и делает то, что я хочу. Два из этих столбцов — это то, о чем мой вопрос, и это F и H. F — это сумма платежа, а H — баланс. G в этом случае предполагается как потраченная сумма (которая представляет собой разницу между суммой платежа и балансом). То, что я пытаюсь сделать, это после выполнения запроса, и мне дается x количество записей, которые, если я нажимаю кнопку F в каждой строке, вычитается соответствующей H той же строкой, и результат попадает в столбец G
Sub Button1_Click()
Range("G11").End(xlDown).Formula = Range("F11").End(xlDown).Value - Range("H11").End(xlDown).Value
End Sub
SQL начинает вставлять данные с 12-й строки, поэтому у меня есть G11.End(xlDown).
В основном я надеюсь, что если данные находятся в 12-й строке в F / H и равны 8,3, а в 20-й строке - как 13,5, то G в 12-й и 20-й строках будет 5 и 9
Ответы 1
Вы можете вставить одну формулу в диапазон, при условии, что вы запишете ее с помощью динамической, нефиксированной ($) нотации, чтобы она использовала эквивалентную строку/столбец, так что:
dim lr as long
lr = cells(rows.count,"F").end(xlup).row 'finds the last row in column F
range(cells(12,"G"),cells(lr,"G")).formula = "=F12-H12" 'note that the formula destination and the source data start on 12
Если вы предвидите изменение строки 11 или просто хотите упростить исправление позже, вы можете сделать что-то вроде:
dim lr as long, r as long
r = 12 'can change this, or make in inputbox...
lr = cells(rows.count,"F").end(xlup).row 'finds the last row in column F
range(cells(r,"G"),cells(lr,"G")).formula = "=F" & r & "-H" & r
Редактировать1: изменил все 11 на 12
Редактировать2: Решит проблему с листами (используя только второй код, так как его будет легче изменить в будущем):
With Sheets("Report")
dim lr as long, r as long
r = 12 'can change this, or make in inputbox...
lr = .cells(.rows.count,"F").end(xlup).row 'finds the last row in column F
.range(.cells(r,"G"),.cells(lr,"G")).formula = "=F" & r & "-H" & r
End With
Я скопировал/вставил каждый из кодов, которые я предоставил изначально, и убедился, что они работают, когда связаны с модулем.
Хм, так что он получает правильный расчет и помещает его в правильный столбец, но он не идет на правильный лист. Если это слишком много работы, я могу просто положить кнопку на лист отчета. Эта большая помощь была великой.
@JoeBlack вы убедились, что имя листа обновлено для вашего? With Sheets("Report") в моем коде. При тестировании это помещает точную формулу в лист отчета, независимо от того, откуда я запускаю код (кнопка на случайном листе или меню alt + f8 были проверены).
lol Спасибо за помощь, это работает, как я и надеялся. У меня был .range(.cells(r,"G).cells(lr,"G")). Я не ставил запятую перед .cells. Удаление этого имело значение. Спасибо за помощь!
@JoeBlack не проблема. пожалуйста, отметьте это как отвеченное (флажок слева от моего сообщения), чтобы оно не осталось в списке без ответа.
Спасибо. Я пытаюсь это сейчас. Я получил сообщение об ошибке Object не поддерживает это свойство или метод при попытке первого. Я не упомянул об этом, но кнопка и столбцы находятся на разных листах. Кнопка находится на листах (начало), столбцы находятся на листах (отчет). Я сомневаюсь, что именно поэтому я получаю это конкретное сообщение об ошибке, но я попробую вторую версию.