0

I'll try and be as clear as possible. The nested loops is meant to go through the InTbl's amended column and look for find any cell that has a value in.

It's also then meant to loop through each row of the asset codes table through list column two (specifically for the asset code) and check it against the asset code in the input table (with row reference i)

Once it finds the row with the asset code matching reference i, it should post the value from the amended column into the cell offset from the cell in the Asset table.

The main issue I am getting is that none of the values are being copied over, I think there is something wrong in the piece of code

   For c = 1 To LastAss
         If AsTbl.DataBodyRange(c, 2).Value = InTbl.DataBodyRange(i, 4).Value Then
            AsTbl.DataBodyRange(c, 2).Offset(0, 6).Value = InTbl.DataBodyRange(1, 4).Value

         End If

      Next

But I can't actually tell which bit is wrong.

Anyways, please find my code below, is there any ideas? or should it be done differently?

Dim InTbl As ListObject, i As Long, LastRow As Long, AmendedCCY, AsTbl As ListObject, c As Long, AssCode As Range, LastAss As Long 'Defines all variables

Set InTbl = ThisWorkbook.Sheets("Input").Range("Input").ListObject ' Sets inTbl as the input table's data without headings
Set AmendedCCY = InTbl.ListColumns(4).DataBodyRange ' Sets variable as spcifically the amended column in the input table
Set AsTbl = ThisWorkbook.Sheets("Asset List").Range("AssListTab").ListObject 'Sets variable as the full asset table
Set AssCode = AsTbl.ListColumns(2).DataBodyRange
LastRow = AmendedCCY.Rows.Count 'Sets variable as the number of rows in the data range of the input table (or as the number of assets)
LastAss = AssCode.Rows.Count

    For i = 1 To LastRow 'For each iteration from one to the number of all rows.

        If Not IsEmpty(InTbl.DataBodyRange(i, 4).Value) Then 'If the cell in the amended column is not empty then
          For c = 1 To LastAss
             If AsTbl.DataBodyRange(c, 2).Value = InTbl.DataBodyRange(i, 4).Value Then
                AsTbl.DataBodyRange(c, 2).Offset(0, 6).Value = InTbl.DataBodyRange(1, 4).Value

             End If

          Next
        End If

    Next 'Go to next iteration


End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Breakingfaith
  • 63
  • 7
  • How many columns got `AsTbl`? Also, have you checked that `If AsTbl.DataBodyRange(c, 2).Value = InTbl.DataBodyRange(i, 4).Value Then` can be true? And about line `AsTbl.DataBodyRange(c, 2).Offset(0, 6).Value = InTbl.DataBodyRange(1, 4).Value`, you would be copying all the time the same value (not sure if that's right or not) – Foxfire And Burns And Burns Mar 03 '20 at 09:19
  • AsTbl has 74 columns The values - yeah you're right that's the error. It should have be (i,1)... However, now it only posts the value of the first iteration of (i,4) so you're last comment is right. I – Breakingfaith Mar 03 '20 at 09:32
  • 1
    So the error was just that? Nice. About speeding up code, check https://stackoverflow.com/a/49514930/9199828 – Foxfire And Burns And Burns Mar 03 '20 at 09:37
  • Actually you were right about the last point as well but I fixed that before the comment. And perfect, thank you I'll try that now – Breakingfaith Mar 03 '20 at 09:38
  • When you have this type of problems, best to proceed incrementally. For example, 1. read the value in one cell and check it on debugging or show it in a MsgBox (old-style debugging ;) ). Once this works: 2. add a "for" loop as you will have it in your big snipped, iterate over all cells in a range, and check that you can still read the values. Once this works: 3. Add comparison bit etc. Basically, divide and conquer! – Yulia V Jul 16 '20 at 12:11
  • Very impressed by your achievements BTW, good luck for the future! – Yulia V Jul 16 '20 at 12:16

0 Answers0