0

First question so here goes.

I am trying to create a loop that will work it's way through rows in a table and assign the cell values for respective columns to declared variables. The plan is to use these variables in logic statements and calculations with named range input cells, then transfer the output to a worksheet where it will form the basis of a pivot table.

The legacy workbook template I am using had a heap of vlookups, if statements, etc with no named ranges; it is messy and someone has inserted columns/rows which have broken some of the formulas.

I was doing something similar with MSAccess queries and vba some years ago but have not done much vba and none since or in excel.

So far, it addition to the MSDN Reference I have found the following helpful https://stackoverflow.com/a/21490552 and https://stackoverflow.com/a/12497229/6598860 

'Declare variables
Dim Var1 As String    'To store Value from Table Column "Col1"
Dim Var2 As String    'To store Value from Table Column "Col2"
Dim Var3 As Variant    'To Store Value from Table Column "Date"
Dim SD As String    'To Store Value from Named Input Cell "StartDate"
Dim ED As String    'To Store Value from Named Input Cell "EndDate"
Dim counter As Integer    'Counter for pasting to row in worksheet "Dump"

'Set Date Range
SD = Range("StartDate").Value
ED = Range("EndDate").Value

'Set Counter
counter = 1

'Loop through rows in table "tblMyData"on Worksheet "Data" and
'assign values to variables (this bit is breaking down)
Dim lo As Excel.ListObject
Dim lr As Excel.ListRow
Set lo = Worksheets("Data").ListObjects("tblMyData")
For Each lr In lo.ListRows
    Var1 = Range(lr.Range, lo.ListColumns("Col1").Range).Value
    Var2 = Range(lr.Range, lo.ListColumns("Col2").Range).Value
    Var3 = Range(lr.Range, lo.ListColumns("Date").Range).Value

    'Logic Statement e.g.
    If SD > Var3 > ED Then
        Worksheets("Dump").Activate
        Cells(counter, 1).Value = Var1 * Var2
        Cells(counter, 2).Value = Var3
    Else

    End If
Next

I will be replacing the Variable Names and Column names with something more descriptive but hopefully this illustrates what I am trying to achieve. Any help or advice would be much appreciated. Thanks

Community
  • 1
  • 1
damnthing
  • 9
  • 1
  • 3
  • 1
    Please say what you mean by "breaking down." A compile or runtime error? Unexpected results? – Doug Glancy Jul 17 '16 at 02:13
  • I have had a compile error and unexpected results. With Var1 AS String I had Run-time error '13': Type mismatch and "Var1 = Range(lr.Range, lo.ListColumns("Col1").Range).Value" – damnthing Jul 17 '16 at 03:27
  • Sorry:I have had a compile error and unexpected results. With Var1 AS String I had Run-time error '13': Type mismatch and "Var1 = Range(lr.Range, lo.ListColumns("Col1").Range).Value" was highlighted during debug So I tried changing Var1 to a Variant, which resulted in the value from the first column heading of the table being pasted (repeatedly) in the destination worksheet. Despite the name in the code being "Col1" it is not actually the first column in the table that I am trying to return and I am looking for the value of each row of data to be passed to the variable Var1. – damnthing Jul 17 '16 at 03:36
  • Try changing `Range(lr.Range, lo.ListColumns("Col1").Range).Value` to `Intersect(lr.Range, lo.ListColumns("Col1").Range).Value`. I don't know if that's the issue, but that's how I'd reference the single cells. – Doug Glancy Jul 17 '16 at 03:46
  • Doug Glancy you are a champion.. Works right with Intersect. Do you want to post your edit in the answer section below so I can award points or something? – damnthing Jul 17 '16 at 04:37
  • Glad to hear it. I'll post it. – Doug Glancy Jul 17 '16 at 04:38

2 Answers2

2

Change

Range(lr.Range, lo.ListColumns("Col1").Range).Value

to

Intersect(lr.Range, lo.ListColumns("Col1").Range).Value.

That's how I'd reference the single cells at the intersection of a column and a row.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
0

I am not sure if this is the only error, but I would be replacing the bottom IF with the piece of code below:

'Logic Statement e.g.
If SD > Var3 And Var3 > ED Then
    With Worksheets("Dump")
        .Cells(counter, 1).Value = Var1 * Var2
        .Cells(counter, 2).Value = Var3
    End With
Else
    'if you are not doing anything with the Else you can just remove it
End If
Shai Rado
  • 33,032
  • 6
  • 29
  • 51