Unable to correctly access cells in a table

Question

I am trying to retrieve the last (highest row number) set of values in a table and put them into a report sheet. However, when I watch the variables, all of the values that I access using .Offset() come up empty. Any help is appreciated.

Sub Create_Report()
Dim cel As Range
Dim celTable As Range
Dim selectedRange As Range
Dim selectedRangeTable As Range
Set selectedRange = Range("B6:B17")
Set selectedRangeTable = Range("A:A")

Sheets("Report").Select
For Each cel In selectedRange.Cells
    WellName = cel.Text
    Sheets(WellName & " Table").Select
    For Each celTable In selectedRangeTable.Cells
        If IsEmpty(celTable.Offset(1, 0)) Then
            IPDate = Range("AF2")
            DaysOnline = Date - IPDate
            NRI = Range("AD2")
            Bench = Range("AG2")
            NBOED = celTable.Offset(0, 11)
            BOPD = celTable.Offset(0, 14)
            MCFD = celTable.Offset(0, 12)
            BWPD = celTable.Offset(0, 16)
            CurrentTubing = celTable.Offset(0, 21)
            LastTest = celTable.Offset(0, 2)
            Exit For
        End If
    Next celTable

    cel.Offset(0, 1) = IPDate
    cel.Offset(0, 2) = DaysOnline
    cel.Offset(0, 3) = NRI
    cel.Offset(0, 4) = Bench
    cel.Offset(0, 5) = NBOED
    cel.Offset(0, 6) = BOPD
    cel.Offset(0, 7) = MCFD
    cel.Offset(0, 8) = CurrentTubing
    cel.Offset(0, 9) = LastTest
    If IsEmpty(cel.Offset(1, 0)) Then Exit For
Next cel

End Sub

You really need to read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Which sheet is this related to `Set selectedRangeTable = Range("A:A")`? — SJR, Feb 22 '19 at 13:10

score 0 · Accepted Answer · edited Feb 22 '19 at 15:41

0

Some tips which may help you:

Do not use .Select or .Activate
~~If you use IsEmpty do not forget to use =True/False~~
When you use range in a specific sheet use With statement
When you set a range import it in a With statement if you want to avoid repetitions or clearly declare sheet name
Declare all your variables.
Use Option Explicit on the top
If you want to assign a value to a variable use .Value
If you are not sure if you select the correct range use immediate window to double check - rngname.select
Lastly is good practice to declare both workbook & worksheet

edited Feb 22 '19 at 15:41

Tom

9,725
3
31
48

answered Feb 22 '19 at 13:14

Error 1004

7,877
3
23
46

4

Point 2: why = True/False? It generates a boolean that doesn't need a comparison. – QHarr Feb 22 '19 at 13:21
@QHarr using True/False the results is a Logical Boolean. In my opinion is better to used. Your suggestion? – Error 1004 Feb 22 '19 at 13:27
1

You don’t need the =True/false. Simply use the return value as it will equate to the same thing – QHarr Feb 22 '19 at 13:29
1

@Error1004 What you suggest would be the same like `If True = True Then` because the first `True` here would be the result of `IsEmpty`. And `True = True` would result in `True` so `If True Then` is enough, which means `If IsEmpty(…) Then` is enough because it is either `True` or `False`. – Pᴇʜ Feb 22 '19 at 13:31
Thanks o lot for you clarification QHarr & Pᴇʜ. i ll edit answer right now. – Error 1004 Feb 22 '19 at 13:37
These comments are now really confusing with the updated edit. Have updated so it is clearer – Tom Feb 22 '19 at 15:40

Unable to correctly access cells in a table

1 Answers1