Цикличен макрос за копиране на потребителски въведен диапазон и поставяне в потребителски въведен диапазон в неизвестна отворена работна книга

Аз съм много нов в VBA. От няколко седмици се опитвам да разработя код, който да имитира функцията vlookup и hlookup в Excel.

Създавам макрос, който копира и поставя данни от една работна книга в друга.

Данните ще имат референтни стойности в лявата колона както на изходната работна книга, така и на целевата работна книга.

Референтните стойности в целевата работна книга ще бъдат в различен ред от референтните стойности в работната книга източник.

Данните за копиране ще бъдат разположени на 4 колони от референтните стойности. Данните (заедно с референтните стойности) могат да бъдат дълги хиляди редове и стотици колони.

Целевата работна книга и работната книга, в която се намират изходните данни, ще бъдат отворени.

Потребителят ще посочи местоположението на референтните стойности както в работната книга източник, така и в работната книга местоназначение.

Потребителят също ще посочи диапазона от данни, които да бъдат копирани

Всички данни ще бъдат копирани.

Ето примерен файл, върху който съм работил. Действителните ми данни ще бъдат много повече от това. Данните се копират от тук: Оригинална работна книга

След това данните се поставят в тази работна книга. Целевата работна книга ще изглежда подобно, но можете да видите референтните данни в различен ред: Целева работна книга

Също така, след успешен цикъл в същия лист (където диапазонът за поставяне е същият лист в същата работна книга) получавам тази грешка: също получавам „Грешка по време на изпълнение 91. Обектна променлива или с блокова променлива не е зададена.“ Ето какво получих досега:

> Sub copyv5input()
>  
> Dim wsSrc As Worksheet Dim wbSrc As Workbook Dim wsTgt As Worksheet
> Dim wbTgt As Workbook Dim vRng1 As Range Dim vNo As Range Dim rNum As
> Integer Dim vRef1 As Range Dim vRng2 As Range Dim vDest1 As Variant
> Dim vDest2 As Variant Dim vDest3 As Range Dim cNum As Integer Dim
> cNum2 As String Dim vNew2 As Range
> 
> rNum = 1 
> cNum = 1 
>     Set vRng1 = Application.InputBox("Select the range of reference data:", Type:=8)    '1
>     Set vRef1 = vRng1.Cells(rNum, cNum)     '1
>     
>     
>     
>     Set vRng2 = Application.InputBox("Select the reference data range for destination:", Type:=8)   '2
>      
>         
>    
>     Set vDest1 = vRng2.Find(what:=vRef1)    '2
>     Set vDest2 = Range(vDest1.Address)      '2
>     Set vDest3 = vDest2.Offset(0, 1).Resize(, 4)    '2
> 
> Do While vRef1 <> ""
> 
> Set vNo = vRef1.Offset(0, 4).Resize(, 4)    '1
>          
>          If vRef1 = vDest1 Then
>         
>             vNo.copy Destination:=vDest3
>         
>         
>          End If
>     
>     rNum = rNum + 1
>         
>             Set vRef1 = vRng1.Cells(rNum, cNum)
>             Set vDest1 = vRng2.Find(what:=vRef1)
>             Set vDest2 = Range(vDest1.Address)      '2
>             Set vDest3 = vDest2.Offset(0, 1).Resize(, 4)
>      Loop
>        
> 
> End Sub

Благодаря предварително!


object loops excel copy-paste vba
person Jiggler    schedule 28.01.2018    source източник

Отговори (2)


arrow_upward
0
arrow_downward

Здравейте, добре дошли в stack overflow, страхувам се, че въпросът ви е малко труден за проследяване (или може би е само аз). Надявам се, че съм на прав път, но мисля, че искате

  1. изберете списък със стойности, които искате да търсите
  2. изберете диапазон с тези стойности и допълнителни данни в колони, които искате да върнете
  3. добавете търсената колона с данни към списъка с препратки

