WildBurrow
New Member
- Joined
- Apr 5, 2021
- Messages
- 41
- Office Version
- 365
- Platform
- Windows
I've found code that will copy select data from (Sheet1) to another (Sheet2) ; using only values found in columns "B:E" and "I:K".
Columns "I:K" will always have data on each row pasted on to Sheet 2.
Columns "B:E" may not have data on each row.
Code from Sheet1:
Screen Shot from Sheet2:
Now working on Sheet2:
Column G will always have data and I want to use this as my LastRow identifier.
I need to determine if any ONE cell in each of the columns "C:F" contain the value "Yes", then run the next bit of code.
So in the above example, I would want to find out if any one cell in the range "C23:C32" contained "Yes", then do the same for columns D, E, and F.
Each column will trigger it's own event.
I found code that would return a Boolean value for Columns "C:F" but it isn't based upon LastRow of column "G".
How can I write code that will;
1) Identify LastRow of column "G" and
2) possibly Offset(?) to get a Boolean return for each column "C:F"?
Vikki
Columns "I:K" will always have data on each row pasted on to Sheet 2.
Columns "B:E" may not have data on each row.
Code from Sheet1:
VBA Code:
Sheets("Sheet1").Activate 'source
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "H").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = 16 'Sheet2 top row of data
For i = 19 To Lastrow 'Sheet1 top row of data
If Cells(i, "H").Value = range("L3").Value Then ' Value used to filter results
Application.Union(Cells(i, "B"), Cells(i, "C"), Cells(i, "D"), Cells(i, "E"), Cells(i, "I"), Cells(i, "J"), Cells(i, "K")).Copy
Sheets("Sheet2").range("C" & Lastrowa).PasteSpecial
Sheets("Sheet2").range("C" & Lastrowa, "H" & Lastrowa).Interior.Color = xlNone
Sheets("Sheet2").range("C" & Lastrowa, "F" & Lastrowa).Font.Color = RGB(0, 0, 0)
Lastrowa = Lastrowa + 1
End If
Next
'https://www.mrexcel.com/board/threads/copy-certain-columns-to-another-worksheet-based-on-criteria-of-another-column.1031722/
Screen Shot from Sheet2:
Now working on Sheet2:
Column G will always have data and I want to use this as my LastRow identifier.
I need to determine if any ONE cell in each of the columns "C:F" contain the value "Yes", then run the next bit of code.
So in the above example, I would want to find out if any one cell in the range "C23:C32" contained "Yes", then do the same for columns D, E, and F.
Each column will trigger it's own event.
I found code that would return a Boolean value for Columns "C:F" but it isn't based upon LastRow of column "G".
VBA Code:
Dim found As Boolean, cell As range
found = False
'Find "Yes" in Specialty Zones
For Each cell In range("C23:F52").Cells
If cell.Value = "Yes" Then
found = True
End If
Next
If found = True Then
MsgBox "Positive result"
Else
MsgBox "Negative result"
End If
End sub
'https://stackoverflow.com/questions/40100321/excel-vba-if-range-value-something-then
How can I write code that will;
1) Identify LastRow of column "G" and
2) possibly Offset(?) to get a Boolean return for each column "C:F"?
Vikki