Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
136 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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