[resolved] Please help with VBA, for Excel 2007: working with objects

Hi, I have attempted to create a code for the following described purpose, but my codes do not produce my desired results. Without reallizing the difference between VB.NET and VBA, I had a thread over at the VB.NET forum.
My version of VBA is: "Microsoft Visual Basic 6.5", VBA: Retail 6.5.1053


I am attempting to create a program to take account of several cells in a column like so (example):
123
234
123
123
345
005
007
123

1. filter out any 005, 007, 006, and 009 values,

2. then store everything else into an array without repetition, so the array would now be:
123
234
345

3. then convert integers to strings
4. lastly combine the strings
5. and output the combined string:
"123, 234, 345"

My codes are:

Code:

---

Public Function CollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String

' This is a non-coded note
' input asks for Sheetname, the initial cell position (CellCol, CellRow), and the number of cells to account for (including initial cell)
' Dim = command to set variable type
Dim CellRowNow, CurrVal As Integer ' CellRowNow is the current position of Cell row, CurrVal is the value of the current cell
Dim SourceArray(), ArrayLength, ArrayPosition As Integer ' SourceArray(ArrayPosition) is the value of the current array variable
Dim NoSimilar As Boolean ' NoSimilar is whether the CurrVal is the same value as any numbers already stored in SourceArray()
Dim result, StringTemp As String ' result is the accumulating string of numbers, StringTemp is used to store string converted from integer

ArrayLength = 0

    Do While SelectLength > 0
        ' loops for "SelectLength" numbers of times
        SelectLength = SelectLength - 1 ' if initial cell at (1,1) w/ SelectLength of 2, then first processed position is at (1,2) when SelectLength should now be 1 (2-1=1), second position (which is the initial cell at (CellCol, CellRow)) is at (1,1) when SelectLength is 0 (1-1=0)
        CellRowNow = CellRow + SelectLength ' sets current cell row, and current cell column never changes
        CurrVal = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' gets current cell value from current position
        ' ---------------
        If CurrVal <> 5 Or CurrVal <> 6 Or CurrVal <> 7 Or CurrVal <> 9 Then
            ' if CurrVal does not equal 5, 6, 7, or 9
            ' ---------------
            If ArrayLength > 0 Then
                ' if not first value in array
                ArrayPosition = 0 ' reset array position
                NoSimilar = False
                ' ---------------
                Do While ArrayPosition < ArrayLength
                    ' loops for "ArrayLength" numbers of times
                    If SourceArray(ArrayPosition) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value Then
                        ' if current cell value equal any of array values
                        NoSimilar = False ' not similar is not true
                        Exit Do ' thus exit loop
                    End If
                    NoSimilar = True ' if still in loop then there hasn't been match between cell value and array value
                    ArrayPosition = ArrayPosition + 1 ' advance to next array position
                Loop
                ' ---------------
                ' ---------------
                If NoSimilar = True Then
                    ReDim Preserve SourceArray(0 To ArrayPosition) ' ArrayPosition numerically 1 less than ArrayLength to account for 0
                    SourceArray(ArrayPosition) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' insert current value into current array slot
                    ArrayLength = ArrayLength + 1 ' increase record of array length
                Else
                    ' If NoSimilar = False, then nothing happens here
                End If
                ' ---------------
            ElseIf ArrayLength = 0 Then
                ' if first value in array
                ReDim SourceArray(0) ' initialize first array slot
                SourceArray(0) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' insert value into first array slot
                ArrayLength = 1 ' set array length to 1
            Else
                result = "Error" ' impossible case where ArrayLength value is absent or < 0
            End If
            ' ---------------
        Else
            ' If CurrVal does equal 5, 6, 7, or 9, then nothing happens here
        End If
        ' ---------------
        If result = "Error" Then
            Exit Do ' exit loop if there is an error
        End If
    Loop
   
    ArrayPosition = 0
    Do While ArrayPosition < ArrayLength
        StringTemp = str(SourceArray(ArrayPosition))
        result = result + ", " + StringTemp
        ArrayPosition = ArrayPosition + 1
    Loop
    CollectSources = result
End Function

---

There are no error messages from my codes, but it does not do as it is intended.
Currently, it displays: ", 0"
no matter what data was given.