Можете да използвате свойството Application.WorksheetFunction за vlookup или начинът, по който бих направил, е да превъртя всяка стойност, търсейки съвпадение, след което да върна стойността на същия ред, но в друга колона. Това може да е малко бавно за дълги списъци с данни, но е просто и ще работи

 Sub copyv5input2()


 Dim vRng1 As Range

 Dim rNum As Integer
 Dim rNum2 As Integer
 Dim vRef1 As Range
 Dim vRng2 As Range
 Dim cNum As Integer
 Dim lookupV As String
 Dim foundR As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Set vRng1 = Application.InputBox("Select the range of reference data:", Type:=8)
Set vRng2 = Application.InputBox("Select the reference data range for destination:", Type:=8)   '2
cNum = Application.InputBox("Select the column number you want to return from reference data:")


For rNum = 1 To vRng1.Rows.Count

    lookupV = vRng1.Cells(rNum, 1).Value
    For rNum2 = 1 To vRng2.Rows.Count
        If vRng2.Cells(rNum2, 1) = lookupV Then
            vRng1.Cells(rNum, 1).Offset(0, 1) = vRng2.Cells(rNum2, cNum).Value
            foundR = foundR + 1
            GoTo 10
        End If
    Next rNum2
10
Next rNum

With Application
    .ScreenUpdating = true
    .Calculation = xlCalculationAutomatic

End With
MsgBox "complete, " & foundR & " values returned", vbInformation, "auto lookup"

 End Sub
person David wyatt    schedule 28.01.2018
comment
Благодаря Дейвид. Съжалявам, че не го предадох правилно. Причината за отместванията в моя код е, че данните започват на 4 колони от референтните клетки. Освен това идеята е да поставите всяка колона с данни, вместо просто да изберете една колона с данни. Целевата работна книга ще има същите препратки, но редът на редовете ще бъде различен. - person Jiggler; 28.01.2018
comment
Здравей Джиглър, не е проблем, вероятно съм бил аз повече от теб. За да изясним, искате ли да върнете повече от една колона (напр. 4, след това 5, след това 6 и т.н.) или избирате само референтната колона. Ако случаят е такъв, когато изпълнявате моя код, изберете цялата таблица върху референтните данни (колони от 1 до 5), след това въведете колона 5 в следващото поле за въвеждане. - person David wyatt; 28.01.2018
comment
Здравей @David Wyatt. Успях (в крайна сметка) да разбера вашия код. Модифицирах го, като премахнах декларацията cNum и след това, във вашия цикъл, добавих променлива на обект range.copy, след което готово! Благодаря отново - person Jiggler; 30.01.2018
comment
Страхотно е да чуя, да помогна - person David wyatt; 30.01.2018

arrow_upward
0
arrow_downward

Добре дошли в Stack Overflow!

Можете да използвате функциите на работния лист на Excel, като използвате функцията VBA: Application.Worksheet.

Например, имам функция за работен лист:

=VLOOKUP(D7,$A$2:$B$5,2,FALSE)

...така че във VBA бих могъл да направя диалогов прозорец MsgBox да се появи със същия резултат, като използвам:

MsgBox Application.WorksheetFunction.VLookup(Range("D7"), Range("$A$2:$B$5"), 2, False)

Повече информация:

person ashleedawg    schedule 28.01.2018
comment
Благодаря за това. Не съм сигурен обаче, че това ще проработи, тъй като не постига лесно целта. Както казах, работя с огромно количество данни. Ако имам данни, които са 2000 реда и 400 колони, които трябва да бъдат репликирани в различна работна книга в различен ред на реда, тогава, ако използвам функцията vlookup в рамките на vba, ще трябва да стартирам този макрос няколко пъти, за да постигна резултата. Това, което предложихте, ще върне препратка към една колона. Във вашия пример, колона 2. Ще ми трябва това да се завърти 400 пъти, за да получа 400-те колони с данни. Макросът за копиране и поставяне не е ли по-ефективен? Благодаря отново - person Jiggler; 28.01.2018
comment
Би било полезно, ако редактирате въпроса си, за да споделите пример за това, което имате и как трябва да изглежда като когато свърши. Има някои съвети в пример за минимално възпроизвеждане, а също така вижте Писане на перфектния въпрос (Това е човекът, който току-що получи милион повторения, след като отговори на 34 000 въпроса, значи знае какво говори... :-) - person ashleedawg; 28.01.2018