функция ВПР

[batiq]

Код: Выделить всё

Range("C" + CStr(i)).Cells.Formula = Application.WorksheetFunction.VLookup(Range("B" + CStr(i)).Cells, Workbooks("sitebase.xls").Worksheets(BSCName).Application.Range("A:B"), 2, False)


невозможно получить свойства VLookup класса WorksheetFunction..

[GSerg]

А нафига ты WorksheetFunction сюда приплёл? Я же тебе синтаксис привёл. В двух вариантах.

Код: Выделить всё

range(i, 3).formula = "=vlookup(B" & cstr(i) & ", [sitebase.xls]" & BSCName & "!$A:$B, 2, FALSE())"

[batiq]

Ура!.
Спасибо..

[batiq]

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

[batiq]

а можно ли сделать так чтоб в я чейке был уже результат, а не формула?.

[GSerg]

Вообще-то формула рульнее, имхо...

Код: Выделить всё

range(i, 3).value = Application.WorksheetFunction.VLookup(Range("B" & CStr(i)), Workbooks("sitebase.xls").Worksheets(BSCName).Range("A:B"), 2, False)

Только придётся книгу открыть...


А чтобы книгу не открывать, можно проставить ячейкам .formula, а потом для каждой проставленной сделать

Код: Выделить всё

.value=.value

[batiq]

Вообще-то формула рульнее, имхо...

при открытии лист просит обновить ли данные (от источника с другой книги)..

Код: Выделить всё

range(i, 3).value = Application.WorksheetFunction.VLookup(Range("B" & CStr(i)), Workbooks("sitebase.xls").Worksheets(BSCName).Range("A:B"), 2, False)

ошибка: невозможно получить свойство VLookup класса Worksheets..

[GSerg]

Вторым способом воспользуйся, а?..

[batiq]

Вторым способом воспользуйся, а?..

я не понял как..

[batiq]

где найти справку по функциям.. почему у меня нет справки по vlookup?.

[batiq]

Так правильно?:

Код: Выделить всё

Range("C" + CStr(i)).Formula = "=vlookup(B" & CStr(i) & ", [sitebase.xls]" & BSCName & "!$A:$B, 2, FALSE())"
Range("C" + CStr(i)).Value = Range("C" + CStr(i)).Value

..

[GSerg]

Да, так правильно.

А справка по vlookup есть справка по ВПР.

[batiq]

Следующий код работает в том случае если функция VLookUp находит искомый текст, иначе код дает ошибку: "Невозможно получить свойство VLookUp.."

Код: Выделить всё

If Application.WorksheetFunction.VLookup(BSCName, Worksheets("SiteBase Conformity").Range("A:B"), 2, False) = "" Then
ConformityBSCName = InputBox("", , OfferedSheetsName)
Else: ConformityBSCName = BSCName
End If


[GSerg]

Ну да, on error goto ...

[batiq]

может условие изменить?.

[GSerg]

Неа...

[batiq]

on error goto

Куда его вставить (как оно работает)?.

[GSerg]

Ы...

Код: Выделить всё

on error goto notfound
Application.WorksheetFunction.VLookup BSCName, Worksheets("SiteBase Conformity").Range("A:B"), 2, False
ConformityBSCName = BSCName

exit function

notfound:
ConformityBSCName = InputBox("", , OfferedSheetsName)

end function

[batiq]

Спасибо GSerg, uhm и Alibek!.

Код: Выделить всё

Private Function FindConformity(ByVal BSCN As String, ByVal OfferedSht As String) As String
On Error GoTo notfound
    FindConformity = Application.WorksheetFunction.VLookup(BSCN, Worksheets("Conformity On SiteBase").Range("A:B"), 2, False)
    Exit Function
notfound:
    FindConformity = InputBox("Ââåäèòå èìÿ ñîîòâåòñòâóþùåé ÁÄ êîíòðîëëåðà", , OfferedSht)
End Function


[batiq]

Подскажите зачем ByVal..

[Слобожанин]

Может кто подскажет...
У меня есть динамический массив, например:

Код: Выделить всё

Dim Массив()
ReDim Массив(переменная)

Можно ли его каким либо образом подставить в конструкцию

Код: Выделить всё

Application.WorksheetFunction.VLookup(BSCN, Worksheets("Conformity On SiteBase").Range("A:B"), 2, False)

вместо Worksheets("Conformity On SiteBase").Range("A:B").
BSCN - строковая переменная.

Заранее благодарю.

С уважением,
Слобожанин.

[GSerg]

Пробовал?

[Слобожанин]

Уважаемый GSerg,
Пробовал...
Получилось следущее..

Код: Выделить всё

Dim искомое As String, планеты As String, НачальнаяСтрока As Long
искомое = "Марс"
Dim Массив()
ReDim Массив(КолПланет)
    For k = 1 To КолПланет
        Массив(k) = Worksheets(планеты).Cells(НачальнаяСтрока + k, 1).Value
    Next k
