Excel VBA error – where do I look for the problem?

[under8d]

Hello all;

I’m stuck! I have a bit of code that works just fine in a small developmental spreadsheet that I used to figure out if and how I could do this, but when I cut and paste it into my larger spreadsheet I get the following error.

“Object variable or With block variable not set”

It stops at the line “Intersect(myRow.EntireRow, myCol.EntireColumn).Activate”

The listboxes are named the same in both projects, and there are no naming conflicts.

You have already figured out that I am not a programmer. I like to play with VBA, but my activity mostly consists of finding code that almost does what I would like to accomplish and modifying it to suit, so I have a lot of trouble when I bump into these types of challenges.

The code works in one environment and not another and they appear to me to be similar and should present no conflicts. But I am obviously missing something here.

So, I don’t have a clue as to where to look for what is causing this grief. Any advice would be appreciated.

Thanks,

Ray

Private Sub cmdOK_Click()

Dim myCol As Range
Dim myRow As Range
Dim myName As String
Dim number As String
 
ActiveWorkbook.Sheets("Sheet1").Activate
 
myName = lstName.Value
number = lstActivity.Value
 
Set myRow = Columns(1).Find(myName)
Set myCol = Rows(1).Find(number)
    Intersect(myRow.EntireRow, myCol.EntireColumn).Activate
    With Sheet1
    ActiveCell.Select
    ActiveCell.Value = Date
    End With

End Sub

 

[under8d]

If I managed to capture anyone’s curiosity, here is a link to the spreadsheets.

http://ptliving.net/ptl/

Click on the link “2 attachments” and download.
File names are:
activities spreadsheet Sept 16.xls
enters the date at col row intersection Sept 16.xls
Cheers,

Ray

 

[DaveInIowa]

Most likely either myName or number were not found which would leave myRow or myCol equal to Nothing (i.e, Object variable not set). Try replacing your cmdOK_Click routine with this and see what happens:

Private Sub cmdOK_Click()

    Dim myCol As Range
    Dim myRow As Range
    Dim myName As String
    Dim number As String
     
    ActiveWorkbook.Sheets("Sheet1").Activate
     
    myName = lstName.Value
    number = lstActivity.Value
     
    Set myRow = Columns(1).Find(myName)
    Set myCol = Rows(1).Find(number)
    If (myRow Is Nothing) Then
        MsgBox myName & " not found"
    ElseIf (myCol Is Nothing) Then
        MsgBox number & " not found"
    Else
        Intersect(myRow.EntireRow, myCol.EntireColumn).Activate
        With Sheet1
        ActiveCell.Select
        ActiveCell.Value = Date
        End With
    End If

End Sub

 

[Gavona]

So, I don't have a clue as to where to look for what is causing this grief. Any advice would be appreciated.