Kevininstructor kindly revamped my entire code into the following .NET code:

Code:

---

Private Sub GetDistinctValues()
    Using cn As New System.Data.OleDb.OleDbConnection
        ' Change to your worksheet
        Using cmd As OleDbCommand = New OleDbCommand With _
        { _
                .Connection = cn, _
                .CommandText = "SELECT DISTINCT F1 FROM [Sheet1$] " _
        }
            ' Change to use your file
            Dim FileName As String = IO.Path.Combine(Application.StartupPath, "book1.xlsx")
            ' Change if not using Excel 2007
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = FileName, _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }
            ' Change if not using Excel 2007
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")

            cn.ConnectionString = Builder.ConnectionString
            cn.Open()

            Dim dt As New DataTable
            dt.Load (cmd.ExecuteReader)
            Dim Result = String.Join(",", _
            ( _
                From T In dt.AsEnumerable _
                Select Value = CStr(T.Field(Of Integer)("F1"))).ToArray _
            )

            Console.WriteLine (result)
        End Using
    End Using
End Sub

---

Which led to my reallization that VB.NET is entirely incompatible with VBA.

Thanks to anyone for taking a look at this.

Try this simplified code

vb Code:

Public Function CollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String
 
    Dim colUnique As Collection
    Dim lngCurrVal As Long
    Dim j As Long
    Dim strResult As String
    
    On Error Resume Next
    
    Set colUnique = New Collection
    
    For j = 0 To SelectLength - 1
        lngCurrVal = CLng(Worksheets(Sheetname).Cells(CellRow + j, CellCol).Value)
        If lngCurrVal <> 5 And lngCurrVal <> 6 And lngCurrVal <> 7 And lngCurrVal <> 9 Then
            colUnique.Add lngCurrVal, "k" & lngCurrVal ' Trying to add the same value with the same key will cause an error and nothing will added, by this way we ensure no value will repeated
                                                             ' I start the key with "k" because a key must be a string type
        End If
    Next
    
    For j = 1 To colUnique.Count
        strResult = strResult & colUnique.Item(j) & ", "
    Next
    
    CollectSources = Mid$(strResult, 1, Len(strResult) - 2) ' remove the last comma
    
End Function

If you need more description about the code, just ask.

VBA is also not VB6....although they are closer in syntax than VB6 and VB.NET there are still things in VB6 that will not work in VBA.

VBA questions are posted in the Office Development section and that is where I have moved your thread. :)

I have deleted the other one.

Thank you very much for your help, it worked perfectly for my purpose. I'll attempt to understand what I can, and be sure to follow with questions.

Thank you again!

Could you tell me what the dollar sign amidst of the Mid function at line 24 signifies? Thanks!

I did not previously reallize that the number of each unique value would have to be kept... To my understanding, this is also achieved by object oriented programming with objects having properties such as name and amount. However I am thus far unable to wrap my head around how to actually do it.

Would anyone be so kind to help me again in this matter? Thank you.

Example:
From (values in cells in a column formation):
123
234
123
123
345
345
005
007
123

Output:
"123 (4), 234 (1), 345 (2)"

Quote:

---

Originally Posted by needhelpooo

Could you tell me what the dollar sign amidst of the Mid function at line 24 signifies? Thanks!

---

Mid$ return the result as string type
while Mid return the result as object type
therefore Mid$ is recommended for speed purpose.

Quote:

---

Originally Posted by needhelpooo

Would anyone be so kind to help me again in this matter?

---

I'm here again :wave:

The new output requirement needs to change the implementation because using Collection as before will make it much difficult.
I have commented the code as possible as i can to make it clear.

vb Code:

