0

The function is designed to take in input - variable pg - that is in a cell on the spreadsheet, go through the rows of data to see which row in a column 1 matches variable pg. Once the match is found, it then goes through the columns to see which of the columns has "VRP23" Or "VRP24" in the first row. When that is found, it takes the number of the matching row/column and performs the "step1" modification. The issue is that in the spreadsheet the error #VALUE! appears and I'm not sure why this is. 

Function getECONpgdimscore1(pg As String) As Double

Dim row As Integer
row = 2

Dim c As Integer
c = 1
Dim econ As Double
econ = 0

Dim x As Integer
Dim NumRows As Integer
NumRows = Range("A2", Range("A2").End(xlDown)).rows.count
Cells(row, 1).Select

For x = 1 To NumRows
    If Cells(row, 1).Value = pg Then
        Do While c < 48
            Cells(row, 7 + c).Select
            If Cells(1, 7 + c).Value = ("VRP23" Or "VRP24") Then
                econ = econ + step1(Cells(1, 7 + c), Cells(row, 7 + c))
            End If
            c = c + 1
        Loop
    End If
    row = row + 1
Next x


getECONpgdimscore1 = (econ / 100) * 2.5

End Function
Joshua
  • 40,822
  • 8
  • 72
  • 132
R. Shi
  • 1
  • 1
    There is no reason to use `Integer` in your code. I'd exchange them all for `Long`: Integers vs Longs 32-bit vs 64-bit http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – Ralph Nov 09 '17 at 13:12
  • 2
    What is `step1` – jamheadart Nov 09 '17 at 13:12
  • `If Cells(1, 7 + c).Value = ("VRP23" Or "VRP24") Then` needs to be `If Cells(1, 7 + c).Value = "VRP23" Or Cells(1, 7 + c).Value = "VRP24" Then` – Shai Rado Nov 09 '17 at 13:18
  • Have you debugged your code? Place the cursor to one of the cells that shows `#VALUE!`, switch to VBA-Editor, set a breakpoint (F9) at the beginning of the code and then enter `? getECONpgdimscore1(activecell.value)` in the immediate window and then step thru your code. – FunThomas Nov 09 '17 at 13:18
  • 1
    The If statement should be `If Cells(1, 7 + c).Value = "VRP23" Or Cells(1, 7 + c).Value = "VRP24" Then` – newacc2240 Nov 09 '17 at 13:19
  • There are quite a few issues with this code. Selecting cells is not necessary. You should also count up to find the last row (number of rows). Your loop is using a keyword "row" as an increment which also doesn't match the number of rows (you could just use x). – jamheadart Nov 09 '17 at 13:19
  • Try setting in the debugging options Break on all errors, then run your function again and see where the error occurs. Don't forget to switch it back though. – PaulG Nov 09 '17 at 14:01

0 Answers0