Paste data from array - question

[jonas-martens]

Hi,

I'm placing data from an input sheet in an array.
I paste 3 columns from this array on another sheet:
Column 1,2,3 and I use the 6th column to place a figure.

Only disadvantage is that there is already data on the 'paste' sheet in column 4 and 5.

Macro and example attached.
thx

[a2b1c1d1e1f1]

so ... where is this question and what does it concern ?

[jonas-martens]

so ... where is this question and what does it concern ?

Sorry if I was unclear…
If I read the case, I understand your confusion 🙈

I’ll try to explain:
Example:
I have a 6 column ‘temp’ array which I paste the 6th column in a range (column H).
But if I do this the data in Column F on my sheet is gone.

More info in the excel attached

[jolivanes]

You have an "Input" sheet with data in Columns A to K except Column I which is a blank Column
You have a "5km | Djun" sheet with Columns A to T that need data except Columns E and R that are blank Columns
Which Columns of your "Input" sheet do you want copied to your "5km | Djun" and into which Column should that be pasted?
As an example: Column A to Column A, Column C to Column B, Column F to Column D etc etc.

[TMS]

I'm placing data from an input sheet in an array.

I would populate two arrays. One with columns 1 to 3, and the second with column 6. Then paste them separately where you want them without overwriting columns 4 and 5.

[jolivanes]

Further to Post #4.

Sub Misschien()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long
Dim arrInput, arrInputCol, attOutputCol
Set sh1 = Worksheets("Input")
Set sh2 = Worksheets("5km | Djun")
arrInput = sh1.Range("A2:K" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Value
arrInputCol = Array(1, 3, 4, 6, 7)
arrOutputCol = Array(1, 2, 3, 4, 6)
    With sh2
        For i = LBound(arrOutputCol) To UBound(arrOutputCol)
            .Cells(23, arrOutputCol(i)).Resize(UBound(arrInput, 1)) = Application.Index(arrInput, , arrInputCol(i))    '<---- 23 is the starting row
        Next i
    End With
End Sub

In your attachment, it looks like Column F in "5km | Djun" has the data from Column A in "Input".
If so, change the 7 in arrInputCol to a 1.

You can add, delete and/or change the numbers in arrInputCol and in arrOutputCol as needed.
Make sure that both have the same amount of entries.
The numbers in arrInputCol are the Column numbers from "Input" and the numbers in arrOutputCol are the Column numbers from "5km | Djun".
As it stands now, data from Column1 goes into Column 1, Column3 goes into Column 2, Column 4 into Column 3, Column 6 into Column 4 and Column 7 into Column 6.

[jonas-martens]

Hi jolivanes,

This almost does the trick.
One question:

The data pasted in the 6th column on the "5km | Djun" sheet is no data which is present in the "Input" sheet.
It's just that the first person receives 500 points, the second 499, third 498 ...

How can I implement this in the code?
thx

[Sintek]

May I ask why you are searching for Name when you are clearing everything beforehand?

Set foundVal = Sheets("5km | Djun").Range("B:B").Find(Data(u, 3), LookIn:=xlValues, Lookat:=xlWhole)

And why are you looping the Data when you are just overwriting everything anyway...

[jonas-martens]

Hi,

Just a simple version of my actual code to highlight my problem.
I’m copying data from one sheet to another.

If the name is already there, give appropriate points.
If he’s not there, copy name and give points.

[jolivanes]

Probeer dit eens. Try this.

Sub Misschien()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, j As Long, x As Long
Dim arrInput, arrInputCol, attOutputCol
Set sh1 = Worksheets("Input")
Set sh2 = Worksheets("5km | Djun")
arrInput = sh1.Range("A2:G" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Value
arrInputCol = Array(1, 3, 4, 6, 7)
arrOutputCol = Array(1, 2, 3, 4, 6)
x = 501

    For j = LBound(arrInput) To UBound(arrInput)
        arrInput(j, 7) = x - j
    Next j

    With sh2
        For i = LBound(arrOutputCol) To UBound(arrOutputCol)
            .Cells(23, arrOutputCol(i)).Resize(UBound(arrInput, 1)) = Application.Index(arrInput, , arrInputCol(i))
        Next i
    End With
End Sub

[Sintek]

This dos what your initial code does excluding the clear of column F

Sub J3v16()
Dim Data, lr As Long
With Sheets("Input").Cells(1).CurrentRegion
    Data = Application.Index(.Value, Application.Evaluate("row(2:" & .Rows.Count & ")"), Array(3, 4, 6))
End With
With Sheets("5km | Djun")
    lr = .Cells(.Rows.Count, 3).End(xlUp).Row: If lr = 1 Then lr = 23
    Union(.Range("B23:D" & lr), .Range("G23:G" & lr)).ClearContents
    .Range("B23").Resize(UBound(Data), 3) = Data
    With .Range("G23").Resize(UBound(Data))
        .Value = Evaluate("ROW(" & .Address & ")-523")
        .Value = Evaluate(.Address & "*-1")
    End With
End With
End Sub

If Input sheet is continuously updating then why not make use of Sheet event Code on completion of entry...No need to loop everything...
So as is...why is your code clearing the entries on 5km sheet...Should that not be the case...
If it is and the data must remain...what is the numbering sequence then if exists and if new data is added...

Perhaps you should upload a sample file with existing data and result and then new data and expected result...