Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2015-02-02
7
123 Views
Last Modified: 2016-02-11
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
Comment
Question by:Conor_Newman
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40584147
Is it because line 91 says

    MsgBox "blank at Line: " & ActiveCell.Row

Should it say:

    MsgBox "blank at Line: " & r.Row

?
0
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40584171
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40584174
Personally, I would have the line above as

For Each r In Range("A6:A1000").cells()
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:Conor_Newman
ID: 40584180
You may have fixed it ;-) Will  accept as solution in a few minutes after I run it a few more times. Thank you.
0
 
LVL 2

Author Closing Comment

by:Conor_Newman
ID: 40584200
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
 
LVL 4

Expert Comment

by:scsyme
ID: 40584233
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40585902
Everything has absolute values. No formula's in the sheets at all.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question