Public Function CollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String
 
    Dim strValues() As String
    Dim lngCurrVal As Long
    Dim j As Long
    Dim k As Long
    Dim n As Long
    Dim b As Long
    Dim strResult As String
    
    
    ' Get values
    For j = 0 To SelectLength - 1
        lngCurrVal = CLng(Worksheets(Sheetname).Cells(CellRow + j, CellCol).Value)
        If lngCurrVal <> 5 And lngCurrVal <> 6 And lngCurrVal <> 7 And lngCurrVal <> 9 Then
            ReDim Preserve strValues(n)
            strValues(n) = lngCurrVal
            n = n + 1
        End If
    Next
    
    b = UBound(strValues) ' for speed purpose, cache UBound(strValues) in a variable instead of continuesly calculating it inside the loop
    
    ' Search for repeated values and add its count after it
    For j = 0 To b - 1
        If strValues(j) <> vbNullString Then
            n = 1
            For k = j + 1 To b
                If strValues(j) = strValues(k) Then
                    n = n + 1
                    strValues(k) = vbNullString ' delete the repeated value in order to not find it again.
                End If
            Next
            strValues(j) = strValues(j) & " (" & n & ")" ' append the value count
        End If
    Next
    
    For j = 0 To b
        If strValues(j) <> vbNullString Then
            strResult = strResult & strValues(j) & ", "
        End If
    Next
    
    CollectSources = Mid$(strResult, 1, Len(strResult) - 2) ' remove the last comma
End Function

Thank you very much! :wave:

Again, that is exactly what I need for my data organization. I'll take some time to learn these codes then return with questions for anything I couldn't understand.

Thanks again!

Hi again, I've noticed that when at the end of the output list, if there is only one of the item at the end it doesn't show the (#) part, example:

For:
123
123
345
345
The output would be:
123 (2), 345 (2)

While for:
123
123
345
The output would be:
123 (2), 345
I would like this format:
123 (2), 345 (1)

I'm writing a follow up program for the next step, which would require the data to be in the "345 (1)" format (I'm not yet good enough to work around things). While I gained some understanding to each of your lines, I couldn't figure out why it's omitting the "(1)" at the end. Could you help me again?

Thank you! :)




The following code is the follow-up program, which requires the specific formatting for its inputs. Couldn't have been able to write it without your help. :)

Code:

---

Function reCollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String
Dim colUnique As Collection
Dim currElementAmount As Long
Dim j As Long
Dim k As Long
Dim n As Long
Dim b As Long
Dim x As Variant
Dim y As Variant
Dim strValues() As String
Dim strResult As String
Dim allStrings As String
Dim extElement As String
Dim currElement As String

' 1. Collect strings into allStrings
For j = 0 To SelectLength - 1
    allStrings = allStrings & CStr(Worksheets(Sheetname).Cells(CellRow, CellCol + j).value) & ", "
Next

' 2. Insert uniques from extracted elements of the single string into collection object
    ' extract entire elements
    x = Split(allStrings, "), ")
    For j = 0 To UBound(x) - 1
        extElement = x(j)
        ' separate value and amount in each element
        y = Split(extElement, " (")
        currElement = CStr(y(0))
        currElementAmount = CLng(y(1))
       
' 3. Reconstitute into array: strValues()
        For k = 1 To currElementAmount
            ReDim Preserve strValues(n)
            strValues(n) = currElement
            n = n + 1
            ' ''' vestigial code: strResult = strResult & currElement & ", "
        Next
    Next

' 4. Recount
        b = UBound(strValues)
 ' for speed purpose, cache UBound(strValues) in a variable instead of continuesly calculating it inside the loop
        ' Search for repeated values and add its count after it
    For j = 0 To b - 1
        If strValues(j) <> vbNullString Then
            n = 1
            For k = j + 1 To b
                If strValues(j) = strValues(k) Then
                    n = n + 1
                    strValues(k) = vbNullString ' delete the repeated value in order to not find it again.
                End If
            Next
            strValues(j) = strValues(j) & " (" & n & ")" ' append the value count
        End If
    Next
   
    For j = 0 To b
        If strValues(j) <> vbNullString Then
            strResult = strResult & strValues(j) & ", "
        End If
    Next


reCollectSources = Mid$(strResult, 1, Len(strResult) - 2)
End Function

---

What it does is that it takes inputs (listed in a column):
123 (2), 234(3), 345 (1)
234 (4), 345 (1)
123 (1), 345 (4)
And sums it up to:
123 (3), 234 (7), 345 (6)
And it's functional as long as the inputs are in the required format of "345 (1)".

The solution is simple, just replace the line #25

vb Code:

' Search for repeated values and add its count after it
    For j = 0 To b - 1

with

vb Code:

