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
121 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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