Вопрос:
Я пытаюсь создать форму, которая, надеюсь, автоматически обновит список значений для отдельного раскрывающегося списка (без кодов VBA) при вводе пользователя.
Вот форма, которую пользователь увидит:
В настоящее время обе колонки F и H основаны на формуле проверки данных:
INDIRECT(«VList!»&SUBSTITUTE(ADDRESS(1,MATCH($B11,VList!$1:$1,0),1),»1″,»»)&»2:»&SUBSTITUTE(ADDRESS(1,MATCH($B11,VList!$1:$1,0),1),»1″,»»)&COUNTA(INDIRECT(«VList!»&ADDRESS(1,MATCH($B11,VList!$1:$1,0),4)&»:»&ADDRESS(100,MATCH($B11,VList!$1:$1),4))))
… где VList ссылается на лист, как показано ниже:
Поэтому мой вопрос здесь, на основе имени проекта в столбце B, есть способ обновить список в листе VList со значением «Cost Per Unit» [Cell E11], чтобы выпадающий список в F12 и H12 автоматически обновлено со значением «Cost Per Unit»?
Долгое время занимался этим безрезультатно, поэтому я надеюсь найти здесь некоторых экспертов, чтобы увидеть, возможен ли такой сценарий без VBA. Благодарю!
Изменить: Мне сказали, что коды VBA могут запускаться автоматически при изменении значения ячейки, поэтому я открыт для любых решений/помощи с VBA. Тем временем мы будем исследовать это направление!
Edit2: Добавлена простая иллюстрация ниже, которая, мы надеемся, лучше отражает то, что я пытаюсь достичь в excel: 
* Edit3: Я начинаю исследовать метод Worksheet_SelectionChange, и это то, что я вышел до сих пор:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim projectName As String Dim VariableList As Worksheet Dim Form As Worksheet Dim thisRow As Integer Dim correctColumn As Integer Dim lastRow As Integer Set VariableList = ThisWorkbook.Sheets(«VList») Set Form = ThisWorkbook.Sheets(«Form») On Error GoTo EndingSub If Target.Column = 5 Then thisRow = Target.Row projectName = Form.Cells(thisRow, 2) correctColumn = Application.Match(projectName, VariableList.Range(«1:1»), 0) lastRow = VariableList.Columns(correctColumn).Find(«*», SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row VariableList.Cells(lastRow + 1, correctColumn).value = Form.Cells(5, thisRow).value End If EndingSub: End Sub
Как-то значение Form.Cells(5, thisRow).Value всегда пусто.
Если я изменю его на Target.Value он по-прежнему принимает предыдущее значение, которое было введено (например, я сначала поместил “ABC” в новую переменную, он не обновляется. Я изменил новую переменную на “DEF”, обновил список с “ABC” вместо “DEF”). Он также принимает ВСЕ значения, которые находятся под столбцом E каким-то образом.
Кроме того, нажатие Enter после того, как я поместил один вход в E11, также вызывает обновление значений E11 и E12 при изменении только E12. Однако, если я нажимаю кнопку мыши после ввода E11, тогда обновляется только значение E11.
Что именно я делаю неправильно здесь?
Лучший ответ:
Мне было очень весело с этим, если кто-то может усовершенствовать прикрученные части, не стесняйтесь исправлять.
Я также рекомендую использовать таблицы. Я понимаю, что вы можете писать длинные формулы для обозначения диапазонов, но указание имени вашей таблицы дает расширенный список с простой ссылкой.
Private Sub Worksheet_Change(ByVal Target As Range) Dim NewVar As Range On Error GoTo Err Set NewVar = Range(«C:C») ‘data entered here, could be a referstorange kind of named range reference If Application.WorksheetFunction.CountA(Intersect(Target, NewVar)) <> 0 Then Call ertdfgcvb(Target, NewVar) ‘only run if there an intersect, f*ed up but works anyway Err: End Sub Sub ertdfgcvb(Target As Range, NewVar As Range) Dim ws As Worksheet, Valid As Long, project As String, ListElmnt As String, Unlisted As Boolean, rng1 As Range, rng2 As Range Set ws = Sheets(«VList») ‘the data that you refresh Valid = 2 ‘projects in column B HeaderRow = 1 ‘headers in Vlist are in row #1 uRow = Cells.Rows.Count ‘f* yeah, compatibility considerations For Each Cell In Intersect(Target, NewVar) ‘will evaluate for each cell individually, in case you were to insert columns ListElmnt = Cell.Value2 ‘stores the prospective list element r = Cell.Row ‘stores the list element row to… project = Cells(r, Valid).Value2 ‘identify the related project HeaderRowRef = HeaderRow & «:» & HeaderRow ColumnNum = ws.Range(HeaderRowRef).Find(What:=project, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookAt:=xlWhole).Column ‘finds the project in VList ‘MsgBox ws.Name Set rng1 = ws.Cells(HeaderRow + 1, ColumnNum) Set rng2 = ws.Cells(uRow, ColumnNum) LastRow = ws.Range(ws.Cells(HeaderRow + 1, ColumnNum), ws.Cells(uRow, ColumnNum)).Find(«*», SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ‘finds the last row for the project in VList ‘f*ed up but works Unlisted = True ‘assumes it unlisted For x = HeaderRow + 1 To LastRow If ListElmnt = CStr(ws.Cells(x, ColumnNum).Value2) Then Unlisted = False ‘unless proven otherwise Next If Unlisted Then ws.Cells(LastRow + 1, ColumnNum) = ListElmnt ‘if it unlisted it gets appended to the end of the list Next End Sub
РЕДАКТИРОВАТЬ:
Как очистить таблицу, например:
Sub ert() Dim rng As Range Set rng = Range(«Táblázat1») ‘obviously the table name Do While x < rng.Rows.Count ‘for each row If rng(x, 1).Value2 = «» Then ‘if it empty rng(x, 1).Delete Shift:=xlUp ‘then delete but retaining the table format Else x = x + 1 ‘else go to the next line (note: with deletion comes a shift up!) End If Loop End Sub
