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

 

[shewendavelaar]

The numbers are larger than 100 million, I tried changing the data type into Long but that didn't work

 

[footoo]

The numbers are larger than 100 million, I tried changing the data type into Long but that didn't work

They must be formatted as text.
Try changing to Dim b as string :

Private Sub CommandButton1_Click()
Dim b$, i%, c As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        b = .Cells(i, 7).Value
        Set c = Sheets("Sheet2").Range("a1:a273").Find(b, lookat:=xlWhole)
        If Not c Is Nothing Then
            Sheets("Sheet2").Cells(c.Row, 2).Resize(, 3).Copy .Cells(i, 12)
        Else
            MsgBox "klantnummer " & b & " is niet gevonden"
        End If
    Next
End With
End Sub

 

[shewendavelaar]

I tried changing the datatypes but that didn't work as well . The code does recognize i but the value still doesn't get stored in b.

 

[footoo]

It works for me. I don't know what else to try.
Are you sure the numbers are in column G of Sheet1 ?

 

[shewendavelaar]

It's working now, thanks