Планета= Application.VLookup(искомое, Массив(), 1, True)


При этом, Массив получен такой
Массив(1)="Венера"
Массив(2)="Земля"
Массив(3)="Марс"
Массив(4)="Нептун"
Массив(5)="Плутон"

В результате мне выдаёт то, что находится под номером, которым обозначается столбец. Т.е. если "1", то "Венера", если "4" - "Нептун"

С уважением,
Слобожанин.

[GSerg]

Я чё-то не понимаю... какой смысл использовать ВПР на одномерном массиве? Конечная задача в чём?

[Слобожанин]

Уважемый GSerg,

...какой смысл использовать ВПР на одномерном массиве? Конечная задача в чём?

1. Одномерный массив выбран для простоты изложения.
2. В дальнейшем нужно будет находить значение массива находящееся наиболее близко к анализируемому. Например, мне нужно будет найти значение по ключевому полю наиболее близкое к значению Меркурий, т.е. между Марс и Нептун.


С уважением,
Слобожанин.

[GSerg]

Это странно.

1. Лучше излагать сразу, не назначая синий шарик для простоты жёлтым кубиком

Код: Выделить всё

  Dim arr(1 To 5, 1 To 5) As String
 
  arr(1, 1) = "Меркурий"
  arr(2, 1) = "Венера"
  arr(3, 1) = "Земля"
  arr(4, 1) = "Марс"
  arr(5, 1) = "Юпитер"
 
  arr(3, 2) = "sdf"
 
  MsgBox Application.WorksheetFunction.VLookup("Земля", arr, 2, 0)

работает, однако:

2. Если массив упорядочен по столбцу поиска, ВПР вернёт либо найденное значение, либо первое меньшее его. То есть при отсутствии Меркурия будет всегда найден Марс и никогда Нептун.

[Слобожанин]

Уважаемый GSerg,
Действительно,

Как только вы переберёте все варианты решения и не найдёте нужного, тут же обнаружится решение, простое и очевидное для всех, кроме вас

Ларчик просто открывался.
Нужно было заменить VLookup на HLookup, (т.к. при заполнении массива из таблицы произошло его транспонирование) и всё пошло очень просто.

__!____A______!___________B___________!
_1!___________!_______________________!
_2!___________!_______________________!
_3!___________!_______________________!
_4!___________!_______________________!
_5!__Планета__!Наименование поставщика!
_6!__Венера___!__________1____________!
_7!___Земля___!__________2____________!
_8!___Марс____!__________3____________!
_9!_Меркурий__!__________4____________!
10!_Юпитер____!__________5____________!

Код: Выделить всё

Sub Test()
Dim лист As String, КодПоиска As String

лист = "Планеты"
КодПоиска = "Нептун"
Dim Массив() As String
ReDim Массив(1 To 2, 1 To 5)
    For i = 1 To 2
        For j = 1 To 5
             Массив(i, j) = Worksheets(лист).Cells(5 + j, i).Value
        Next j
    Next i
Вставка = Application.WorksheetFunction.HLookup(КодПоиска, Массив, 1, 1)
End Sub

Очень вам благодарен за оказанную помощь.

С уважением,
Слобожанин.

[oleg11]

Подскажите, пожалуйста, как в VBA выполнить функцию ВПР или СУММЕСЛИ. т.е. надо найти значение на пересечении конкретных строк и столбцов в одном файле а затем это значение вставить на пересечении конкретных строк и столбцов в другом файле. в ячейках должны остаться только значения.
Вообще, задача состоит в последующем поиске другого значения и его вставки и так далее, пока не будут найдены все значения по столбцу. Затем то же надо проделать со следующим столбцом значений. Помогите, если можете.
спасибо.

[KL]

Подскажите, пожалуйста, как в VBA выполнить функцию ВПР или СУММЕСЛИ. т.е. надо найти значение на пересечении конкретных строк и столбцов в одном файле а затем это значение вставить на пересечении конкретных строк и столбцов в другом файле. в ячейках должны остаться только значения.
Вообще, задача состоит в последующем поиске другого значения и его вставки и так далее, пока не будут найдены все значения по столбцу. Затем то же надо проделать со следующим столбцом значений. Помогите, если можете.
спасибо.

(!) этот синтаксис при ошибке просто возвращает ее номер, не прерывая кода

Application.Vlookup()
Application.SumIf()

(!) этот синтаксис при ошибке выдает сообщение об ошибке с прерыванием кода

Application.WorksheetFunction.Vlookup()
Application.WorksheetFunction.SumIf()

WorksheetFunction.Vlookup()
WorksheetFunction.SumIf()

[KL]

Примеры:

Код: Выделить всё

x = Application.VLookup(Range("A1"),Range("C1:F100"),4,FALSE)
y = Application.CountIf(Range("C1:F100"),">0")