RioDegenero
New Member
- Joined
- Mar 1, 2023
- Messages
- 23
- Office Version
- 365
- 2021
- Platform
- Windows
- MacOS
You do not indicate where you want to paste those values.So in the snip below, it's the values in A5 and B8 I will like to copy
Sub copyvalues()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim f As Range
Dim i As Long
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
For i = 1 To sh1.Cells(1, Columns.Count).End(1).Column
Set f = sh1.Columns(i).Find("#N/A", , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
sh2.Range("A" & Rows.Count).End(3)(2).Value = f.Offset(-1).Value
Else
sh2.Range("A" & Rows.Count).End(3)(2).Value = sh1.Cells(Rows.Count, i).End(3).Value
End If
Next
End Sub
Dante Amor | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Set 1 | Set 2 | Set 3 | ||
2 | 2 | 5 | 4 | ||
3 | 3 | 6 | 8 | ||
4 | 4 | 7 | |||
5 | 5 | 8 | |||
6 | #N/A | 9 | |||
7 | #N/A | 10 | |||
8 | #N/A | 11 | |||
9 | #N/A | #N/A | |||
10 | #N/A | ||||
11 | #N/A | ||||
12 | #N/A | ||||
13 | |||||
Sheet1 |
Dante Amor | |||
---|---|---|---|
A | |||
1 | Results | ||
2 | 5 | ||
3 | 11 | ||
4 | 8 | ||
5 | |||
Sheet2 |
Thanks for thisYou do not indicate where you want to paste those values.
With the following macro you copy those values from Sheet1 to Sheet2 in column A, one value below another.
I added a condition, if #N/A does not exist then it copies the last value of that column.
VBA Code:Sub copyvalues() Dim sh1 As Worksheet, sh2 As Worksheet Dim f As Range Dim i As Long Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") For i = 1 To sh1.Cells(1, Columns.Count).End(1).Column Set f = sh1.Columns(i).Find("#N/A", , xlValues, xlWhole, , , False) If Not f Is Nothing Then sh2.Range("A" & Rows.Count).End(3)(2).Value = f.Offset(-1).Value Else sh2.Range("A" & Rows.Count).End(3)(2).Value = sh1.Cells(Rows.Count, i).End(3).Value End If Next End Sub
For example:
Dante Amor
A B C 1 Set 1 Set 2 Set 3 2 2 5 4 3 3 6 8 4 4 7 5 5 8 6 #N/A 9 7 #N/A 10 8 #N/A 11 9 #N/A #N/A 10 #N/A 11 #N/A 12 #N/A 13 Sheet1
Results:
Dante Amor
A 1 Results 2 5 3 11 4 8 5 Sheet2