Generally I would use the watch window and step through my code when something unexpected happens (or doesn't happen).

When you get an error choose to debug rather than end. Right click over, say, myrow.entirerow and choose to Add Watch. In the watch window you can edit the watch - I would probably change this one to myrow.address and also add mycol.address.
If this shows you that myrow is nothing then add a watch for number. Does that value exist in row1 of your worksheet? etc.

Use F8 to step through the macro watching what is happening on screen and adding watches as you go to figure out what the code is doing. Set breakpoints just before problem bits of code so you can step through just those.

Gavin

 

[mintjulep]

And there is really no need for the With statement in the first place.

Set myrange = Intersect(myRow.EntireRow, myCol.EntireColumn)
myrange.Value = Date

 

[under8d]

Thanks folks;

I ditched the With statement, and set up the watch. My lack of understanding of this shines through. I didn’t come up with any clues at all. And it is frustrating that it works in one circumstance and not another. I guess that I just don't know what I should be looking for. Any more ideas to further my education?

Cheers,

Ray

 

[Gavona]

OK I have downloaded your workbooks.
When the code errors this line is highlighted:
Intersect(myRow.EntireRow, myCol.EntireColumn).Activate

Hovering over myCol.EntireColumn (or watching it) shows:
"Object variable or With block variable not set"

So does hovering over myRow.EntireRow

So hover over (or watch) myRow : myRow=Nothing
still something wrong

The code says:
Set myRow = Columns(1).Find(myName)
This means: Find myName in the first column of the workbook.
myRow is nothing because it hasn't been found

Hover over myName, note the name and see if it exists in the first column of your workbook.
The problem is pretty obvious - you'll kick yourself!

Investigate myCol in a similar way... There is of course no way that
Set myCol = Rows(1).Find(number)
will do what you intend. Your activities are in Row 2 not Row 1!

I hope that you will now be better able to debug your code.

Gavin

 

[under8d]

Hi Gavin;

Thanks for taking the time to help me with my education. Patiently explaining that to me was a huge help, and now I have a much better understanding of how to use the debugging tools.

And it is amazing how I can miss the obvious. I wish my mind was wired a bit differently so that I could mange this. Perhaps lots of practice will lead me there.

So now I have one (I think) more favour to ask. I sure could use a bit of advice on how to come up with a line of code that would look for the condition where a last name from column A followed by a comma and then the first name from column B to be the condition that part of the code is looking for.

I guess put in pseudo code:
The cell where row A contains X (last name) and row B contains Y (first name) and the two strings are separated by a comma intersects with the column that contains the value X (an activity.

Having typed this, I think that I am on the wrong track. I am wondering if it is possible to parse the value in the lstName. No, that wouldn’t work. Two people could share the same last name. Stuck!

Thinking out loud here, and not very successfully methinks!

Thanks so much.

Ray

 

[Gavona]

I am not a programmer either. However, I am pretty sure that you have the right approach already. Just create another column and put a formula in it like this to concatenate lastname and firstname:
=A1&", "&B1

I suggest making the new column Column A. It could be hidden if you prefer.

(Remember, get things working manually. Then use the recorder to record yourself doing them, then modify the code.)





Gavin

 

[under8d]

Thanks again Gavin. I was thinking of solving the problem by doing some string manipulation to add the first name to column A, then execute intersect bit and another string manipulation to restore it back to a first name, last name column set up. This does seem like a non programmer’s solution to solving the problem. It always amazes me how skilled programmers can create miracles with a couple of lines of code. Perhaps on of them will jump in with a suggestion or two.

Thanks again. You really helped me a great deal.

Ray

 

[Gavona]

My suggestion was to have the concatenated column available all the time. Just hide it.
I think the most efficient code often uses built in functionality as in-built functions use very efficient low level underlying code. Generally looping through cells is going to be slow compared with in-built functionality that achieves the same outcome. Example:
To find the sum of a range of cells you could use in built =sum(range)
or you could loop through
Mycell.value=0
For each c in range
Mycell.value = mycell.value + c.value
Next c

It always amazes me how skilled programmers can create miracles with a couple of lines of code. Perhaps on of them will jump in with a suggestion or two.

Agreed and an interesting challenge for them. However in this case, in my opinion (further challenge to the programmers)
Any 'programming' solution will either have to join the values in the two columns or to look first for rows which contain the Lastname then within those rows for rows that contain the Firstname. Two searches rather than one must surely be less efficient? Joining values from the two columns to compare them every time, rather than once must again be inefficient.

There are other ways without using an extra column. EG use advanced filter (or autofilter) to hide all but the row that meets your name criteria then use Find to get to the column... This can be applied with code and the user need not be aware. Probably a quick as the first solution I suggested.

In my opinion, the trick is to combine the in-built functionality with coding to use it efficiently. For example:
*select all cells and apply the formula, don't copy it down.
Without VBA select all cells, type the formula for the active cell then use Ctrl-Enter. With the range still selected Copy PasteSpecial, Values)
Simplify the recorded code, remove unnecessary selections etc. Wrap it with code to only calculate the range in question, to switch off screen updating etc.

Why do you want to use code that you do not understand, so find difficult to debug rather than code that you have created yourself and fully understand. I suggest create your own code then post alongside a description of the business task and ask how your approach or code could be improved.


Gavin