• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

Looping through a range (For each r in range) showing no value on cells that do have a value

Looping through a range (For each r in range) showing no value on cells that do have a value.
Faulting out at line 91, the message box to say that r.value is blank, check the cell and row, its not blank,
and its random, sometimes its working, sometimes its not. (mostly not).
BUT, here's what's wrecking my head, when I F8 through, it all works fine.. so I never get to see the issue occuring in real time.


Sub Config()
' Configure For Use Button on main page
' Set user Password for Lotus (If used) and Server location for system
Dim tempx As String
Dim sTemDir1 As String
Dim sTemp As String

If MsgBox("Are you sure you wish to Configure the System?", vbYesNo) = vbYes Then
Else
Exit Sub
End If
Application.ScreenUpdating = False


' Set Directory for Project DocVersion Folder
sTemDir = Application.GetSaveAsFilename(InitialFileName:="_", Title:="Browse to the Projects Directory and click Save, without selecting a file or folder.")

tempx = sTemDir
sTemDir = RemoveLastChar(tempx)


' Set Server
sTemp = sTemDir

' Contacts Matrix
sTemDir1 = sTemDir & "_Project Contacts Matrix\"

' Set Project Directory
Sheets("Configuration Tab").Range("B4").Value = sTemDir

' Set Contacts Matrix Directory
Sheets("Configuration Tab").Range("B5").Value = sTemDir1

' Lotus Notes Password
' Sheets("Configuration Tab").Range("B3").Value = InputBox("Password for your Lotus Notes?")

' Server Location
Sheets("Configuration Tab").Range("B6").Value = sTemp

' My Location
Sheets("Configuration Tab").Range("B7").Value = ThisWorkbook.Path


Dim sSections As String
Dim wsSheet, _
    wsSheet1, _
    wsSheet2, _
    wsSheet3 As Worksheet
Dim MyArray1 As Variant
Dim iRowZ As Integer
Dim wb As Workbook



    
On Error Resume Next



'########################################################################
'########################################################################
'##########~~~~~~~~~~~~~~~Set Values for DropDown1~~~~~~~~~~~~~~~########
'########################################################################
'########################################################################

'sTemp = Sheets("Configuration Tab").Range("B4").Text
'If sTemp = "" Then
'MsgBox ("Please Configure for use, save, close and reopen.")
'Exit Sub
'Else
'End If
'sTemp = Left(sTemp, 3)
'Sheets("Configuration Tab").Range("B6").Text = sTemp

wb = Workbooks.Open(Filename:=sTemp & "_Project Index\Project Directory Management.xlsm")

wb.Sheets("Projects Log").Select
Range("A6").Select

sSections = ""

If Not myDict1 Is Nothing Then
        myDict1.RemoveAll
    Else
        Set myDict1 = CreateObject("Scripting.Dictionary")
End If
0
For Each r In Range("A6:A1000")

    If r.Value = "" Then
    MsgBox "blank at Line: " & ActiveCell.Row
    GoTo Next1
    End If
        If Not myDict1.exists(r.Value & "-" & r.Offset(0, 1).Value) And r.Offset(0, 9).Value = "New" Then
            myDict1.Add r.Value & "-" & r.Offset(0, 1).Value, i
            i = i + 1
            sSections = sSections & "|" & r.Value & "-" & r.Offset(0, 1).Text
        End If
Next r



Next1:


MyArray1 = Split(sSections, "|")

Workbooks("Project Directory Management.xlsm").Close False

ThisWorkbook.Activate
Worksheets("Control Tab").Select
ThisWorkbook.Worksheets("Control Tab").ComboBox1.List = MyArray1

Open in new window

0
Conor_Newman
Asked:
Conor_Newman
  • 4
  • 2
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Is it because line 91 says

    MsgBox "blank at Line: " & ActiveCell.Row

Should it say:

    MsgBox "blank at Line: " & r.Row

?
0
 
Conor_NewmanAuthor Commented:
Still has the same issue, its the line above it is the real issue I suppose, it shouldn't be reading r.value as "". There is a text string in all the cells, but its reading them as nothing and so my array never gets populated. Its worked for 3 years, and just started having issues the last few weeks guessing some Microsoft update changed something. ( have that damn .exd issue as well popping up on my users pcs.)
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Personally, I would have the line above as

For Each r In Range("A6:A1000").cells()
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Conor_NewmanAuthor Commented:
You may have fixed it ;-) Will  accept as solution in a few minutes after I run it a few more times. Thank you.
0
 
Conor_NewmanAuthor Commented:
Quick and simple fix, curious as to why it worked up until now, maybe something changed in one of the office updates.  Regardless, thank you for the fix :)
0
 
scsymeCommented:
Looking promising, although find the behaviour and apparent solution somewhat odd.

A few other considerations for future.
Are the cells in the range populated with absolute values, or using a formula?
Consider a more verbose message box output in the trouble area, making no assumptions about exactly what Excel is looking at.

    MsgBox "ActiveWorkbook.Name: " & ActiveWorkbook.Name & vbCrLf & _
            "ActiveSheet.Name: " & ActiveSheet.Name & vbCrLf & _
            "ActiveCell.AddressLocal: " & ActiveCell.AddressLocal & vbCrLf & _
            "ActiveCell.Value: " & ActiveCell.Value & vbCrLf & _
            "ActiveCell.Value2: " & ActiveCell.Value2 & vbCrLf & _
            "ActiveCell.Formula: " & ActiveCell.Formula & vbCrLf

Open in new window


Either replace ActiveCell with r in your case, or just before the message box put r.Activate

Also strongly consider using Option Strict and pre-defining your variables into specific types. This may have helped here too, if r was specifically defined as a Range.
0
 
Conor_NewmanAuthor Commented:
Everything has absolute values. No formula's in the sheets at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now