#excel #vba
#excel #vba
Вопрос:
У меня есть фрагмент кода, который использует оператор 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" amp; r amp; "-H" amp; r
Правка1: изменены все 11 секунд на 12 секунд
Edit2: решит проблему с листами (используя только второй код, поскольку его будет легче изменить в будущем):
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" amp; r amp; "-H" amp; r
End With
Я скопировал / вставил каждый из кодов, которые я предоставил изначально, и проверил, что они работают при связывании с модулем.
Комментарии:
1. Спасибо. Я пытаюсь сделать это сейчас. Я получил объект, который не поддерживает это свойство или сообщение об ошибке метода при попытке первого. Я не упоминал об этом, но кнопка и столбцы находятся на разных листах. Кнопка находится на листах (Начало) столбцы находятся на листах (отчет). Я сомневаюсь, что именно поэтому я получаю это конкретное сообщение об ошибке, но я попробую вторую версию.
2. Хм, значит, он получает правильный расчет и помещает его в правильный столбец, но он не попадает на нужный лист. Если это слишком много работы, я мог бы просто поместить кнопку в лист отчета. Такая большая помощь была очень кстати.
3. @JoeBlack вы убедились, что имя листа обновлено для вашего?
With Sheets("Report")в моем коде. При тестировании это помещает точную формулу в лист отчета, независимо от того, откуда я запускаю код (кнопка на случайном листе или меню alt f8 были отмечены).4. лол, Спасибо за помощь, все работает так, как я надеялся. У меня был .range(.cells(r,»G).cells(lr,»G»)). Я не ставил запятую перед .cells. Удаление этого имело значение. Спасибо за всю помощь!
5. @JoeBlack это не проблема. пожалуйста, отметьте это как ответ (флажок слева от моего сообщения), чтобы это не оставалось в списке без ответа.