PDA

View Full Version : Copy and Paste loop



Pamella
08-03-2019, 04:13 AM
I'm trying to do a relatively simple copy and paste.

I am Copying 3 cell values, and also assigning a cell value to a string variable,
once all done go to Output sheet and paste strDer variable value to B2 and value of smatch to B3, B4, B5
Then loop to next row.

I need help with the pasting bit.






Dim smatch as range
Dim strDer as string

strDer = smatch.Offset(0, -1).Value
copval= ActiveCell.Resize(, 3).Copy

mana
08-03-2019, 05:29 AM
Sub test()
Dim r As Range
Dim m
Dim n As Long

n = 2
For Each r In Rows("11:20")
m = Application.Match("testing", r, 0)
If IsNumeric(m) Then
Cells(n, "B").Resize(, 3).Value = r.Cells(m).Resize(, 3).Value
n = n + 1
End If
Next

End Sub

Pamella
08-03-2019, 05:53 AM
my lngMyrow is dim as long. I'm trying to use like this
Range(lngMyRow).Resize(, 3).Value but getting error
and I need to assign to a variable so i can paste later.

mana
08-03-2019, 06:04 AM
Range("E" & lngMyRow).Resize(, 3).Value


Cells(lngMyRow, "E").Resize(, 3).Value

Pamella
08-03-2019, 06:09 AM
"E"? I dont know which column it will be

mana
08-03-2019, 06:34 AM
>"E"

It's just an example

mana
08-03-2019, 06:46 AM
Could you give me sample data?

Paul_Hossler
08-03-2019, 07:30 AM
I'm trying to do a relatively simple copy and paste.

I am Copying 3 cell values, and also assigning a cell value to a string variable,
once all done go to Output sheet and paste strDer variable value to B2 and value of smatch to B3, B4, B5
Then loop to next row.

I need help with the pasting bit.



Q: Do you want to keep on making the copies a long column in B, i.e. B2:B5 for first copy, B6:B9 for next, etc.?

Q: Where does smatch get a value

Q: How is the input data structured; A1:Z26 on Sheet1? Is Row 1 headers?

Sample workbook with the before and after would be helpful