selecting variable data form one sheet and pasting it in another

[shewendavelaar]

I'm new to VBA (and coding in general. I've been trying to copy a set of data (client data) from one sheet and pasting it in a specific row. And I want this to happen when the client number equals the client number on the first sheet.

This is the code that I've written so far. Can someone help me debug it?

Private Sub CommandButton1_Click()
Dim a As Integer
Dim b As Integer
Dim cl As Integer
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a
b = Worksheets("Sheet1").Cells(i, 7).Value
Worksheets("sheet2").Activate
With Worksheets("sheet2").Range("a1:a273")
Set c = .Find(b, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "klantnummer is niet gevonden"
Do
cl = ActiveCell.Column
Worksheets("sheet2").Range(.Cells(cl, 2), .Cells(cl, 5)).Copy Worksheets("Sheet1").Range(.Cells(i, 12), .Cells(i, 14))
Loop While Not c Is Nothing
End If
End With
Next


End Sub

 

[footoo]

It is not clear from your description or the code what you are trying to do.
Describe with more detail.

If Not c Is Nothing Then
MsgBox "klantnummer is niet gevonden"

Presumably this should be : If c Is Nothing Then

The syntax for the Cells method is :
Cells(row number, column number) or Cells(row number, "column letter ")

 

[shewendavelaar]

What I'm trying to do is:

1. select number in sheet1
2. Check in sheet2 if number is present
3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present

 

[footoo]

What I'm trying to do is:

1. select number in sheet1
2. Check in sheet2 if number is present
3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present

1. select number in sheet1

In what cell (or cells) is the number?

2. Check in sheet2 if number is present

Present in A1:A273?
Can the number appear more than once? If yes, what should happen?

3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present

Copy from Sheet2 (columns B:D) and paste to Sheet1 (columns L:N) ?

 

[shewendavelaar]

1. the number is in cells (i (2), 7), meaning the row is a variable. The code has to be able to loop through a list of numbers.
2. yes the number is present in A1:A273 in sheet 2
3. The number can only appear once.

Thank you for your help .

 

[footoo]

Private Sub CommandButton1_Click()
Dim a As Integer, b As Integer, cl As Integer, c as Range
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
    b = Worksheets("Sheet1").Cells(i, 7).Value
    With Worksheets("sheet2")
        Set c = .Range("a1:a273").Find(b)
        If c Is Nothing Then
            MsgBox "klantnummer( " & b & " is niet gevonden"
            GoTo nxt
        End If
       .Cells(c.Row, 2).Resize(, 3).Copy Worksheets("Sheet1").Cells(i, 12)
    End With
nxt:
Next
End Sub

 

[shewendavelaar]

This doesn't solve the problem unfortunately, I think the value of cell(i, 7) doesn't get stored in b.

 

[footoo]

This doesn't solve the problem unfortunately, I think the value of cell(i, 7) doesn't get stored in b.

Step through the code via F8 (with the Locals window open in the VBE) to check what values are getting assigned.

 

[shewendavelaar]

There's an object defined-error where the value get's stored in b.

 

[footoo]

Are all the klantnummers integers (that is whole numbers no bigger than 32,767) ?