Я пытаюсь создать подпрограмму, которая будет вводить формулу в ячейку, заполнять формулу vlookup до последней строки, затем копировать формулу и pastespecial->values для всего диапазона. Таблица, которую я использую в vLookup, находится в отдельном файле, который не всегда хранится в одном месте. Таблица всегда форматируется одинаково, но размер таблицы не всегда одинаков.
Я должен сделать это на 4 разных листах, и столбец, в который я должен ввести эту формулу, имеет заголовок «Оценка заказа». Я использую .Find, чтобы вернуть местоположение "Order Grade". Затем я хочу ввести свою строку Vlookup 1 ниже, где находится «Оценка заказа».
если я введу формулу вручную на листе, она будет выглядеть так:
=VLOOKUP(C2,[newpipe.xlsx]Sheet1!$A$1:$B$376,2,FALSE)
в VBA формула, которую я хочу построить, будет выглядеть примерно так:
=vlookup(RC[-1],stringFileName\[newpipe.xlsx]Sheet1!$A$1:LastColumn & LastRow,2,False
Когда пользователь выбирает stringFileName в диалоговом окне открытия файла. LastColumn и LastRow на выбранном листе должны быть рассчитаны макросом.
Вот что у меня есть на данный момент.
Private Function UseFileDialogOpen()
Dim myString As String
' Open the file dialog
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
myString = .SelectedItems(1)
'MsgBox myString
UseFileDialogOpen = myString
Else
MsgBox ("Failed to properly open file")
myString = "fail"
UseFileDialogOpen = myString
End If
End With
End Function
Sub formatOrderColumn()
Dim strSearch
Dim foundColumn
Dim foundRow
Dim RowBelowSpotFound
Dim fileLocation
strSearch = "Order Grade"
Set aCell = ActiveSheet.Rows(1).Find(what:=strSearch, LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
If Not aCell Is Nothing Then
foundColumn = aCell.Column
foundRow = aCell.Row
spotFound = ColumnLetter(foundColumn) & foundRow + 1
' MsgBox "Value Found in Row " & foundRow & _
" and the Column Number is " & foundColumn
Else
Exit Sub
End If
fileLocation = UseFileDialogOpen()
LastColumn = FindLastColumn(UserSelectedSheet)
LastRow = FindLastRow(UserSelectedSheet)
Range(RowBelowSpotFound).Formula = _
"=vlookup(RC[-1], [" & fileLocation & "]Sheet1!$A$1:" & LastColumn & lastrow & ",2,False"
End Sub
Я не знаю, как получить lastrow и lastColumn из выбранного пользователем файла. У меня есть функции, которые делают это для любого переданного им рабочего листа. Я понимаю, что плохо справился с объяснением своей ситуации, и совсем не уверен, что собираюсь делать это наилучшим образом. Если у вас есть вопросы, дайте мне знать, и я постараюсь уточнить. Я скоро выйду из офиса, поэтому, возможно, не смогу ответить до утра.
Вот новая формула. Я получаю сообщение об ошибке в последней строке, когда пытаюсь установить в формуле ячейки смещения строковое значение. Строковое значение верное. Я получаю ту же ошибку, если пытаюсь установить значение ячейки напрямую, без использования держателя mystring для первого построения строки. "ошибка приложения или объекта"
Sub vlookupOrderGrade()
Dim strSearch
Dim fileLocation
Dim aCell As Range
Dim aCellString
Dim myString As String
strSearch = "Order Grade"
Set aCell = ActiveSheet.Rows(1).Find(what:=strSearch, LookIn:=xlValues, _
Lookat:=xlWhole, MatchCase:=True)
If Not aCell Is Nothing Then
fileLocation = UseFileDialogOpen()
If fileLocation <> "fail" Then
'replace last "\" with a "["
fileLocation = StrReverse(fileLocation)
fileLocation = Replace(fileLocation, "\", "[", 1, 1)
fileLocation = StrReverse(fileLocation)
'build string
myString = "=vlookup(" & _
ColumnLetter(aCell.Column - 1) & aCell.Row + 1 & _
", '" & fileLocation & "]Sheet1'!$A:$B,2,False"
MsgBox (myString)
'set cell to string
aCell.Offset(1, 0).Formula = myString
End If
Else
Exit Sub
End If
End Sub
