Checking Values in columns with an Array of variables

Question

I am working in VBA and getting data from a database. However, sometimes the data is missing a value. Therefore I want to know when it is missing a value.

I thought I had a clever idea where I could define each column as its own range. Then create an array of those column names, loop through it, then loop through each cell within the range, and then if a cell is empty, take the average of the surrounding cells. However, it doesn't seem like I am defining my "range variable", the variable I use in the first loop through of the columns, correctly. VBA says it is "Nothing."

Dim csheet as Worksheet
Set csheet  =ThisWorkbook.Sheets(“Sheet1”)
Dim name1, name2, name3, name4, name5, name6, name7, name8 As Range
Set name1 = csheet.Range("B4", "B25")
Set name2 = csheet.Range("C4", "C25")
Set name3= csheet.Range("D4", "D25")
Set name4= csheet.Range("E4", "E25")
Set name5= csheet.Range("F4", "F25")
Set name6= csheet.Range("G4", "G25")
Set name7= csheet.Range("H4", "H25")
Set name8 = csheet.Range("I4", "I25")

Dim data() As Variant
data = Array(name1, name2, name3, name4, name5, name6, name7, name8)
Dim currentRange As Range

For k = 1 To UBound(data)
    currentRange = data(k)
    For Each cell In currentRange
        If IsEmpty(cell.Value) = True Then
            cell.Value = Application.WorksheetFunction.Average(cell.Offset(1, 0).Value, cell.Offset(-1, 0).Value)
            If IsError(cell.Value) = True Then
                cell.Value = cell.Offset(-1, 0)
            ElseIf IsError(cell.Value) = True Then
                cell.Value = cell.Offset(1, 0)
            ElseIf IsError(cell.Value) = True Then
                cell.Value = 0
                MsgBox ("There is an error with the data. Please fix once done running. Thank you.")
            End If
        End If
    Next cell
Next

Kashif Qureshi · Accepted Answer · 2017-09-15 15:46:28Z

use SET to set you CurrentRange variable. Like this:

 Set currentRange = data(k)

Full Code:

Dim csheet As Worksheet
Set csheet = ThisWorkbook.Sheets("Sheet1")
Dim name1, name2, name3, name4, name5, name6, name7, name8 As Range
Set name1 = csheet.Range("B4", "B25")
Set name2 = csheet.Range("C4", "C25")
Set name3 = csheet.Range("D4", "D25")
Set name4 = csheet.Range("E4", "E25")
Set name5 = csheet.Range("F4", "F25")
Set name6 = csheet.Range("G4", "G25")
Set name7 = csheet.Range("H4", "H25")
Set name8 = csheet.Range("I4", "I25")
Dim k As Integer

Dim data() As Variant
data = Array(name1, name2, name3, name4, name5, name6, name7, name8)
Dim currentRange As Range

For k = 0 To UBound(data)
    Set currentRange = data(k)
    For Each cell In currentRange
        If IsEmpty(cell.Value) = True Then
            cell.Value = Application.WorksheetFunction.Average(cell.Offset(1, 0).Value, cell.Offset(-1, 0).Value)
            If IsError(cell.Value) = True Then
                cell.Value = cell.Offset(-1, 0)
            ElseIf IsError(cell.Value) = True Then
                cell.Value = cell.Offset(1, 0)
            ElseIf IsError(cell.Value) = True Then
                cell.Value = 0
                MsgBox ("There is an error with the data. Please fix once done running. Thank you.")
            End If
        End If
    Next cell
Next

R. Roe · Accepted Answer · 2017-09-15 15:51:58Z

You forgot to set your currentRange like this:

Set currentRange = data(k)

Also you are skipping your first range in the array. You should start your loop at 0. Arrays are 0 indexed, they always start at 0.

For k = 0 To UBound(data)

One other thing, I'm not sure why you're checking IsError(cell.Value) three times with the same condition. With your current code and if the cell has an error it will only be caught the first time. It will never reach the other two checks. That is the nature of if then ifelse statements. if A is true there is no need to check the other if statements, or if A is false then check the next statement if B is true then there is no need to check the other statement, etc.

If your intent was to check it three times then you would need separate if then statements for each check, but it still wouldn't do you much good because you are using the same condition three times so if one is true they're all true. Hope that makes sense.

Collectives™ on Stack Overflow

Checking Values in columns with an Array of variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related