mikey33
09-17-2009, 09:27 AM
I am trying to ref on sheet and copy an adjacent range of cells to another sheet using an IF statement and some For statements to step through the sheet. I believe I stated everything correctly but I keep getting a 1004 error code. I have attached my test sheet and code. Also, I would like it to compare two IF statements but that is another task to figure out. Any ideas would be appreciated.
Sub IDQ_To_Tekla2()
'Index information back to transfer sheet from Report sheet
Dim j As Integer
Dim i As Integer
Dim jj As Integer
Dim IDQ_row As Integer
Dim TR_row As Integer
Dim LR As Long
Dim LR_TR1 As Long
LR = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).row
LR_TR1 = Sheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).row
'LR_TR2 = Sheets("Sheet1").Cells(Rows.Count, 24).End(xlUp).row
'LR_TR3 = Sheets("Sheet1").Cells(Rows.Count, 36).End(xlUp).row
IDQ_row = 1 'IDQ row on report page
TR_row = 1 'IDW row on sheet page
i = 1 'row index
j = 1 'col index
jj = 1 'TR col index
For IDQ_row = 9 To LR
IDQ_cell = Sheets("Report").Cells(IDQ_row, 1).Select
Q_cell = Sheets("Report").Cells(IDQ_row, 7).Select
Report_dat = Sheets("Report").Range("H" & IDQ_row & ":" & "L" & IDQ_row)
For TR_row = 9 To LR_TR1
For jj = 1 To 4
j = 12 * (jj - 1) + 1
TR_IDQ_cell = Sheets("Sheet1").Cells(TR_row, j).Select
TR_Q_cell = Sheets("Sheet1").Cells(TR_row, j + 6).Select
If IDQ_cell = TR_IDQ_cell Then Report_dat.Copy
'ElseIf Q_cell = TR_Q_cell Then Report_dat.Copy 'It doesn't like this line Matt !!!!
Sheets("Sheet1").Select
Range(Cells(TR_row, j + 7).Address).Select
ActiveSheet.Paste
Next jj
Next TR_row
Next IDQ_row
End Sub
Thanks for the help!!!
Mike
Sub IDQ_To_Tekla2()
'Index information back to transfer sheet from Report sheet
Dim j As Integer
Dim i As Integer
Dim jj As Integer
Dim IDQ_row As Integer
Dim TR_row As Integer
Dim LR As Long
Dim LR_TR1 As Long
LR = Sheets("Report").Cells(Rows.Count, 1).End(xlUp).row
LR_TR1 = Sheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).row
'LR_TR2 = Sheets("Sheet1").Cells(Rows.Count, 24).End(xlUp).row
'LR_TR3 = Sheets("Sheet1").Cells(Rows.Count, 36).End(xlUp).row
IDQ_row = 1 'IDQ row on report page
TR_row = 1 'IDW row on sheet page
i = 1 'row index
j = 1 'col index
jj = 1 'TR col index
For IDQ_row = 9 To LR
IDQ_cell = Sheets("Report").Cells(IDQ_row, 1).Select
Q_cell = Sheets("Report").Cells(IDQ_row, 7).Select
Report_dat = Sheets("Report").Range("H" & IDQ_row & ":" & "L" & IDQ_row)
For TR_row = 9 To LR_TR1
For jj = 1 To 4
j = 12 * (jj - 1) + 1
TR_IDQ_cell = Sheets("Sheet1").Cells(TR_row, j).Select
TR_Q_cell = Sheets("Sheet1").Cells(TR_row, j + 6).Select
If IDQ_cell = TR_IDQ_cell Then Report_dat.Copy
'ElseIf Q_cell = TR_Q_cell Then Report_dat.Copy 'It doesn't like this line Matt !!!!
Sheets("Sheet1").Select
Range(Cells(TR_row, j + 7).Address).Select
ActiveSheet.Paste
Next jj
Next TR_row
Next IDQ_row
End Sub
Thanks for the help!!!
Mike