» Excel VBA (часть 2)

Цитата:

в а2 забить сумму, в В2 формулу, например такую
=ЕСЛИ(И(A2<6000;A2>=1500);"3x"&(A2/3);ЕСЛИ(A2>=6000;"6x"&(A2/6);"1x"&A2)

Простите, я вас не понял

Вставил формулу, получилось так:


Началная сумма 10000
Дата     Транскакция Всего
11.01.2007    -6200     6x1033,33333333333


А нужно, примерно так:

Началная сумма 10000
Дата     Трансакция Всего
11.01.2007    -6200     8966,67
11.02.2007 выплата 2 7933,34
итд

Tanya3000
Smog

Цитата:

Ребят есть два задания из теста...... объясните пожалуйста:

Вообще-то есть такая вещь, как отладчик. Можно вставить код (а он, к тому же, небольшой) в VBA и по шагам помотреть, что он и как делает. Не забываем о Watch Expression (просмотр значений переменных).
Когда учишь язык - хороший способ понять, что к чему.

AndVGri,
nick7inc,
Smog
Выражаю Вам большую человеческую благодарность за Ваши большие и маленькие подсказки мне. В итоге курсовую с интергированием приложений я доделала и защитила на 4.
БОЛЬШОЕ СПАСИБО!

kalinakrasnay
Поздравляю!

nick7inc

Цитата:

Вообще-то есть такая вещь, как отладчик. Можно вставить код (а он, к тому же, небольшой) в VBA и по шагам помотреть, что он и как делает. Не забываем о Watch Expression (просмотр значений переменных).

Ну я-то это знаю

Цитата:

Когда учишь язык - хороший способ понять, что к чему.

причем не только когда учишь
kalinakrasnay

Цитата:

Выражаю Вам большую человеческую благодарность за Ваши большие и маленькие подсказки мне. В итоге курсовую с интергированием приложений я доделала и защитила на 4.
БОЛЬШОЕ СПАСИБО!

gett

Цитата:

А нужно, примерно так:

чтобы 6 раз было написано, что отняло?

Цитата:

чтобы 6 раз было написано, что отняло?

Да
Хочу сделат простой счетчик трансакций с кредитной карточке. Банк у меня, если сумма более чем 1500 делит ее на три месеца, а если болше 6000, делит ее на 6 месеца. На пример, если заплатил 2100, кажди ,есец со счета снимается по 700, если заплатил 9000 каждий месец снимается по 1500.
Как это поставит в excel?
При этом, excel я знаю на очень очень низком уровне :о

gett

Цитата:

Хочу сделат простой счетчик трансакций с кредитной карточке. Банк у меня, если сумма более чем 1500 делит ее на три месеца, а если болше 6000, делит ее на 6 месеца.

Мне не совсем понятно правило округления, ведь сумма может нацело не поделиться.
Я использовал ОКРВНИЗ() (Округляет число до кратного заданной точности с недостатком).
Вот пример: Ссылка.
Если что непонятно - спрашивайте.

nick7inc

Цитата:

Мне не совсем понятно правило округления, ведь сумма может нацело не поделиться.

это не принципиально. Б Вашем примере надо excel-у сказать результат =FLOOR($A4/$B4;1) снимат с счета каждий месец


Цитата:

Примерно так:

Началная сумма 10000
Дата Трансакция Всего
11.01.2007 -6200 8966,67
11.02.2007 выплата 2 7933,34
11.03.2007 выплата 3 6900
11.04.2007 выплата 4 5867
и так далее

Сделал я базовую таблицу http://rapidshare.com/files/83535507/proba.zip но не умею разделит суммы как описал выше

gett

Цитата:

Сделал я базовую таблицу

Формат какой? У меня Excel 2000 не открывает.

nick7inc
ой! вот http://rapidshare.com/files/83780597/proba.zip предидуший бил excel 2007

Господа,
Есть такая задача: в ячейках цифро-буквенные сочетания, иногда с пробелами. Надо все литерные символы и пробелы убрать, чтобы остались только числа. Буквы можно заменить любыми цифрами в принципе. Как это сделать?
Попытался стандартными средствами через
Cells.Replays What:= "[a-zA-Z]", Replacement:="0"

В результате VBA замену делать не хочет, [a-zA-Z] воспринимает не как маску, а как последовательность, которую надо искать.
Какие могут быть варианты?

mrdime
Фанкшн
StrLen = Len(str)
RemoveNonNumeric = ""
For i = 1 To StrLen
c = Mid(str, i, 1)
If c Like "#" Then RemoveNonNumeric = RemoveNonNumeric & c
Next i

visual73
Я хотел это реализовать через Cells.Replays... Но, в любом случае - спасибо. Написал, запусти у себя - все работает, а это главное.

начались глюки с гиперссылками в ячейках, посмотрел и выяснил, что в адресе все пробелы заменены сочетанием %20 (точно не помню)

Написать макрос по замене этой абракадабры на пробел не составит труда но...

Вопрос:
а как собственно изъять адрес из ячейки? т. е. надо:

dim a as string
a=гиперссылка_ячейки

CEMEH
Найдем все ячейки рабочего листа, содержащие гиперссылки, и сформируем массив с адресами ссылок.

Sub Hyper()
Dim a As String, Cell As Range, Adr()
ActiveSheet.UsedRange.Select
ReDim Adr(1 To Selection.Count)
i = 1
For Each Cell In Selection
On Error Resume Next
a = Cell.Hyperlinks(1).SubAddress 'адрес ячейки
'a = Cell.Hyperlinks(1).Address 'путь ссылки
If Err = 0 Then
Adr(i) = a
i = i + 1
End If
Next Cell
ReDim Preserve Adr(1 To i - 1)
End Sub

Нужна помощь. Цель - копировать значения из массива E102:I106 в конец таблицы, а не постоянно в D117. Копироваться будет всегда именно массив E102:I106.

Код:
Range("E102:I106").Select
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
Range("D117").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Может не Range("D117"), а так:
Range("E102:I106").Select
Selection.Copy
Cells(Cells.SpecialCells(xlLastCell).Row + 1, "D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

SAS888, работает, спасибо большое

SAS888
СПАСИБО!

есть еще ВОПРОС:
Как в массиве выбрать только уникальные значения?

'Имеем некую переменную
dim A(100) as string
'тут код, который заполняет эту переменную значениями
For x=1 to 100: A(X)= cells(x,1): next x

'теперь убираем повторяющиеся значения
For X=1 to 100
For Y=1 to 100
IF A(X)=A(Y) and X<>Y Then A(Y)=""
Next Y
Next X

Может существует более простое и быстрое решение?

CEMEH
Как-то оно не совсем так

Код: Dim B() as String
Dim I as Integer
I=0

For X= 1 to 99
If A(X) <> "" Then
For Y = 2 to 100
IF A(X) = A(Y) Then
A(Y) = ""
I = I + 1
End If
Next Y
End If
Next X

'далее если захочется уплотнить массив
ReDim B(100 - I)
I = 0
For X=1 to 100
If A(X) <> "" Then
I = I + 1
B(I) = A(X)
End If
Next X

CEMEH

Код:
Public Sub Unique()
Dim Values As Variant, i As Long, iRow As Long
Dim pKeys As New Scripting.Dictionary

'поиск уникальных значений
Values = Range(Cells(1&, 1&), Cells(100&, 1&)).Value
For i = LBound(Values) To UBound(Values)
If Not pKeys.Exists(Values(i, 1&)) Then pKeys.Add Values(i, 1&), 0
Next i

'вывод уникальных значений
Values = pKeys.Keys
iRow = 1&
For i = LBound(Values) To UBound(Values)
Cells(iRow, 2&).Value = Values(i)
iRow = iRow + 1&
Next i
End Sub

Иногда (может пригодиться) бывает удобнее создать не массив, а коллекцию. А т. к. двух одинаковых членов коллекции быть не может, то:

Sub MyCol()
Dim i As Integer, MyCollect As New Collection
On Error Resume Next
For i = 1 To 100
MyCollect.Add Cells(i, 1)
Next i
On Error GoTo 0
End Sub

Теперь обратиться к членам коллекции можно при помощи For Each....Next

Имею:

Dim A As Double
A = 1.9641 - 1.964

В результате получаю A = 9,9999999999989E-05 !

Вопрос:
C чем это связано? и
Возможно ли избежать этого, без привлечения дополнительного кода и функций округления?

gett

Цитата:

Сделал я базовую таблицу http://rapidshare.com/files/83535507/proba.zip но не умею разделит суммы как описал выше

Ссылка
Всё равно я не совсем понимаю, как именно надо сделать вам таблицу, ваш пример мне не совсем понятен (ну не бухгалтер я и в украинском не особо разбираюсь).
Как я понял вашу ситуацию написано [more=здесь] есть какие-то закупки. Они датированы определённым числом, например, 20 марта 2007 года. Должна быть исходная таблица (или место в таблице, если она будет всего одна), в которую это всё заносится. Потом есть вторая таблица (место), в которой отражаются транзакции банка.

Предлагаю вам нарисовать более понятную таблицу с подписями что есть что (очень желательно, чтобы они были на русском, или, в крайнем случае, на английском языке). Подписи легко делать путём добавления примечаний к ячейкам (правая кнопка мышки по ячейке, добавить примечание).

Чем понятнее вы растолкуете, что вам нужно, тем больше шансов у вас получить ответ. И, если вы этим занялись, учите Excel, пригодится.
[/more]
Как работает мой пример написано [more=здесь]
здесь я буду ссылаться на свой пример (файл - Книга1.xls).
1) имеем сумму платежа, надо высчитать размер транзакции банка.
Делаем это поиском по таблице. В столбце A у нас полная сумма платежа. В столбце B - формула, которая ищет в таблице на второй вкладке листа значение в первом столбце и берёт справа от него число транзакций. Смотрите описание функции ВПР().
2) У нас есть колл-во транзакций, делим всю сумму на неё и округляем по правилам банка. Это и есть сумма, выплачиваемая в первый месяц*.
Наверху над каждой суммой стоит номер транзакции. Если транзакция не последняя, то берётся сумма первого масяца(*), иначе вычисляется остаток: разность основной суммы и суммы всех танзакций предыдущих месяцев.
[/more].

