Форум программистов, компьютерный форум, киберфорум
Наши страницы
VBA
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.74/34: Рейтинг темы: голосов - 34, средняя оценка - 4.74
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
1

Вставка относительных ссылок в формулу посредством переменных

29.11.2012, 17:20. Просмотров 6157. Ответов 10
Метки нет (Все метки)

Добрый день.
я который раз возвращаюсь к одной загвоздке в своем макросе и никак не могу ее решить.

помогите пожалуйста.

есть вырезка из кода:

Visual Basic
1
2
k = ActiveCell.Offset(-9, -5).Address(0, 0, ReferenceStyle:=xlR1C1)
s = ActiveCell.Offset(-9, -6).Address(0, 0, ReferenceStyle:=xlR1C1)
* * * * * * * *
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
For i = 10 To 50
With Cells(i, u)
             Select Case Cells(i, 5).Interior.Color
            Case 255, 6750207, 14211288
                .ClearContents
            Case 10092543, 10092492
 
                .FormulaR1C1 = _
                    "=IF(AND(" & k & "=0," & s & "=0),"""",IF(" & s & "/" & TextBox1.Value & ">=R2C2," & k & "/" & s & ",0))"
            End Select
    End With
Next i
я хочу, чтобы макрос, прописывая формулу (во второй части) оставлял именно адреса ячеек (при просмотре формулы в самой книге Excel). При попытки менять координаты параметра "Address(0, 0," - ссылка на ячейки становятся абсолютными.


причем координаты офсета для k и s (ActiveCell.Offset) сбиваются при наличии этого самого Address (т.е на деле офсет выделяет не то что нужно), что не дает мне сделать формулу универсальной. придется для каждого значения ТекстБокса1 прописывать свои координаты, чего бы не хотелось.


помогите пожалуйста додумать.

Добавлено через 9 минут
походу тут нужен какой то цикл, ибо макрос растягивает данную формулу на весь столбец, и координаты строчек должны меняться....
 Комментарий модератора 
Найдите разницу между фразой "по средствам" и предлогом "посредством"!
http://ru.wiktionary.org/wiki/%D1%81...82%D0%B2%D0%BE
http://ru.wiktionary.org/wiki/%D0%BF...B2%D0%BE%D0%BC
0
QA
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
29.11.2012, 17:20
Ответы с готовыми решениями:

Использование относительных ссылок и функций в именах-формулах
добрый день. проблема подробно описана в файле, здесь коротко суть: 1) при программном...

WebBrowser: указание относительных ссылок для локального сайта
Мне необходимо создать форму для просмотра локального сайта (сайт находится на локальном ПК). В...

Передача значений переменных или ссылок, неконтролируемое изменение переменных
// заголовок класса ObservableCollection<LineDom> Method1; Method1 = new...

Вставка CSS в HTML посредством VBScript
Всем доброго денёчка. Во время дорабатывания скрипта, который отправляет встроенную в него HTML...

присвоить ячейке Googledocs формулу ссылающуюся на другой лист посредством javascript
Извините если не туда написал. Пытаюсь в google docs прописать формулы посредством javascript ...

10
Скрипт
5452 / 1133 / 49
Регистрация: 15.09.2012
Сообщений: 3,429
29.11.2012, 19:16 2
caustic, смоделируйте свою ситуацию: упростите формулу, сделайте законченный код, выложите его на форуме и поясните, что в нём не получается.
0
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
30.11.2012, 08:38  [ТС] 3
Цитата Сообщение от Скрипт Посмотреть сообщение
caustic, смоделируйте свою ситуацию
у меня есть табличка (в приложении)
главная особенность а так же причина моей проблемы в написании макроса в том, что первой части таблицы за месяц берется одна колонка, а во второй - две.

Сейчас по упрощенной методе объясню:
сначала макрос данными заполняет вторую часть таблицы, определяя месяц, который юзер выбрал через ComboBox1:
Visual Basic
1
2
3
4
5
6
7
8
9
       a = ComboBox1.Value 
For Each c In [N2:AK2]
    If c.Value = a Then
       c.Offset(3).Select 'нашел нужный месяц (рассмотрим на примере февраля), спустился на 3 строки вниз и выделил ячейку 
    End If
Next
'
k = ActiveCell.Address(0, 0, ReferenceStyle:=xlR1C1) 'пытаюсь назначить переменную k для выделенной ячейки
s = ActiveCell.Offset(0,1).Address(0, 0, ReferenceStyle:=xlR1C1) 'пытаюсь назначить переменную s от выделенной ячейки на 1 колонку вперед (второй столбик месяца)
далее (опустив часть кода макроса) определив месяц из комбобокса по той же методе но для первой части таблицы, хочу подставить в формулу свои переменные с сохранением формулы с адресами ячеек:
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
u = ActiveCell.Column
For i = 10 To 50
With Cells(i, u)
             Select Case Cells(i, 5).Interior.Color
            Case 255, 6750207, 14211288
                .ClearContents
            Case 10092543, 10092492
 
                .FormulaR1C1 = _
                                "= & k & / & s &"      'хочу чтоб в экселе была именно формула с адресами ячеек
            End Select
    End With
Next i
помогите мне пожалуйста разобраться:
1) как правильно скорректировать 8 и 9 (первая часть макроса) строчки так, чтобы макрос оставлял формулу с сылками на ячейки (как показано в прикрепленном файле)
-почему не подходит способ который уже есть:
да, этот способ работает, оставляет формулу в виде в каком я хочу. но при этом способе сбивается координаты Offset, что не позволяет мне создать универсальную формулу для каждого месяца

2)необходимо, чтобы эта формула растягивалась на весь столбец (опять же как показано в прикрепленном файле)
PS. сейчас она растягивается, но из за того что, повторюсь, координаты офсета сбиваются, мне не желателен данный способ


спасибо!
0
Вложения
Тип файла: xls Книга1 - копия.xls (36.0 Кб, 7 просмотров)
Скрипт
5452 / 1133 / 49
Регистрация: 15.09.2012
Сообщений: 3,429
30.11.2012, 09:26 4
Пункт 1

caustic, сообщение #3, второй код, строка 11 - просто опечатались вы?


Пункт 2

Условия выполнения кода:
  1. используется книга, выложенная в сообщении #3;
  2. активная ячейка P5.
Что не так в этом коде?
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
Sub Procedure_1()
 
    Dim k As String, s As String
 
    'В переменную "k" помещаю адрес активной ячейки.
    k = ActiveCell.Address(0, 0, ReferenceStyle:=xlA1)
    s = ActiveCell.Offset(0, 1).Address(0, 0, ReferenceStyle:=xlA1)
    
    Range("C5").Formula = "=" & k & "/" & s
 
End Sub
0
30.11.2012, 09:26
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
30.11.2012, 09:30  [ТС] 5
Цитата Сообщение от Скрипт Посмотреть сообщение
caustic, сообщение #3, второй код, строка 11 - просто опечатались вы?
да, мой косяк.
Visual Basic
1
2
                .FormulaR1C1 = _
                    "=" & k & "/" & s & ""
и так между делом, для чего нижнее подчеркивание применяется в некоторых случаях для записи формулы?
0
Скрипт
5452 / 1133 / 49
Регистрация: 15.09.2012
Сообщений: 3,429
30.11.2012, 09:33 6
Цитата Сообщение от caustic Посмотреть сообщение
и так между делом, для чего нижнее подчеркивание применяется в некоторых случаях для записи формулы?
нижнее подчёркивание используется для написания кода, чтобы переносить текст на следующую строку, чтобы текст не выходил за рамки монитора.

Т.е. нижнее подчёркивание используется для удобства написания и чтения кода.
1
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
30.11.2012, 09:48  [ТС] 7
Цитата Сообщение от Скрипт Посмотреть сообщение
Что не так в этом коде?
ответьте мне только на один вопрос:
координаты заданные офсетом не должны сбиваться, при добавлении
к строчке
Visual Basic
1
s = ActiveCell.Offset(0, 1)
дополнение:
Visual Basic
1
s = ActiveCell.Offset(0, 1).Address(0, 0, ReferenceStyle:=xlA1)
потому что у меня в первом варианте выходит одна координата (правильная)

при втором - координата смещается куда то вправо на совсем не логичную ячейку вне таблицы


Если не должна, буду разбираться в своем макросе
0
Скрипт
5452 / 1133 / 49
Регистрация: 15.09.2012
Сообщений: 3,429
30.11.2012, 09:55 8
caustic, не понятен вопрос. Напишите вопрос в соответстви с кодом в сообщении #4.
0
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
30.11.2012, 10:20  [ТС] 9
первый случай
получаем правильный результат в ячейке CB13. но формула в ней записана без ссылок на ячейки а лишь значения взятые с ячеек

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 Sub Proverka()
 
Dim a, k, s As String
 
For Each b In [BC4:BZ4]
       If b.Value = "март" Then
          b.Offset(3).Select
             k = ActiveCell
          ActiveCell.Offset(0, 1).Select
             s = ActiveCell
       End If
Next
 
Range("CB13").FormulaR1C1 = "=" & k & "/" & s & ""
 
 End Sub


второй случай
после него в ячейке CB13 находится формула с сылками на ячейки, но ссылаются они на абсолютно "левые" ячейки: =EH22/EI22

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 Sub Proverka()
 
Dim a, k, s As String
 
For Each b In [BC4:BZ4]
 
       If b.Value = "март" Then
          b.Offset(3).Select
             k = ActiveCell.Address(0, 0, ReferenceStyle:=xlR1C1)
          ActiveCell.Offset(0, 1).Select
             s = ActiveCell.Address(0, 0, ReferenceStyle:=xlR1C1)
       End If
Next
 
Range("CB13").FormulaR1C1 = "=" & k & "/" & s & ""
 
 
 End Sub
P.s. некоторые координаты в макросе могут быть нарушены, ибо пишу уже с рабочего компьютера, но суть остается той же: координаты сильно сбиваются при добавлении ".Address(0, 0, ReferenceStyle:=xlR1C1)"

Добавлено через 8 минут
все что я хочу - это запись формулы с относительными адресами ячеек и возможностью растягивать ее на весь столбец...
0
Скрипт
5452 / 1133 / 49
Регистрация: 15.09.2012
Сообщений: 3,429
30.11.2012, 10:25 10
Пункт 1

Цитата Сообщение от caustic Посмотреть сообщение
Visual Basic
1
Dim a, k, s As String
в VBA, если вы хотите задать тип данных переменной, то нужно тип данных задавать каждой переменной. Правильно вот так:
Visual Basic
1
Dim a As String, k As String, s As String
В вашем же случае:
Visual Basic
1
Dim a, k, s As String
переменные a и k имееют тип данных Variant.


Пункт 2

Цитата Сообщение от caustic Посмотреть сообщение
получаем правильный результат в ячейке CB13
а при чём ячейка CB13 и книга из сообщения #3?

Пишите сообщения в соответствии с книгами, которые вы выкладываете.
1
caustic
18 / 18 / 0
Регистрация: 30.09.2011
Сообщений: 283
30.11.2012, 13:29  [ТС] 11
Цитата Сообщение от Скрипт Посмотреть сообщение
а при чём ячейка CB13 и книга из сообщения #3?
это для примера, чтоб показать разницу и скачки координат

Добавлено через 3 минуты
т.к. на самом деле эта ячейка определяется исходя из выбранного значения ComboBox1. я просто упростил макрос

Добавлено через 2 часа 44 минуты
вопрос снимаю. забил в макрос координаты всех месяцов под переменные
0
30.11.2012, 13:29
Answers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
30.11.2012, 13:29

Вставка записей в ДБФ-таблицу посредством использования оператора СЕЛЕКТ
Созданную предварительно пустую dbf-таблицу собираюсь заполнить данными нескольких "селектов". Для...

Как организовать вывод переменных посредством printf
Интересует такой нюанс. Как организовать вывод этих переменных таким образом? т.е....

вставка ссылок кодом
1-й документ по специальной кнопке выполняет создание док-та в другой базе Set db...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
11
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.