Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array to read data from one sheet and write to another if it meets criteria
Newb to VBA and need some assistance. I have data in one worksheet that
I need to write to another if it meets certain criteria. What I have runs but it places the data in the matching cell. For example cell A35 meets criteria so it, along with data from cell BD35 and cell BQ35 are placed in cell A35, B35, C35 in the new sheet but I'd like it to be place in the next empty cell. Here's what I have.... Dim i As Integer Dim intRowCount As Integer intRowCount = Range("'qry CR data dump.xls'!A2").CurrentRegion.Rows.Count For i = 2 To intRowCount If Left(Workbooks("data.xls").Worksheets("dump").Cell s(i, "A").Value, 3) = "CR-" Then Cells(i, "A").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "A").Value, 4) Cells(i, "B").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "BD").Value, 4) Cells(i, "C").Offset(4, 0).Value = Workbooks("data.xls").Worksheets("dump").Cells(i, "BQ").Value ActiveCell.Offset(1, 0).Select Else: End If Next i Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array to read data from one sheet and write to another if it meets criteria
You mean something like this.
Wasn't sure of the source of the data, as you seem to have 2; "qry CR data dump.xls" and "data.xls". Private Sub CommandButton2_Click() Dim SourceWB As Workbook Dim i As Integer Dim intRowCount As Integer Set SourceWB = Workbooks("data.xls") intRowCount = 4 'start value ? As you had Cells(i, "A").Offset(4, 0) With SourceWB.Worksheets("dump") For i = 2 To .Range("A2").CurrentRegion.Rows.Count If Left(.Cells(i, "A").Value, 3) = "CR-" Then intRowCount = intRowCount + 1 Cells(intRowCount, "A").Value = Right(.Cells(i, "A").Value, 4) Cells(intRowCount, "B").Value = Right(.Cells(i, "BD").Value, 4) Cells(intRowCount, "C").Value = .Cells(i, "BQ").Value End If Next i End With End Sub NickHK "RudyShoe" wrote in message oups.com... Newb to VBA and need some assistance. I have data in one worksheet that I need to write to another if it meets certain criteria. What I have runs but it places the data in the matching cell. For example cell A35 meets criteria so it, along with data from cell BD35 and cell BQ35 are placed in cell A35, B35, C35 in the new sheet but I'd like it to be place in the next empty cell. Here's what I have.... Dim i As Integer Dim intRowCount As Integer intRowCount = Range("'qry CR data dump.xls'!A2").CurrentRegion.Rows.Count For i = 2 To intRowCount If Left(Workbooks("data.xls").Worksheets("dump").Cell s(i, "A").Value, 3) = "CR-" Then Cells(i, "A").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "A").Value, 4) Cells(i, "B").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "BD").Value, 4) Cells(i, "C").Offset(4, 0).Value = Workbooks("data.xls").Worksheets("dump").Cells(i, "BQ").Value ActiveCell.Offset(1, 0).Select Else: End If Next i Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array to read data from one sheet and write to another if it meets criteria
Perfect, that did the trick! Many thanks! Sorry for the confusion on
the source data, my error. One other question- can this be written so that code executes faster? NickHK wrote: You mean something like this. Wasn't sure of the source of the data, as you seem to have 2; "qry CR data dump.xls" and "data.xls". Private Sub CommandButton2_Click() Dim SourceWB As Workbook Dim i As Integer Dim intRowCount As Integer Set SourceWB = Workbooks("data.xls") intRowCount = 4 'start value ? As you had Cells(i, "A").Offset(4, 0) With SourceWB.Worksheets("dump") For i = 2 To .Range("A2").CurrentRegion.Rows.Count If Left(.Cells(i, "A").Value, 3) = "CR-" Then intRowCount = intRowCount + 1 Cells(intRowCount, "A").Value = Right(.Cells(i, "A").Value, 4) Cells(intRowCount, "B").Value = Right(.Cells(i, "BD").Value, 4) Cells(intRowCount, "C").Value = .Cells(i, "BQ").Value End If Next i End With End Sub NickHK "RudyShoe" wrote in message oups.com... Newb to VBA and need some assistance. I have data in one worksheet that I need to write to another if it meets certain criteria. What I have runs but it places the data in the matching cell. For example cell A35 meets criteria so it, along with data from cell BD35 and cell BQ35 are placed in cell A35, B35, C35 in the new sheet but I'd like it to be place in the next empty cell. Here's what I have.... Dim i As Integer Dim intRowCount As Integer intRowCount = Range("'qry CR data dump.xls'!A2").CurrentRegion.Rows.Count For i = 2 To intRowCount If Left(Workbooks("data.xls").Worksheets("dump").Cell s(i, "A").Value, 3) = "CR-" Then Cells(i, "A").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "A").Value, 4) Cells(i, "B").Offset(4, 0).Value = Right(Workbooks("data.xls").Worksheets("dump").Cel ls(i, "BD").Value, 4) Cells(i, "C").Offset(4, 0).Value = Workbooks("data.xls").Worksheets("dump").Cells(i, "BQ").Value ActiveCell.Offset(1, 0).Select Else: End If Next i Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
SUM data if meets criteria | Charts and Charting in Excel | |||
copy row to new sheet where cell value meets criteria | Excel Discussion (Misc queries) | |||
Show only data that meets a certain criteria | Excel Discussion (Misc queries) | |||
In an array, I need to find the row # that meets 2 criteria | Excel Worksheet Functions |