' Search for repeated values and add its count after it
    For j = 0 To b

That's perfect, thanks!

I'm also wondering about how to improve the codes I wrote so that it would display the list of numbers in a sorted manner according to the amount it appears in the array where the data was drawn from.

example:
What my codes in previous post does (listed in a column):
123 (2), 234(3), 345 (1)
234 (4), 345 (1)
123 (1), 345 (4)
Internally it converts it into an array that is a mixture that contains 3 of "123", 7 of "234", and 6 of "345".
And then outputs:
123 (3), 234 (7), 345 (6)
Would be great if its output could be sorted as such:
234 (7), 345 (6), 123 (3)
P.S. Is there a name to this type of sorting in computer science?

Could you help me with this again? Thank you so much!

Here you are, the previous code is not changed, i just added new sub to sort values according their count.

vb Code:

Public Function CollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String
 
    Dim strValues() As String
    Dim lngCurrVal As Long
    Dim j As Long
    Dim k As Long
    Dim n As Long
    Dim b As Long
    Dim strResult As String
    
    
    ' Get values
    For j = 0 To SelectLength - 1
        lngCurrVal = CLng(Worksheets(Sheetname).Cells(CellRow + j, CellCol).Value)
        If lngCurrVal <> 5 And lngCurrVal <> 6 And lngCurrVal <> 7 And lngCurrVal <> 9 Then
            ReDim Preserve strValues(n)
            strValues(n) = lngCurrVal
            n = n + 1
        End If
    Next
    
    b = UBound(strValues) ' cache UBound(strValues) in a variable instead of continuesly calculating it inside the loop
    
    ' Search for repeated values and add its count after it
    For j = 0 To b
        If strValues(j) <> vbNullString Then
            n = 1
            For k = j + 1 To b
                If strValues(j) = strValues(k) Then
                    n = n + 1
                    strValues(k) = vbNullString ' delete the repeated value in order to not find it again.
                End If
            Next
            strValues(j) = strValues(j) & " (" & n & ")" ' append value count
        End If
    Next
    
    SortValuesCount strValues
    
    For j = 0 To b
        If strValues(j) <> vbNullString Then
            strResult = strResult & strValues(j) & ", "
        End If
    Next
    
    CollectSources = Mid$(strResult, 1, Len(strResult) - 2) ' remove the last comma
End Function
 
Private Sub SortValuesCount(ByRef v() As String)
    Dim j As Long
    Dim k As Long
    Dim b As Long
    Dim x As Long
    Dim y As Long
    Dim z As String
    
    
    b = UBound(v)
    
    For j = 0 To b
        If v(j) <> vbNullString Then
            x = Val(Mid$(v(j), InStr(1, v(j), "(") + 1)) ' get the count
            For k = j + 1 To b
                If v(k) <> vbNullString Then
                    y = Val(Mid$(v(k), InStr(1, v(k), "(") + 1)) ' get the count
                    If x < y Then ' compare the two count and swap if x < y
                        z = v(j)
                        v(j) = v(k)
                        v(k) = z
                    End If
                End If
            Next
        End If
    Next
 
End Sub

Once again, thank you so much for your help! The sorting algorithm is great :)

Well,

Already have! :)
I'm stuck with it asking me to "spread it around" first, and I won't forget about rating the rest of your posts. You have really helped me very generously.

Quote:

---

Originally Posted by needhelpooo

Already have! :)

---

Thanks, indeed after posting, i discovered that you actually rate my solution, therefore i have edited the last post.

I had a question below, but after taking a nice walk, it came to me of why it happened as well as the solution. :)

By adding a while loop to repeat the sorting when a switch between numbers happen.

vb Code:

Private Sub SortValuesCount(ByRef v() As String)
    Dim j As Long
    Dim k As Long
    Dim b As Long
    Dim x As Long
    Dim y As Long
    Dim z As String
    
    b = UBound(v)
    i = 1
    Do While i = 1
        i = 0
        For j = 0 To b
            If v(j) <> vbNullString Then
                x = Val(Mid$(v(j), InStr(1, v(j), "(") + 1)) ' get the count
                For k = j + 1 To b
                    If v(k) <> vbNullString Then
                        y = Val(Mid$(v(k), InStr(1, v(k), "(") + 1)) ' get the count
                        If x < y Then ' compare the two count and swap if x < y
                            z = v(j)
                            v(j) = v(k)
                            v(k) = z
                            i = 1
                        End If
                    End If
                Next
            End If
        Next
    Loop
 End Sub

