PDA

View Full Version : Simple and Basic VBA question.



mannygcg
05-21-2019, 07:22 PM
I want to now create a table (Table8) that basically filters through the other table (Table7) and copy certain cells of each row on this new table.
The code is working up to a certain extent. I can get the first cell of each row to be copied, however not the rest of the cells of the row.
I believe the problem is with ".Range(2).Value = Cells(cell.Row, 3).Value" and the other similar lines.



Sub Optimum()

Dim myTable1 As ListObject
Dim myArray1 As Variant
Dim myTable2 As ListObject
Dim myArray2 As Variant
Dim newrow As ListRow

Set myTable1 = Sheets("Mod 5 CR").ListObjects("Table8")

Set myTable2 = Sheets("Sheet5").ListObjects("Table7")
Set myArray1 = myTable2.ListColumns(1).Range

For Each cell In myArray1
If cell.Value >= Sheets("Mod 5 CR").Range("B20").Value And cell.Value <= Sheets("Mod 5 CR").Range("B18").Value Then
Set newrow = myTable1.ListRows.Add
With newrow
.Range(1).Value = cell.Value
.Range(2).Value = Cells(cell.Row, 3).Value
.Range(3).Value = Cells(cell.Row, 8).Value
.Range(4).Value = Cells(cell.Row, 23).Value
.Range(5).Value = Cells(cell.Row, 4).Value
End With
End If
Next cell
End Sub


Thanks guys!Edit: I tried a line "msgbox Cells(cell.Row, 3).Value" and the msgbox displayed the value I wanted!

大灰狼1976
05-21-2019, 10:48 PM
Hi mannygcg!
It seemed no problem. Please upload the attachment.

mannygcg
05-21-2019, 11:50 PM
Hi mannygcg!
It seemed no problem. Please upload the attachment.

But it does not work :/
What attachment? of the file?

大灰狼1976
05-22-2019, 12:19 AM
Yes, upload the workbook sample please.

p45cal
05-22-2019, 06:22 AM
A workbook would be helpful.
Maybe, because lines such as:
.Range(2).Value = Cells(cell.Row, 3).Value
have ranges without a sheet qualification the wrong cell values could be being copied. Currently the reference defaults to the active sheet cells (or, if the code is in a sheet's code-module, the cells on that sheet).
A guess:
.Range(2).Value = Sheets("Sheet5").Cells(cell.Row, 3).Value