?
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
Medium Priority
?
134 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
[X]
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
  • 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 2000 total points
ID: 40584174
Personally, I would have the line above as

For Each r In Range("A6:A1000").cells()
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

771 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