使用UserForm中的数据将值从一张纸复制到另一张纸 [英] Copy values from one sheet to another using Data from UserForm

问题描述

我有一个用户窗体，其中包含您可以填写的以下值:

I have a Userform which contains the following values that you can fill in:

TextBoxLopnummer.Value
TextBoxFragestallare.Value
TextBoxMottagare.Value
TextBoxDatum.Value

图片:

当有人填写日期值时:TextBoxDatum.Value我想在整个工作簿中搜索该值，并将该单元格所在的整行粘贴到工作表"Arende中的Lägg"单元格A15中.请注意，此值可以在工作簿的不同工作表中显示，并且可以在同一工作表中多次出现.因此，在单元格A15及以下的单元格中，可能会有很多行.

When somebody fills in the date value : TextBoxDatum.Value I want to search this value in the whole workbook and paste the whole row in which that cell is, inside of Sheet "Lägg in Ärende" cell A15. Note that this value can be in diffrent sheets in the workbook and appear several times inside of the same sheet. So in Cell A15 and below there can be alot of rows.

我已经开始实现这一点了，但老实说，我对如何完成它一无所知:

I have started implementing this a bit but I honestly dont have any idea on how to finish it:

'in the rows below I wanna write so that ".Value=copies the value from the sheets where it finds eg. the date".
emptyRow = WorksheetFunction.CountA(ws.Range("A:A")) + 14
Cells(emptyRow, 1).Value =
Cells(emptyRow, 2).Value =
Cells(emptyRow, 3).Value =
Cells(emptyRow, 4).Value =
Cells(emptyRow, 5).Value =
Cells(emptyRow, 6).Value =
Cells(emptyRow, 7).Value =
Cells(emptyRow, 8).Value =

请注意，您可以同时搜索多个日期，可以搜索4个条件，请参见上图.当您填写两个条件时，代码应将这两个条件与整个工作簿中具有相同条件的行匹配，然后复制该行，等等.

Note that you can search for more then the date at the same time, there are 4 criterias that you can search on, see the picture above. When you fill in 2 criterias then the code should match these two with the row in the entire workbook that has the same criterias and copy that row, etc.

TextBoxLopnummer还将始终位于A2单元格中，并一直向下位于要搜索其的工作表中.B2单元格中的TextBoxFragestallare，C2单元格中的TextBoxMottagare，D2单元格中的TextBoxDatum.

Also TextBoxLopnummer will always be in cell A2 and downwards in the sheets where its being searched in. TextBoxFragestallare in cell B2, TextBoxMottagare in cell C2, TextBoxDatum in cell D2.

如何继续解决我的问题?

How can I continue with solving my issue?

推荐答案

这将为您提供大部分尝试方法.根据您对原始问题的评论，我相信这就是您所需要的.

This is going to get you most of the way to what you are trying to do. Based on the comments in your original question I believe this is what you need.

过程:

• 在UserForm代码上有一个搜索按钮的单击事件.在示例中，它是Button1.根据您自己的需要将其命名.

• Have a click event for the search button on the UserForm code. In the example, it's Button1. Name it according to your own needs.

在每次运行之前(根据请求)清除目标工作表

Clear the target sheet before each run (per requests)

从文本框值中设置一个数组，其中每个值的索引与要搜索的列号匹配

Set an array from the textbox values where the index of each value matches the column number to search

遍历每个工作表，但目标表除外.

Loop through each worksheet, except the target sheet.

每次一行，将相应列的值与匹配它的数组索引进行比较.

One row at a time, compare the value of the appropriate column to the array index that matches it.

如果找到匹配项，则将"match"变量设置为true

If a match is found, the "match" variable is set to true

遍历数组中其余的TextBoxes值，如果其中任何一个都不匹配，则将"match"变量设置为false，并以失败的方式中断Textboxes上的循环.

Loop through the rest of the TextBoxes values from the array, if ANY of them don't match, the "match" variable is set to false, and break the loop over the Textboxes as a fail.

如果到搜索工作表的ROW循环结束时匹配"为true，则第1列至第8列将遍历，将值从搜索到的工作表设置为目标工作表.

If "match" is true by the end of the loop through the ROW of the Searched worksheet, columns 1 to 8 get looped through, setting the values from the searched sheet to the target Sheet.

下一行完成循环

下一个工作表完成循环

可能要检查的问题:

• 您可能需要对日期进行一些转换，但是如果工作表上的日期与用户表单上的日期具有相同的格式，则应该可以.

• You might have to do some conversions of dates, but if the dates on the sheets are in the same format as the dates on the user form, it should work.

如果工作表中的文本具有0.0或不同的小数位数，数字可能会提供类似的问题.

Numbers might provide similar issues if the text in the sheet has a 0.0 or varying decimal places.

如果发生任何此类问题，只需使用本地窗口"并逐步执行代码以查看其执行.类似的错误可能是类型不匹配.通过使用本地"窗口进行调试，您将知道需要格式化哪些特定值才能将其与文本框进行比较.如果单步执行时间过长，请设置一个断点.

If any issues like this occur, just use your Locals Window and step through your code to see it execute. The likely error you'd receive for something like that would be a type mismatch. By debugging with the Locals window, you will know which specific values need formatted in order to compare them with the text box. Put a break point if stepping through is too long.

未经测试:有问题的评论.

Private Sub button1_click()

Dim ws As Worksheet
Dim lastRow As Long, lRow As Long, tRow As Long
Dim tempValue As String
Dim targetSheet As String
Dim tempList(1 To 4) As String
Dim i As Long
Dim match As Boolean

match = False

'Set TargetSheet and clear the previous contents
targetSheet = "Lägg in Ärende"
tRow = 15
lastRow = Sheets(targetSheet).Range("A" & Rows.count).End(xlUp).row
Sheets(targetSheet).Range("A15:H" & lastRow).ClearContents

'Set an array of strings, based on the index matching the column to search for each
tempList(1) = TextBoxLopnummer.Text       'Column "A" (1)
tempList(2) = TextBoxFragestallare.Text   'Column "B" (2)
tempList(3) = TextBoxMottagare.Text       'Column "C" (3)
tempList(4) = TextBoxDatum.Text           'Column "D" (4)

    'Search through each worksheet
    For Each ws In Worksheets
        If ws.name <> targetSheet Then
            'Get last row of sheet
            lastRow = ws.Range("A" & Rows.count).End(xlUp).row

            'Search through the sheet
            For lRow = 2 To lastRow
                'Using the array of values from the TextBoxes,
                'Each column number matches the index of the array.
                'Only testing the array values that have text in them,
                'If any don't match the loop is broken and returns to main search.
                For i = 1 To 4
                    If tempList(i) <> "" Then
                        If ws.Cells(lRow, i).Text = tempList(i) Then
                            match = True
                        Else
                            match = False
                            Exit For        'If any of the values is false, exit i loop
                        End If
                    End If
                Next i

                'If there was a match, copy the data from Searched ws to targetSheet
                If match = True Then
                    'Get the first Empty row on target sheet
                    For lCol = 1 To 8
                        Sheets(targetSheet).Cells(tRow, lCol).Value = ws.Cells(lRow, lCol).Value
                    Next lCol
                    tRow = tRow + 1
                End If
            Next lRow
        End If
    Next ws

End Sub

这篇关于使用UserForm中的数据将值从一张纸复制到另一张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！