Запутался в своем макросе. Выбор файла и диапазона

[Emmelman]

Всем доброго дня!

Подскажите, пожалуйста, как стоит поправить макрос, чтобы он работал корректно. Макрос записан в PERSONAL.XSLB, чтобы его вызов был доступен из любого файла excel.

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

Проблема в том, что не получается наладить выбор диапазона во время работы макроса.

Range(Выбор_диапазона()).Select - все ломается здесь! Вылетает ошибка method range of object _global failed

Вот текст кнопки:

Sub Analysis()
'
' Analysis Макрос
' ВПР субсчетов справочников с МСФО    
   Workbooks.Open Filename:=GetFileName()
      
     Range(Выбор_диапазона()).Select
    
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight
    Columns("D:E").Select
    Selection.NumberFormat = "General"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],8)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Книга3.xlsm]Общий!C4:C5,2,0)"
    Range("D2:E2").Select
    Selection.AutoFill Destination:=Range("D2:E15")
    Range("D2:E15").Select
End Sub

Columns("D:E").Select - на это и далее пока можно не обращать внимания, т.к. тоже нужен гибкий выбор параметров

Вот коды работающих функций:

Function GetFileName(Optional ByVal Title As String = "Выберите файл для обработки", _
                     Optional ByVal InitialPath, _
                     Optional ByVal MyFilter As String = "Книги Excel (*.xls*),") As String
    ' функция выводит диалоговое окно выбора папки с заголовком Title,
    ' начиная обзор диска с папки InitialPath
    ' возвращает полный путь к выбранной папке, или пустую строку в случае отказа от выбора
    If Not IsMissing(InitialPath) Then
        On Error Resume Next: ChDrive Left(InitialPath, 1)
        ChDir InitialPath    ' выбираем стартовую папку
    End If
    res = Application.GetOpenFilename(MyFilter, , Title, "Открыть")  ' вывод диалогового окна
    GetFileName = IIf(VarType(res) = vbBoolean, "", res)    ' пустая строка при отказе от выбора
End Function

Function Выбор_диапазона()
Dim myNum As Range
    Set myNum = Application.InputBox("Выбери диапазон", Type:=8)
    If myNum Is Nothing Then Exit Function
End Function

Буду очень признателен за помощь и совет!

[IgorGO]

Function Выбор_диапазона()
  Dim myNum As Range
  Set myNum = Application.InputBox("Выбери диапазон", Type:=8)
  If myNum Is Nothing Then Exit Function else set Выбор_диапазона = myNum
End Function

[Emmelman]

Я так понимаю, что myNum необходимо использовать в коде кнопки?

[IgorGO]

а вообще можно так:

Function Выбор_диапазона()
  Set Выбор_диапазона = Application.InputBox("Выбери диапазон", Type:=8)
End Function

и тут возникают сомнения по поводу целесобразности обьявления функции размером в 1 строку, но это больше дело вкуса...

и кроме того, это

Range(Выбор_диапазона()).Select

упадет с описанной Вами ошибкой, если пользователь закрыл окно выбора диапазона по ESC

  Dim SelRg as Range
  set SelRg = Application.InputBox("Выбери диапазон", Type:=8)
  if SelRg is Nothing then Exit Sub else SelRg.select

[IgorGO]

Я так понимаю, что myNum необходимо использовать в коде кнопки?

я так понимаю, что Вы не совсем понимаете что написали...
1. в коде какой кнопки???
2. нет такой необходимости
3. но если хочется - можете использовать

[Emmelman]

Игорь, взял вот этот код
Dim SelRg as Range
set SelRg = Application.InputBox("Выбери диапазон", Type:=8)
if SelRg is Nothing then Exit Sub else SelRg.select

Все заработало, большое вам спасибо! Прошу прощения за тот мой комментарий про кнопку, от неудачи тупанул!

[Emmelman]

Подскажите, пожалуйста, еще, если не сложно

вот этот кусок кода:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],8)"

Хочу переработать,таким образом, чтобы RC[-1] выбиралось пользователем.

Я взял то, что вы предложили, получилось вот так -

Set SelRg = Application.InputBox("Выбери диапазон для вставки формулы ЛЕВСИМВ", Type:=8)
  If SelRg Is Nothing Then Exit Sub Else SelRg.Select 
  ActiveCell.FormulaR1C1 = "=LEFT(" & SelRg & ",8)"

SelRg из InputBox в формулу не подставляется, в ячейке пусто. Может с синтаксисом напутал?

Также потом докручу таким образом, чтобы, когда выбирался диапазон, допустим D2, то в формулу подставлялась бы C2, т.е. ячейка слева. Возможно будет выглядеть вот так внутри формулы: [" & SelRg & "].Offset(, -1)

[IgorGO]

SelRg - это обьект типа Range, а в формулу нужен его адрес (насколько я понял: в стиле R1C1, относительно активной ячейки)

ActiveCell.FormulaR1C1 = "=LEFT(" & SelRg.address(false, false, xlr1c1, relativeto:=ActiveCell) & ",8)"

[Emmelman]

Код работает! Большое спасибо!

А как сделать сдвиг влево адреса диапазона?
ActiveCell.FormulaR1C1 = "=LEFT(" & SelRg.address(false, false, xlr1c1, relativeto:=ActiveCell) & ",8)"

Возможно ли вставить сюда Offset(, -1)? Или будет правильней вычесть единицу, чтобы произошло смещение

[IgorGO]

ActiveCell.FormulaR1C1 = "=LEFT(" & SelRg.address(false, false, xlr1c1, relativeto:=ActiveCell.offset(0,1)) & ",8)"

или

ActiveCell.FormulaR1C1 = "=LEFT(" & SelRg.offset(0,-1).address(false, false, xlr1c1, relativeto:=ActiveCell) & ",8)"