Code Help Needed: Object Variables

[seenfresh]

I am having a little trouble with the following code...

The following line raises the error object variable not set! I am referencing two distinct worksheet through this code...

x = PLATFORM.UsedRange.Rows.Count

Can anyone spot what I am doing wrong with regards to working across multiple objects and how to better set and declare them?

Any advice would be awesome,

Thanks in advance,

Sub GroupListUpdate()

ApplicationScreenUpdating = False

Dim PLATFORM As Worksheet
Dim GROUPLIST As Worksheet

x = PLATFORM.UsedRange.Rows.Count

For iRow = 1 To x

If PLATFORM.Cells(iRow, 3).Interior.ColorIndex = 15 Then

iGroup = PLATFORM.Cells(iRow, 3).Text


With GROUPLIST.Range("A1:A" & Rows.Count)

 
  Set c = .Find(iGroup, LookIn:=xlValues)
  
' TRYING TO FIND the Text of datae represented earlier!
   If c Is Nothing Then
   
' IF WE CAN'T FIND IT, then insert iGroup..
    
       theRow = GROUPLIST.Cells.Find("", .Cells(1, 1), xlFormulas, _
        xlWhole, xlByColumns, xlNext).Row
        
  ' This finds a row starting from top that is empty to put the new values in!!!
        GROUPLIST.Cells(theRow, 1).Value = iGroup
        
        End If
  End With
            
    
    End If

 

[Jonmo1]

You need to tell the code which sheets PLATFORM and GROUPLIST should refer to..
something like

Dim PLATFORM As Worksheet 
Dim GROUPLIST As Worksheet 
Set PLATFORM = Sheets("Sheet1")
Set GROUPLIST = Sheets("Sheet2")
x = PLATFORM.UsedRange.Rows.Count

 

[seenfresh]

reply

Thanks Jonmo,

I understand!

I have come accross another problem, my code is suppose to gather the information from the PLATFORM worksheet of which cells have interior color index of 15 and then go to GROUPLIST worksheet and find an empty cell in column A to put the value in only if the value does not currently exists.

Now i have done this type of procedure before, but this time the same cell in the GROUPLIST worksheet A2 keeps being populated even if it currently holds a value with the last occurance of PLATFORM cell value based on color index...

This tells me a couple of things either the Find function used in my code does not work properly thus keeps refering to row 2 as theRow, and this code should list each unique value through stepping from Row 1 to End thus I should have a populated list...

Any thoughts?

Once again thanks for your help... Much appreciated!

 

[Jonmo1]

Try this out

Sub GroupListUpdate() 

ApplicationScreenUpdating = False

Dim PLATFORM As Worksheet
Dim GROUPLIST As Worksheet
Set PLATFORM = Sheets("PLATFORM")
Set GROUPLIST = Sheets("GROUPLIST")
x = PLATFORM.Cells(Rows.Count, "C").End(xlUp).Row
For iRow = 1 To x

If PLATFORM.Cells(iRow, 3).Interior.ColorIndex = 15 Then

    iGroup = PLATFORM.Cells(iRow, 3).Text
    
    
    With GROUPLIST
    
        c = WorksheetFunction.CountIf(.Range("A:A"), iGroup)
    
      
        ' TRYING TO FIND the Text of datae represented earlier!
        If c = 0 Then
        
            ' IF WE CAN'T FIND IT, then insert iGroup..
            
            theRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
            ' This finds a row starting from top that is empty to put the new values in!!!
            .Cells(theRow, 1).Value = iGroup
            
        End If
    End With
            
    
End If

Next iRow

 

[seenfresh]

reply

Thanks Jonmo

It works great!

I'm still a bit bummed that my previous code wasn't co-operating, but EH!

You code consumes less event procedures and memory usage...

The following line of your code:

theRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1

not quite sure how this finds the first empty cell starting from the top... I see this code as counting the number or rows in column A then activating the first row with the "End(xlUp).Row" and finally adding 1 to make theRow = 2

Obviously I'm interpreting this wrong, could you explain this part!

Thanks again!

May you win the Lottery!!!!

 

[Norie]

seenfresh

That code doesn't find the next empty cell working downwards.

It's coming from the bottom up, which is standard practice.

When working from the top down you can run into various problems, especially if your data is non-contiguous.

 

[Jonmo1]

actually, it takes column A and rows.count (65536), or A65536. Then the .End(xlup) is like pressing CTRL + UP on your keyboard. It finds the Lowest (or highest Row #) NON Blank Cell in column A.

so in keystrokes, it's like

Selecting Cell A65536
Pressing CTRL + UP
Pressing Down

The only times this method would fail is
1. You happen to have data filled in ALL rows from 1 to 65536.
2. You have a stray value somewhere far below what you think is the end of column A.