Forum Discussion
Copy the row until it reaches "Waiting for data"
I have two columns both using a Index Match formula:
| =IF(N3>0,INDEX($F$1:$I$1570,MATCH(N3,$F$1:$F$1570,0),2),"Waiting for data...") |
The information in the 1st column shown are codes scanned into the cell.
The information populated into the 2nd and 3rd columns are formulas (index and matched) found using column 1.
Is there a macro way to select and copy down the row until it reaches "Waiting for data" then pasting those values into another sheet?
| 753759143350 | 010-01534-11 | Montana 680T Handheld Gps |
| 753759209384 | 010-01771-00 | Approach Z80 - Golf Laser Range Finder With Gps - (010-01771-00) |
| Waiting for data... | Waiting for data... | |
| Waiting for data... | Waiting for data... |
Sub copy() Dim i, j As Long i = Application.WorksheetFunction.Match("Waiting for data...", Range("O:O"), 0) Range(Cells(3, 15), Cells(i - 1, 16)).copy Sheets("Tabelle2").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End SubYou can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.
4 Replies
=FILTER(N3:O7,O3:O7<>"Waiting for data...")An alternative could be FILTER if i correctly understand what you want to do.
I have two columns both using a Index Match formula:
=IF(N3>0,INDEX($F$1:$I$1570,MATCH(N3,$F$1:$F$1570,0),2),"Waiting for data...")
The information in the 1st column IS N3 shown are codes scanned into the cell.
The information populated into the 2nd and 3rd columns are formulas (index and matched) found using column 1 N3. So once a value is scanned into column 1/N3 the index and match dynamically populate columns 2 and 3.
I need to find a way to copy the values - NOT THE FORMULAS - from column 2 until it finds a row that has no value to match in the index: "Waiting for data" then paste all those rows/values into another sheet.Sub copy() Dim i, j As Long i = Application.WorksheetFunction.Match("Waiting for data...", Range("O:O"), 0) Range(Cells(3, 15), Cells(i - 1, 16)).copy Sheets("Tabelle2").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End SubYou can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.
