Задачка VBA-Excel спецам.

Звдачка по макросам в Excel-e.

Надо, чтобы программа искала в списке ключевое слово ( в коде это «Choibolsan») и копировала в другой лист инфу из определенных столбцов в этой строке.

Собрав какие были мысли, слепил макрос для Excel.

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

И на этом меня вчера заклинило.
А учитывая, что я Excel- самоучка, заклинило нещадно.

Подозреваю, что надо заменить оператор целиком. На что-нибудь, типа Lookup. Если, кто поможет с меня причитается.

______________________________________________

Sub CopyChoibolsan()
‘Copy cells of cols A,F,E,D from rows containing Stat# in
‘col D of the active worksheet (source sheet) to cols
‘A,B,C,D of Sheet2 (destination sheet)

Sheets(«Info»).Select

Dim DestSheet As Worksheet
Set DestSheet = Worksheets(«Sheet1»)

Dim sRow As Long ‘row index on source worksheet
Dim dRow As Long ‘row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 2

For sRow = 1 To Range(«D65536»).End(xlUp).Row

‘use pattern matching to find «Stat#» anywhere in cell
If Cells(sRow, «D») Like [color=red:wp1rygx4]»*Choibolsan*»[/color:wp1rygx4] Then
sCount = sCount + 1
dRow = dRow + 1
‘copy cols A,F,E & D
Cells(sRow, «L»).Copy Destination:=DestSheet.Cells(dRow, «A»)
Cells(sRow, «T»).Copy Destination:=DestSheet.Cells(dRow, «B»)
Cells(sRow, «N»).Copy Destination:=DestSheet.Cells(dRow, «C»)
Cells(sRow, «H»).Copy Destination:=DestSheet.Cells(dRow, «D»)
Cells(sRow, «M»).Copy Destination:=DestSheet.Cells(dRow, «E»)
Cells(sRow, «F»).Copy Destination:=DestSheet.Cells(dRow, «F»)
Cells(sRow, «O»).Copy Destination:=DestSheet.Cells(dRow, «G»)
Cells(sRow, «AI»).Copy Destination:=DestSheet.Cells(dRow, «H»)
End If
Next sRow

MsgBox sCount & » Stats# copied», vbInformation, «Transfer Done»

End Sub

___________________________

Мерси.

Canada Mama
Canada Mama
Статей: 2998

Похожие записи

