VBA- Adding source and destination cells together rather than overwriting

[Renx10]

Hi,
I have muddled together this piece of VBA that copies a cell range from 1 sheet, then pastes it into another multiple times depending on the number in a different cell. the pastes run concurrently 1 column apart
The issue I have is that the paste is overwriting the data in destination cell rather than adding together.
can anyone see where its going wrong?

Sub CopyAndVlookupWithErrors()
On Error Resume Next

Dim wsSource As Worksheet
Dim wsDestination As Worksheet
Dim lookupRange As Range
Dim pasteColumn As Long
Dim i As Integer

' Set the source and destination worksheets
Set wsSource = ThisWorkbook.Sheets("search2")
Set wsDestination = ThisWorkbook.Sheets("Search2results")

' Copy the range from source to destination
wsSource.Range("D4:J29").Copy

' Set the lookup range and perform VLOOKUP
Set lookupRange = ThisWorkbook.Sheets("Sheet13").Range("A:B")
pasteColumn = Application.WorksheetFunction.Match(wsSource.Range("C1").Value, lookupRange.Columns(1), 0)

' Check for errors in the VLOOKUP result
If Err.Number = 0 Then
' Paste the copied range multiple times with 1 column apart
For i = 0 To ThisWorkbook.Sheets("search form").Range("G3").Value - 1
wsDestination.Cells(2, pasteColumn + i * 1).PasteSpecial Paste:=xlPasteValues
Next i
Else
MsgBox "VLOOKUP Error: Value not found."
End If

On Error GoTo 0
End Sub

Thanks

 

[My Aswer Is This]

Would you please tell us in words what you're attempting to do.
Trying to read all this code is hard to understand what you're attempting to do.
Please provide sheet names and copy what range and paste into what range.

 

[Renx10]

Sorry,
I am wanting to copy cells D4:J29 in "search2" and then paste them into "searchresults2" with the column being determined via a Vlookup
When the data is copied, G3 in "search form" determines how many times the copied data is pasted concurrently, 1 column apart. e.g if G3 = 5 it performs the paste 5 times.

As both the copied and destination contain numbers when the paste is performed I want the result to be a sum of the 2 numbers rather than just pasting over the current number, which is what the current VBA does

 

[My Aswer Is This]

1. You said: in "search2"
Does this mean a sheet named "search2"

2.You said: paste them into "searchresults2"
Does this mean a sheet named "searchresults2"
And other things are hard for me to understand

 

[Alex Blakenburg]

This part of the code doesn't make sense to me:

    ' Set the lookup range and perform VLOOKUP
    Set lookupRange = ThisWorkbook.Sheets("Sheet13").Range("A:B")
    pasteColumn = Application.WorksheetFunction.Match(wsSource.Range("C1").Value, lookupRange.Columns(1), 0)

It saying its a VLookup but although it sets the lookup range as 2 column A:B it is
a) Only doing a Match on column A so not returning a value from B
b) By using a Match it is only returning a row number in the sheet which for some reason is being used a column number for the output.

Accepting the above at face value and assuming you want to copy the output side by side see if these changes to your code do the trick:

Sub CopyAndVlookupWithErrors()
    On Error Resume Next
    
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lookupRange As Range
    Dim pasteColumn As Long
    Dim i As Integer
    Dim NoOfColumns As Long
    
    ' Set the source and destination worksheets
    Set wsSource = ThisWorkbook.Sheets("search2")
    Set wsDestination = ThisWorkbook.Sheets("Search2results")
    
    ' Copy the range from source to destination
    With wsSource.Range("D4:J29")
        NoOfColumns = .Columns.Count
        .Copy
    End With
    
    ' Set the lookup range and perform VLOOKUP
    Set lookupRange = ThisWorkbook.Sheets("Sheet13").Range("A:B")
    pasteColumn = Application.WorksheetFunction.Match(wsSource.Range("C1").Value, lookupRange.Columns(1), 0)
    
    ' Check for errors in the VLOOKUP result
    If Err.Number = 0 Then
        ' Paste the copied range multiple times with 1 column apart
        For i = 0 To ThisWorkbook.Sheets("search form").Range("G3").Value - 1
            wsDestination.Cells(2, pasteColumn + i * NoOfColumns).PasteSpecial Paste:=xlPasteValues
        Next i
    Else
        MsgBox "VLOOKUP Error: Value not found."
    End If
    
    On Error GoTo 0
End Sub