Iterative copy paste problem

[mikey33]

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

[mdmackillop]

Hi Mike
Welcome to VBAX

Rule 1. Always use Option Explicit

Try to avoid Select. Set a variable to the range and refer to this.

Try the following

Option Explicit
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 IDQ_cell As Range
Dim Q_cell As Range
Dim Report_dat As Range
Dim TR_IDQ_cell As Range
Dim TR_Q_cell As Range

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
Set IDQ_cell = Sheets("Report").Cells(IDQ_row, 1)
Set Q_cell = Sheets("Report").Cells(IDQ_row, 7)
Set 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
Set TR_IDQ_cell = Sheets("Sheet1").Cells(TR_row, j)
Set TR_Q_cell = Sheets("Sheet1").Cells(TR_row, j + 6)

If IDQ_cell = TR_IDQ_cell Then
Report_dat.Copy Sheets("Sheet1").Cells(TR_row, j + 7)
Else
If Q_cell = TR_Q_cell Then
Report_dat.Copy Sheets("Sheet1").Cells(TR_row, j + 7)
End If
End If
Next jj
Next TR_row
Next IDQ_row
End Sub

[mikey33]

So I am looking at the code and when I put the option explicit in before the Sub it deletes it. Then when I put it after it said "invalid inside procedure." As you can tell, I am new to .net and am trying to figure out the operation orders. Should I be defining my code as something other then Sub _(). Note that this is a standalone macro that will get put in my personal.xlsb file. Secondly, if the "IF Then Else" statement is used won't the code copy the cell if either statement is satisfied? I would need something like an IF and IF mechanism.

Thanks again! I really appreciate the help.

[mikey33]

Side note. I believe that the code is not comparing the cell's data. I was using .value after the _cell definitions but it is not working. Does the .value only allow integers?

[mdmackillop]

This seems overcomplicated. Can you explain in simple terms what the code should do?

[mikey33]

The code should match cell A9 and G9 on the Reports page to the same cells on sheet1 in columns A&G, M&S or Z&AE. Then once it indexs to the correct place it will copy the range H9:L9 on the Report worksheet to the corresponding spot on worksheet Sheet1. Then this should iterate through each row on the Report worksheet. So that is what I am trying to do. Thanks for the help again.

[mdmackillop]

Sub IDQ_To_Tekla2()
'Index information back to transfer sheet from Report sheet
Dim IDQ_cells As Range
Dim LR As Long
Dim LR_TR1 As Long
Dim FirstAddress As String
Dim cel As Range, c As Range
Dim SearchCols As Range

With Sheets("Sheet1")
Set SearchCols = Union(.Columns(1), .Columns(13), .Columns(25))
End With

With Sheets("Report")
Set IDQ_cells = Range(.Cells(9, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

For Each cel In IDQ_cells
FirstAddress = ""
With SearchCols
Set c = .Find(cel, LookIn:=xlValues)
If Not c Is Nothing Then
'c.Select
FirstAddress = c.Address
Do
If c.Offset(, 6).Value = cel.Offset(, 6).Value Then
c.Resize(, 6).Value = cel.Resize(, 6).Value
Exit Do
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
End Sub