Комментариев нет

  1. Может, я не очень поняла, что значит ссылкой?

    А просто подставить нужную ячеку нельзя?

    Например:

    If Cells(sRow, «D») Like [color=red:100xqua4]»*» + Cells(sRow, «A»).Value + «*» [/color:100xqua4]Then

    если клетка в А-столбце?

  2. Я и хочу, чтобы стоял адрес на клетку, куда вводить слово для поиска.

    «*» + Cells(sRow, «A»).Value + «*»

    Не работает. Нужен адрес типа:

    Sheets(«Info»).Select
    Range(«A1»).Select

    Проблема в том, что после Like требуется текст, а не адрес ячейки. А

  6. =VLOOKUP(A12,’C:\dir\dir\dir\[filename.xls]worksheet_name’!$B$23:$H$86,4,FALSE)

    Вот такая хрень возьмёт значение ячейки А12, найдёт на страничке worksheet_name в файле filename.xls это значение и вернёт то, что находится в 4-м столбце в строке с этим значением. То есть если ‘Choibolsan’ будет на позиции B47, то эта функция выдаст тебе значение поля E47.

  7. [quote="Bjorkmann":2cr4qo3k]=VLOOKUP(A12,’C:\dir\dir\dir\[filename.xls]worksheet_name’!$B$23:$H$86,4,FALSE)

    Вот такая хрень возьмёт значение ячейки А12, найдёт на страничке worksheet_name в файле filename.xls это значение и вернёт то, что находится в 4-м столбце в строке с этим значением. То есть если ‘Choibolsan’ будет на позиции B47, то эта функция выдаст тебе значение поля E47.[/quote:2cr4qo3k]
    Тебе вроде в теле макроса просят, а не в ячейке.

  8. [quote="Нет-нет":2m26tim9][quote="Bjorkmann":2m26tim9]=VLOOKUP(A12,’C:\dir\dir\dir\[filename.xls]worksheet_name’!$B$23:$H$86,4,FALSE)

    Вот такая хрень возьмёт значение ячейки А12, найдёт на страничке worksheet_name в файле filename.xls это значение и вернёт то, что находится в 4-м столбце в строке с этим значением. То есть если ‘Choibolsan’ будет на позиции B47, то эта функция выдаст тебе значение поля E47.[/quote:2m26tim9]
    Тебе вроде в теле макроса просят, а не в ячейке.[/quote:2m26tim9]И куда потом выводить из этого тела? Наверняка в другую ячейку? Choibolsan, куда надо найденное значение выводить? Или надо заполнить столбец и непонятно сколько там будет таких значений?

  9. [quote="Нет-нет":44advmty]dim choi

    choi = range(«A1»)

    dalshe — kak hochesh[/quote:44advmty]

    Дело в том, что Like не видит переменную после себя. Как текст ( если ковычками взять) находит прекрасно.

  11. [quote="Bjorkmann":j92jeq29][quote="Нет-нет":j92jeq29][quote="Bjorkmann":j92jeq29]=VLOOKUP(A12,’C:\dir\dir\dir\[filename.xls]worksheet_name’!$B$23:$H$86,4,FALSE)

    Вот такая хрень возьмёт значение ячейки А12, найдёт на страничке worksheet_name в файле filename.xls это значение и вернёт то, что находится в 4-м столбце в строке с этим значением. То есть если ‘Choibolsan’ будет на позиции B47, то эта функция выдаст тебе значение поля E47.[/quote:j92jeq29]
    Тебе вроде в теле макроса просят, а не в ячейке.[/quote:j92jeq29]И куда потом выводить из этого тела? Наверняка в другую ячейку? Choibolsan, куда надо найденное значение выводить? Или надо заполнить столбец и непонятно сколько там будет таких значений?[/quote:j92jeq29]

    За это отвечает вот эта часть:

    [code:j92jeq29]Cells(sRow, "L").Copy Destination:=DestSheet.Cells(dRow, "A")
    Cells(sRow, "T").Copy Destination:=DestSheet.Cells(dRow, "B")
    Cells(sRow, "N").Copy Destination:=DestSheet.Cells(dRow, "C")
    Cells(sRow, "H").Copy Destination:=DestSheet.Cells(dRow, "D")
    Cells(sRow, "M").Copy Destination:=DestSheet.Cells(dRow, "E")
    Cells(sRow, "F").Copy Destination:=DestSheet.Cells(dRow, "F")
    Cells(sRow, "O").Copy Destination:=DestSheet.Cells(dRow, "G")
    Cells(sRow, "AI").Copy Destination:=DestSheet.Cells(dRow, "H") [/code:j92jeq29]

    Фактически из списка в сто рядов и двести столбов, я выбираю нужные данные и делаю себе новую компактную таблицу, согласно критерия.

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

  12. [quote="Choibolsan":3953gypk][quote="Bjorkmann":3953gypk][quote="Нет-нет":3953gypk][quote="Bjorkmann":3953gypk]=VLOOKUP(A12,’C:\dir\dir\dir\[filename.xls]worksheet_name’!$B$23:$H$86,4,FALSE)

    Вот такая хрень возьмёт значение ячейки А12, найдёт на страничке worksheet_name в файле filename.xls это значение и вернёт то, что находится в 4-м столбце в строке с этим значением. То есть если ‘Choibolsan’ будет на позиции B47, то эта функция выдаст тебе значение поля E47.[/quote:3953gypk]
    Тебе вроде в теле макроса просят, а не в ячейке.[/quote:3953gypk]И куда потом выводить из этого тела? Наверняка в другую ячейку? Choibolsan, куда надо найденное значение выводить? Или надо заполнить столбец и непонятно сколько там будет таких значений?[/quote:3953gypk]

    За это отвечает вот эта часть:

    [code:3953gypk]Cells(sRow, "L").Copy Destination:=DestSheet.Cells(dRow, "A")
    Cells(sRow, "T").Copy Destination:=DestSheet.Cells(dRow, "B")
    Cells(sRow, "N").Copy Destination:=DestSheet.Cells(dRow, "C")
    Cells(sRow, "H").Copy Destination:=DestSheet.Cells(dRow, "D")
    Cells(sRow, "M").Copy Destination:=DestSheet.Cells(dRow, "E")
    Cells(sRow, "F").Copy Destination:=DestSheet.Cells(dRow, "F")
    Cells(sRow, "O").Copy Destination:=DestSheet.Cells(dRow, "G")
    Cells(sRow, "AI").Copy Destination:=DestSheet.Cells(dRow, "H") [/code:3953gypk]

    Фактически из списка в сто рядов и двести столбов, я выбираю нужные данные и делаю себе новую компактную таблицу, согласно критерия.

    Все работает, но для смены критерия поиска надо каждый раз вносить новое значение в макрос. А хочется иметь ячейку, куда этот критерий вводить, не трогая тело макроса.[/quote:3953gypk]Зачем в макрос? Вноси в ячейку А12 вместо ‘Choibolsan’ значение ‘Chinghizkhan» и автоматически меняются значения по ячейкам, где vlookup работает.

  13. [quote="Нет-нет":yhbyhu32]Я не очень хорошо знаю оператор like, а почему там нельзя просто знаком равно обойтись?[/quote:yhbyhu32]

    После него равно не катит.

  15. [quote:1qjwy5ek]
    А хочется иметь ячейку, куда этот критерий вводить, не трогая тело макроса.
    [/quote:1qjwy5ek]

    Да я вроде бы это и предложила?
    Хотите отдельный лист?
    Пожалуйста:

    Поисковая фраза (без звездочек) пишется на Sheet2 в ячейке Cells(1, «A»).

    Dim SearchSheet As Worksheet
    Set SearchSheet = Worksheets(«Sheet2»)

    If Cells(sRow, «D») Like «*» + SearchSheet.Cells(1, «A»).Value + «*» Then

    или вы хотите и звездочки там задавать текстом?

  16. [quote="Bjorkmann":2pqg6ghs] Зачем в макрос? Вноси в ячейку А12 вместо ‘Choibolsan’ значение ‘Chinghizkhan» и автоматически меняются значения по ячейкам, где vlookup работает.[/quote:2pqg6ghs]

    У меня же не каждый раз одно и то же кол-во строк будет.

    Иначе, надо забивать vlookup на весь лист. А это будет офигенно тормозить компьютер, т.к. vlookup будет при каждом пуке персчитывать весь лист до дна.

    Скажем список в 1000 строк уже тебе даст минут 5-10 обновления листа.

  17. да и со звездочками можно:

    If Cells(sRow, «D») Like SearchSheet.Cells(1, «A»).Value Then

    Ячейка SearchSheet.Cells(1, «A») содержит *ccc*

    я запускаю, срабатывает if для Cells(sRow, «D») = «1ccc2»

  18. [quote="Choibolsan":o9f6rvo1]Иначе, надо забивать vlookup на весь лист. А это будет офигенно тормозить компьютер, т.к. vlookup будет при каждом пуке персчитывать весь лист до дна.

    Скажем список в 1000 строк уже тебе даст минут 5-10 обновления листа.[/quote:o9f6rvo1]Думаешь что макрос сделает это быстрее? Сейчас ради прикола запустил на update таблицу с примерно 7 тысячами vlookup (vlookup происходит в 5 «внешних» файлах) — заняло 12 секунд.

  19. Типа так что -ли ?

    [code:18ew2q53]...
    Dim SearchSheet As Worksheet
    Set SearchSheet = Worksheets("Sheet2")

    sCount = 0
    dRow = 2

    For sRow = 1 To Range("D65536").End(xlUp).Row

    Sheets("Info container").Select

    If Cells(sRow, "D") Like SearchSheet.Cells(1, "A").Value Then

    ...[/code:18ew2q53]

    Эту строку он не хочет переваривать. Требует дебага.

    [color=red:18ew2q53]Set SearchSheet = Worksheets(«Sheet2»)[/color:18ew2q53]

  22. А если ты каждый раз будешь содержимое сравниваемой ячейки закидывать в переменную, свой критерий — в другую переменную и сравнивать их знаком равенства?

  23. ОК. Я просто сам тупил. Проблема была в имени одного из названий листа.

    А так, скрипт Олена встал как надо.

    Всем спасибо за участие.

    Олен, с меня магар.

Ответить

Сейчас популярно

Котятам исполнился год
kursi po kakoi nibudi spetzialinosti na rodine….nujni li?
Как подать документы в Express Entry?
Продление экстренных выплат жителям Канады