Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2021, 10:12 PM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default How can i upload values of Non-Empty Cells Reference of a Table in ComboBox

Hello



Any ideas How can i upload values of ONLY Non-Empty Cells Reference of a Table in ComboBox. Bit Confused in getting the right syntax.

The below code gives all the Cell References in the table. Empty and Non-Empty

Code:
Public Sub NonEmptyCellRef()

Dim oTbl As Table
Dim NonEmptyCellListStr As String,  NonEmptyCellsList As Variant

Dim wdActDoc As Document
Set wdActDoc = ActiveDocument


Set oTbl = wdActDoc.Tables(1)
With oTbl.Range
  For i = 1 To .Cells.Count
    With .Cells(i)
          NonEmptyCellListStr = NonEmptyCellListStr & .RowIndex & ", " & .ColumnIndex & "^"  
          NonEmptyCellsList =  Split((Replace(NonEmptyCellListStr, Chr(147), "")), "^")
    End With
  Next
  ComboBox1.List = NonEmptyCellsList

End With

End Sub
SamD
Reply With Quote
  #2  
Old 03-07-2021, 11:59 PM
gmayor's Avatar
gmayor gmayor is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Assuming this relates to userform code:

Code:
Private Sub UserForm_Initialize()
    NonEmptyCellRef
End Sub

Private Sub NonEmptyCellRef()
Dim oTbl As Table
Dim i As Integer
    Set oTbl = ActiveDocument.Tables(1)
    With oTbl.Range
        For i = 1 To .Cells.Count
            If Len(.Cells(i).Range) > 2 Then
                ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex & "^"
            End If
        Next i
    End With
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 03-08-2021, 04:26 AM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default

Sir,
Quote:
Assuming this relates to userform code:
Yes Mostly i use Userform Objects

Thank you so much you made me remove the following
Code:
NonEmptyCellListStr = NonEmptyCellListStr & .RowIndex & ", " & .ColumnIndex & "^" 
NonEmptyCellsList =  Split((Replace(NonEmptyCellListStr, Chr(147), "")), "^")
OMG And How could I forget Combobox1.Additem

Nevertheless Inorder to have correct neat representation for Cell Reference uploaded in Combobox
Quote:
i changed
ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex & "^"
to
ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex
As I moved on to check Now for Empty Cell Reference with following code. Some unusual results
Code:
Private Sub EmptyCellRef()
Dim oTbl As Table
Dim i As Integer, sText As String, sList As String
    Set oTbl = ActiveDocument.Tables(1)
    With oTbl.Range
        For i = 1 To .Cells.Count
             sText = Trim(.Cells(i).Range.Text)
             If Split(sText, vbCr)(0) = "" Then                
                   ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex 
            End If
        Next i
    End With
End Sub
I dont understand why the result is different. Have Attached .docx File

I get the List of Empty Cell Reference as

2, 2
2, 3
3, 1
3, 2
3, 3
4, 1
4, 2
4, 3

All though some of the above cell reference has some data. like cells 2, 2 and 2, 3

To have correct list of Empty Cell Reference as mentioned below
3, 1
3, 2
3, 3
4, 1
4, 2
4, 3

Do I have to consider the Paragraphs Aspect of Each cell. if yes then How ? to get the above mentioned Empty Cell Reference

SamD
Attached Files
File Type: docx Table-Cells-NonEmpty-Empty-Reference.docx (11.5 KB, 5 views)
Reply With Quote
  #4  
Old 03-08-2021, 04:53 AM
gmayor's Avatar
gmayor gmayor is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

If you want the empty cells listing then

Code:
For i = 1 To .Cells.Count
            If Len(.Cells(i).Range) = 2 Then
                ComboBox1.AddItem .Cells(i).RowIndex & ", " & .Cells(i).ColumnIndex
            End If
Next i
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 03-08-2021, 10:00 PM
SamDsouza SamDsouza is offline How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Windows 10 How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Office 2013
Advanced Beginner
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox
 
Join Date: Aug 2019
Posts: 71
SamDsouza is on a distinguished road
Default

Thank you Sir,


What i take from your posts of this thread is that minimum length of Cell of Table is 2.
Any specific reason for MINIMUM Len of Cell of a Table = 2

Also I need to take care whenever i have to get Len of Text in a cell will always be as LEN(.Cell(1,1).Range.Text)- 2

This is RESOLVED


SamD
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox How to SUM the values only if the particular cells are not empty? Learner7 Excel 3 11-10-2020 02:47 AM
auto progress number evenly between two values across empty cells ? DBenz Excel 4 03-28-2018 04:46 AM
If a2 is not empty, color empty cells in b2:af2 turkanet Excel 2 08-20-2017 11:00 PM
How can i upload values of Non-Empty Cells Reference of a Table in ComboBox Microsoft Excel 12.0 Object Library reference upload chamdan Project 2 04-21-2014 06:30 AM
Apparently empty (blank) cells aren't empty daymaker Excel 3 03-08-2012 03:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft