Using VBA If then statement to copy and paste data with TextBox.Values

Question

I am currently facing the following problem:

The source data currently looks as follows:

Value#1        Value#2
10             AA
11             AA
12             AB
13             AD
1231           AA
125            AB
4312           AA
12314          AA

Now the user has multiple userform textboxes where he can define where the respective values are:

Textbox1 = defines the beginning row of the values
Textbox2 = Column for Value#1
Textbox3 = Column for Value#2
Textbox4 = Criteria in Value#2

Now I want to achieve the following; the user should specify where his values#1 are (in TextBox2), then he should define the column in which the criteria can be found (Values#2 in TextBox3), and after that he should define what criteria should be filtered.

So if he chooses to type "AB" in Textbox4 then the following has to appear in the first available columns in the worksheet:

Value#1        Value#2
12             AB
125            AB

My current code looks something like this, but I'm constantly changing it and nothing really works (syntax-error). I'm actually sure that the beginning of the Syntax is kinda allright (?), but I've no idea how to express with vba that I want them to copy the value from the column into another one, if the criteria is matched. There are plenty of examples on here and at other places, but I couldn't find anything where the range or value isn't predefined.

Dim c as Range

    If OptionButton1.Value = True Then 'also this only happens if the user decides that an Optionbutton is true         
       For Each c In Sheets ("Table") Range (TextBox3.Value & TextBox1.Value + 1 & ":" & TextBox3.Value & lastrow)
             If cell.Value = TextBox4.Value Then
                    Range (TextBox2.Value & TextBox1.Value + 1 & ":" & TextBox2.Value & lastrow) c.copy c.Offset (, 1) 'syntax-error
             End If
        Next

    End If

I'm pretty new to VBA and programming as a whole and can't find a solution to this.

Through some research I'm pretty sure that the syntax to solve this problem is kinda like "For each "x"" etc. but I've never found something where the range und the value is defined with a TextBox.

"...and nothing really works." -- what happens, and why is it different to your expectations? Add this info in your post. — ACEG

Shai Rado Shai Rado · Accepted Answer · 2017-06-12T10:39:50

A few notes regarding your code:

First, you are looping with C in the line For Each c In Sheets ("Table")..., but the following line you are checking If cell.Value = TextBox4.Value, instead of If C.Value = TextBox4.Value.

Second, if you want to copy that cell in case it's equal to TextBox4.Value to the next column, use C.Copy C.Offset(, 1).

Try the code below:

Dim Rng As Range
Dim C As Range

If OptionButton1.Value = True Then 'also this only happens if the user decides that an Optionbutton is true

    ' set the range fisrt , See if you are getting the Error on this line -->
    Set Rng = Sheets("Table").Range(TextBox3.Value & TextBox1.Value + 1 & ":" & TextBox3.Value & lastrow)
    For Each C In Rng
        ' for DEBUG ONLY
        Debug.Print C.Value & " | TextBox4 value: " & TextBox4.Value
        If C.Value = TextBox4.Value Then
            MsgBox "There is a match" ' <-- for DEBUG ONLY
            C.Copy C.Offset(, 1)  ' copy the value from the cell into the next column to the right
        End If
    Next C
End If

Using VBA If then statement to copy and paste data with TextBox.Values

1 Answers