And out of curiosity, is there any better way of going about this? Thanks.

P.S.
Apparently it still doesn't sort things with parenthesis well:
example input:
Some string here (Stuff in parenthesis) (1)
Another string (Stuff in parenthesis) (3)

=============================================================================
After application to a large set of data, I noticed some stragglers didn't sort as expected.:

example 1:
input was:
810 (5), 6 (16), 811 (3), 800 (3), 831 (2), 5 (1)
5 (1), 800 (4), 6 (28)
6 (11)
800 (1), 6 (29), 5 (2), 811 (1)
6 (23), 810 (1)

output was:
800 (8), 6 (107), 810 (6), 811 (4), 5 (4), 831 (2)

example 2:
input was:
810 (1), 6 (29)
5 (1), 6 (32)
6 (11)
6 (31), 831 (1), 800 (1)
6 (23), 831 (1)

output was:
831 (2), 6 (126), 5 (1), 810 (1), 800 (1)

To recap the codes involved, it is the one I wrote with the insertion of the sorting sub you created:

vb Code:

Function reCollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String
Dim colUnique As Collection
Dim currElementAmount As Long
Dim j As Long
Dim k As Long
Dim n As Long
Dim b As Long
Dim x As Variant
Dim y As Variant
Dim strValues() As String
Dim strResult As String
Dim allStrings As String
Dim extElement As String
Dim currElement As String
 
' 1. Collect strings into allStrings
For j = 0 To SelectLength - 1
    allStrings = allStrings & CStr(Worksheets(Sheetname).Cells(CellRow, CellCol + j).value) & ", "
Next
 
' 2. Insert uniques from extracted elements of the single string into collection object
    ' extract entire elements
    x = Split(allStrings, "), ")
    For j = 0 To UBound(x) - 1
        extElement = x(j)
        ' separate value and amount in each element
        y = Split(extElement, " (")
        currElement = CStr(y(0))
 
        currElementAmount = CLng(y(1))
        
' 3. Reconstitute into array: strValues()
        For k = 1 To currElementAmount
            ReDim Preserve strValues(n)
            strValues(n) = currElement
            n = n + 1
            ' ''' vestigial code: strResult = strResult & currElement & ", "
        Next
    Next
    
' 4. Recount
        b = UBound(strValues)
 ' for speed purpose, cache UBound(strValues) in a variable instead of continuesly calculating it inside the loop
        ' Search for repeated values and add its count after it
    For j = 0 To b ' -1
        If strValues(j) <> vbNullString Then
            n = 1
            For k = j + 1 To b
                If strValues(j) = strValues(k) Then
                    n = n + 1
                    strValues(k) = vbNullString ' delete the repeated value in order to not find it again.
                End If
            Next
            strValues(j) = strValues(j) & " (" & n & ")" ' append the value count
        End If
    Next
    
    SortValuesCount strValues
    
    For j = 0 To b
        If strValues(j) <> vbNullString Then
            strResult = strResult & strValues(j) & ", "
        End If
    Next
 
 
reCollectSources = Mid$(strResult, 1, Len(strResult) - 2)
End Function
 
Private Sub SortValuesCount(ByRef v() As String)
    Dim j As Long
    Dim k As Long
    Dim b As Long
    Dim x As Long
    Dim y As Long
    Dim z As String
    
    b = UBound(v)
    For j = 0 To b
        If v(j) <> vbNullString Then
            x = Val(Mid$(v(j), InStr(1, v(j), "(") + 1)) ' get the count
            For k = j + 1 To b
                If v(k) <> vbNullString Then
                    y = Val(Mid$(v(k), InStr(1, v(k), "(") + 1)) ' get the count
                    If x < y Then ' compare the two count and swap if x < y
                        z = v(j)
                        v(j) = v(k)
                        v(k) = z
                    End If
                End If
            Next
        End If
    Next
 End Sub

Before I noticed the problem, I made another sub addition. The addition seems to further interfere with the sorting process...

