excel get cell value 1

[Daya100]

I tried to get help last week and it didn't quite solve the problem. So - now I have a new approach that doesn't work. I'm trying to add cell values to a collection. But, I keep getting the error that the subscript is out of range. Here is my code:

Private Sub UserForm_Activate()

Dim EmptyCells As New Collection
Dim Inst As New Class1
Dim txtNoVal, Name1, cell

cell = Application.Worksheets("Sheet1&quot.Range("d25&quot.Value
'this is what gets the error


If IsEmpty(cell) = True Then
txtNoVal = "Facility Request sheet, cell D25, Date Facilities are required"
End If

Inst.InstanceName = txtNoVal
EmptyCells.Add Item:=Inst
Set Inst = Nothing

thanks!

 

[SkipVought]

Hi,

Not really sure what yer after, but here's a swag...

    Dim EmptyCells As New Collection
    Dim Inst As Object
    Dim txtNoVal, Name1, cell


    cell = Application.Worksheets("Sheet1").Range("d25").Value
        'this is what gets the error
        
        
    If IsEmpty(cell) Then
        txtNoVal = "Facility Request sheet, cell D25, Date Facilities are required "
        Set Inst = Application.Worksheets("Sheet1").Range("d25")
    End If
        
    EmptyCells.Add Item:=Inst.Address
    For Each x In EmptyCells
        MsgBox x
    Next
    Set Inst = Nothing

Tell me if I got close???? Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

well, what I'm trying to do is see if a cell has a value in it. If it doesn't then I add a tect string to the collection. Then later I add that to a list box. BUT - I'm stuck on getting the cells value. This part:
cell = Application.Worksheets("Sheet1&quot.Range("d25&quot.Value
it won't look for a value in the cell - just the subscript error.

 

[SkipVought]

well, I don't know WHY it bombs on cell = ...
for you. It does not for me.

It seems that the code ought to be modified to make the assignment ONLY when cell is empty...

I have selected a range of cells, some of which are empty...

    Dim EmptyCells As New Collection
    Dim cell

    For Each cell In Selection
        If IsEmpty(cell) Then
            EmptyCells.Add Item:=cell.Address
        End If
    Next
' now see what has been flagged as empty
    For Each x In EmptyCells
        MsgBox x
    Next

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

in fact, if you really want the collection to contain the RANGE collection of empty cells, then try this...

    Dim EmptyCells As New Collection
    Dim cell As Range, x

    For Each cell In Selection
        If IsEmpty(cell) Then
            EmptyCells.Add Item:=cell
        End If
    Next
    For Each x In EmptyCells
        x.Value = "FULL"
    Next

"FULL" will be entered into each empty cell LOL Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

That's cool. One more question since I'm not so VBA savvy. WHat is Selection? Are you definiing what that is - or it's whatever cells you have selcted currently or...?
Thanks

 

[SkipVought]

Selection is a Range (array) of selected cells - could be contiguous or not) -- so I could have replaced

Selection...

with any other Range Object. Ranges do not have to be selected. For instance...

Range(Cells(1, 1), Cells(3, 4))

defines a range A13.

Range(Cells(1, 1), Cells(3, 4)).Select

actually selects the range on the ActiveSheet.

I am not sure what you are wanting to do, but creating a collection is a novel idea and can be very useful. Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

Hey Skip,

Thanks so much! I figured out my error too and I feel like a complete idiot - I had the sheet name wrong.