подскажите, чем может быть вызвано:
есть форма (userform1), в ней combobox, прописанный кодом

Private Sub ComboBox3_Enter()
UserForm1.ComboBox3.SetFocus
Worksheets("списки").Activate
Dim Диапазон As String
Dim n As Integer
n = Application.CountA(Range("c:c"))
Диапазон = "c1:c" & CStr(n)
ComboBox3.RowSource = Диапазон
End Sub

работает, при вводе текста предлагает варианты подстановки....

далее из userform1 я вызываю userform3 следующим образом

Private Sub TextBox7_Change()
If TextBox7.Value = "0" Then
UserForm3.Show
UserForm3.ComboBox1.SetFocus
.....
End If
End Sub

в userform3 тоже есть combobox'ы прописанные таким же (!!!!) кодом
только одна загвоздка - при вызове userform3 из userform1 при вводе теста в combobox автоподстановка вариантов НЕ работает.
причем если напрямую запустить userform3 отдельно от userform1 - то все ОК

з.ы.
fmMatchEntryFirstLetter прописано и в свойствах бокса, и в UserForm3_Initialize

где искать корни? я в ступоре

abyrrr
1) Используйте ссылки с указанием имени листа. Если работаете вне предела одной книги, то и имени книги тоже.
2)
Цитата:

ComboBox3.RowSource = Диапазон

должен быть в одной из следующих функций: UserForm_Initialize() или UserForm_Activate() (думаю, что первый вариант предпочтительнее, а второй - более затратный), причём для каждой из форм.

Добавлено:
Для получения адреса можно воспользоваться Address() вместо
Цитата:

Диапазон = "c1:c" & CStr(n)

Вот пример:
Код: Dim r As Range

With Workbooks("Книга1").Sheets("Лист1") ' или ActiveSheet
Set r = Range(.Cells(1, "A"), .Cells(5, "B"))
' Вместо имени столбца можно использовать его номер, как целое число (без кавычек)
End With

MsgBox r.Address(True, True, xlA1, True)
' получится что-то вроде [Книга1]Лист1!$A$1:$B$5

Цитата:

Используйте ссылки с указанием имени листа. Если работаете вне предела одной книги, то и имени книги тоже

работаю в пределе 1 книги

з.ы.
список мне предлагается и в моем варианте написани..., автоподстановка не работает:
т.е. я начинаю писать "лялялятополя" - программа его мне не предлагает после того как я напишу букву "т", если же я разверну список значений и/или начну нажимать "вниз" то это слово будет в списке

за полезные советы по поводу Adress и т.д. большое спасибо ) имхо - буду использовать

Сразу после открытия книги делаю

Set BottleOst = Worksheets.Add
BottleOst.Name = "БутылкаОстатки"

После этого
MsgBox (BottleRashod.CodeName)

выдает пусто. Из-за чего это может происходить? CodeName надо получить для программного добавления методов

abyrrr

Цитата:

список мне предлагается и в моем варианте написани

Упс, я вас неправильно понял. С автоподстановкой - не знаю. Можно попробовать скрыть первое окно перед выводом второго, а после - опять его показать.

Добавлено:
HarryG

Цитата:

MsgBox (BottleRashod.CodeName)
выдает пусто.

Ошибка в коде. Надо вместо MsgBox (BottleRashod.CodeName) написать msgbox(BottleOst.CodeName), а вообще есть такая штука, как sheets("БутылкаОстатки").codename
Добавлено:

Цитата:

для программного добавления методов

А можно с этого места по-подробнее, если не секрет, конечно.

Здравствуйте!

Столкнулся с такой проблемой: пишу макрос, который должен циклически присваивать диапазонам строк имена, номер строки передается в виде аргумента (счетчик, котрый "пробегает" вс строки листа). Первоначальная идея была такая:

d = "строковое_выражение" + str(i)
Rows (i, i+5).Name = d
Но компилятор выдает ошибку, что неправильно заданы аргументы Rows. Я пробовал так:
Rows("i:i+5"), Rows("i,i+5"), Rows(i:i+5), Rows(i,i+5), Rows('i:i+5'), в общем, все возможные варианты, причем Rows(i), Rows (n*i) - синтаксически верные команды. Кто-нибудь знает, как верно передавать аргументы в Rows или есть какойй-нибудь другой способ циклически именовать диапазоны?