Input was:
812 (20), 810 (1), 6 (9)
6 (19), 800 (1), 812 (13)
813 (1), 812 (8), 6 (2)
6 (26), 812 (7) 6 (19), 812 (5)

Output was:
6 (75), 812 (53), 810 (1), 800 (1), 813 (1)

After adding a sub here:
Line 28: currElement = CStr(y(0))
Line 29: ConvertSourceNamesHere currElement ' the added sub
Line 30: currElementAmount = CLng(y(1))

...
The sub:

vb Code:

Private Sub ConvertSourceNamesHere(ByRef v As String)
Dim j As Long
Dim x As Long
Dim result As String
 
 
        x = CLng(v)
        Select Case x
 
            Case 6
            result = "ALL CAPS"
 
            Case 830
            result = "String (And in parenthesis)"
            
            Case 810
            result = "Some string here (And in parenthesis)"
 
            Case 812
            result = "Test"
                
            Case 813
            result = "Second test"
 
            Case Else
            result = v
            
            
        End Select
        v = result
End Sub

After the addition of the sub, the output becomes out of order:
ALL CAPS (75), Second test (1), 800 (1), Test (53), Some string here (And in parenthesis) (1)

Thank you very much for any insights on this.

Quote:

---

By adding a while loop to repeat the sorting when a switch between numbers happen.

---

There is no need at all to repeat sorting when swap two values.

Quote:

---

Apparently it still doesn't sort things with parenthesis well:

---

I was think you are deal with numeric values only, anyway a little change to the sort method will make it work with data includes "(" by using InstrRev instead of Instr

vb Code:

Private Sub SortValuesCount(ByRef v() As String)
    Dim j As Long
    Dim k As Long
    Dim b As Long
    Dim x As Long
    Dim y As Long
    Dim z As String
    
    
    b = UBound(v)
    
    For j = 0 To b
        If v(j) <> vbNullString Then
            x = Val(Mid$(v(j), InStrRev(v(j), "(") + 1))   ' get the count
            For k = j + 1 To b
                If v(k) <> vbNullString Then
                    y = Val(Mid$(v(k), InStrRev(v(k), "(") + 1))  ' get the count
                    If x < y Then ' compare the two count and swap if x < y
                        z = v(j)
                        v(j) = v(k)
                        v(k) = z
                    End If
                End If
            Next
        End If
    Next
 
End Sub

I hope this solve other issues.

Thank you again for your help!

Quote:

---

I was think you are deal with numeric values only

---

I wish I could've known that earlier too, wasn't able to reallize that was needed until the issue came up during the process.

I will be able to try out the new codes next week, thanks again!

Thanks 4x2y, the code is working perfectly with the edit, and I was able to understand it as well.

I have been looking for a way to get a cell's position without any success, could anyone help?

Example:
=Function(C56)
displays:
3,56

Thank you!

if you want "c" instead of 3 there are a plethora of program snippets all over the weba dn in this form to do just that!..

an easy less programmatic way is to use what you have and ask a simple question...

I have been talking lately on this forum about knowing the odd things about your languages and how importand it is..

well for your problem look at how you use row and column your 56 and 3 from above..

sometimes its c56 sometimes it 56,3

the row is always a number its the column that causes an issue

you can get the column back using a number of functions but they all return numbers

so people have reverted to writting large programs to work out the column number to letter(s) value

BUT you get the column back as letters if you ask for the address and you can parse the address for the column letters

like this letters=cells(56,3).address this returns $c$56 and then you have to collect the stuff after the first $ upto the second $ easy

but did you realise that you can ask for just the column bit...

letters=cells(3).address this returns $c$1 and you can get the stuff as before

i would not use this as a way of shortening access to the first row of a column. The row portion retruns a predictable, but worthless value, unless you know different?

hope that helps

Quote:

---

Originally Posted by needhelpooo

I have been looking for a way to get a cell's position without any success, could anyone help?

---

Please note when you have new question in different issue, create new thread, I think this is forum rule.

@Incidentals: thank you very much for your help!

I will post further questions in a different thread.

Quote:

---

I will post further questions in a different thread.

---

in that case if this is finished pls mark resolved

=CELL("address")