-
Jan 31st, 2012, 11:40 AM
#1
Thread Starter
New Member
[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.
Last edited by needhelpooo; Feb 24th, 2012 at 09:54 AM.
-
Jan 31st, 2012, 01:31 PM
#2
Re: Please help with VBA, 6.5 for Excel
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.
-
Jan 31st, 2012, 01:53 PM
#3
Re: Please help with VBA, 6.5 for Excel
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.
-
Jan 31st, 2012, 02:56 PM
#4
Thread Starter
New Member
Re: Please help with VBA, 6.5 for Excel
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!
-
Feb 2nd, 2012, 10:40 AM
#5
Thread Starter
New Member
Re: Please help with VBA, 6.5 for Excel
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)"
-
Feb 2nd, 2012, 03:41 PM
#6
Re: Please help with VBA, 6.5 for Excel
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.
Originally Posted by needhelpooo
Would anyone be so kind to help me again in this matter?
I'm here again
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
-
Feb 2nd, 2012, 04:44 PM
#7
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
Thank you very much!
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!
-
Feb 6th, 2012, 12:25 PM
#8
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
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)".
Last edited by needhelpooo; Feb 6th, 2012 at 12:28 PM.
-
Feb 6th, 2012, 02:12 PM
#9
Re: Please help with VBA, for Excel 2007: working with objects
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
-
Feb 6th, 2012, 02:38 PM
#10
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
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!
Last edited by needhelpooo; Feb 6th, 2012 at 02:55 PM.
-
Feb 6th, 2012, 03:19 PM
#11
Re: Please help with VBA, for Excel 2007: working with objects
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
-
Feb 7th, 2012, 11:02 PM
#12
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
Once again, thank you so much for your help! The sorting algorithm is great
-
Feb 7th, 2012, 11:21 PM
#13
Re: Please help with VBA, for Excel 2007: working with objects
Last edited by 4x2y; Feb 8th, 2012 at 12:03 AM.
-
Feb 8th, 2012, 12:58 AM
#14
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
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.
-
Feb 8th, 2012, 06:41 PM
#15
Re: Please help with VBA, for Excel 2007: working with objects
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.
-
Feb 9th, 2012, 10:05 AM
#16
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
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.
Last edited by needhelpooo; Feb 9th, 2012 at 12:03 PM.
-
Feb 9th, 2012, 07:38 PM
#17
Re: Please help with VBA, for Excel 2007: working with objects
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.
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.
-
Feb 10th, 2012, 01:55 PM
#18
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
Thank you again for your help!
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!
Last edited by needhelpooo; Feb 10th, 2012 at 02:03 PM.
-
Feb 16th, 2012, 11:27 AM
#19
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
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!
Last edited by needhelpooo; Feb 16th, 2012 at 11:30 AM.
-
Feb 18th, 2012, 04:01 AM
#20
Frenzied Member
Re: Please help with VBA, for Excel 2007: working with objects
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
-
Feb 18th, 2012, 05:14 AM
#21
Re: Please help with VBA, for Excel 2007: working with objects
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.
-
Feb 22nd, 2012, 09:45 AM
#22
Thread Starter
New Member
Re: Please help with VBA, for Excel 2007: working with objects
@Incidentals: thank you very much for your help!
I will post further questions in a different thread.
-
Feb 24th, 2012, 05:53 AM
#23
Re: Please help with VBA, for Excel 2007: working with objects
I will post further questions in a different thread.
in that case if this is finished pls mark resolved
=CELL("address")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|