[RESOLVED] Variables used as excel cell coordinates
Hello,
I'm trying to create a little program behind a excel(2003) button. I understood the language to use is Visual Basic and hopefully I'm in the right section of the forum.
I have this piece of code so far :
Code:
For x = lines_start To lines_limit
For y = coulumns_start To columns_limit
k = 0
For i = 1 To cond_nr
'verifying first possible value
vertically_available = yes 'vertically
For j = lines_start To lines_limit
If Sheet1.Cells(y, j) = possibleValues(i) Then
vertically_available = no
Exit For
End If
Next
If vertically_available = yes And horizontally_available = yes Then
k = k + 1
finalValues(k) = possibleValues(i)
End If
End If
Next
'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
Sheet1.Cells(x, y) = finalValues(luck)
Next
Next
My problems seems to be in the lines with red where i reefer to a cell using variables for the cell's coordinates. First to compare the value from that cell with something, as an if condition, second time, to assign a certain value from an array to a cell. The error I get is "Application-defined or object-defined error" .
(obviously all the variables and arrays used are correctly declared but I didn;t post that part of the program too)
Does anyone have any idea how to overcome this ?
Re: Variables used as excel cell coordinates
Have you created the excel workbook object........
can you post that part of the code also.......
Re: Variables used as excel cell coordinates
Quote:
Originally Posted by
sneha2409
Have you created the excel workbook object........
can you post that part of the code also.......
Hello,
I haven;t created the excel workbook object. It seems that excel sheet already exists as an object: In my Microsoft Visual Basic Menu (launched via Excel) I have in the right under the name of my excel file a list with all the objects of this file (each excel sheet is an object).
Another observation: in the lines in red (written in the above post) where the debugger finds the error, if I use instead of variables numbers everything works fine :
Sheet1.Cells(x, y) = finalValues(luck) - I get errors
Sheet1.Cells(1, 1) = finalValues(luck) - works (but it is not what I want.
However , please tell me how to declare this object (I guess the object should be Sheet1 ? ) and I will try.
Also I'll insert below the the entire code (including many irrelevant parts, in case I missed anything) :
Code:
Private Sub CommandButton1_Click()
Dim finalValues(1 To 3), possibleValues(1 To 3), remainedValues(1 To 3) As String
Dim l, j, k, i, luck, x, y, lines_limit, columns_limit, lines_start, columns_start, vecin_stanga, vecin_dreapta, vecin_sus, vecin_jos As Integer
Dim cond_nr As Integer
Dim stanga_value, dreapta_value, sus_value, jos_value, vertically_available, horizontally_available As String
lines_start = 2
columns_start = 2
lines_limit = 4
columns_limit = 4
cond_nr = 3
possibleValues(1) = "Deftones"
possibleValues(2) = "Tool"
possibleValues(3) = "Disturbed"
For x = lines_start To lines_limit
For y = coulumns_start To columns_limit
k = 0
For i = 1 To cond_nr
'verifying first possible value
vertically_available = yes 'vertically
For j = lines_start To lines_limit
If Sheet1.Cells(y, j) = possibleValues(i) Then
vertically_available = no
Exit For
End If
Next
If i = 1 Then
horizontally_available = yes 'horizontally
For l = columns_start To columns_limit
If Sheet1.Cells(x, l) = possibleValues(i) Then
horizontally_available = no
Exit For
End If
Next
If vertically_available = yes And horizontally_available = yes Then
k = k + 1
finalValues(k) = possibleValues(i)
End If
End If
Next
'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
Sheet1.Cells(1, 3) = luck
Sheet1.Cells(1, 1) = finalValues(luck)
Next
Next
MsgBox "done "
End Sub
Re: Variables used as excel cell coordinates
BF
Welcome to the Forums .. :wave:
Question ... are you doing this
- using a VB6 app to interact with an Excel workbook
- all within Excel
If it is the latter, then technically speaking, you are using VBA.
While VBA is similar to VB6, theoretically, you should be in the Office Development Forum.
Spoo
Re: Variables used as excel cell coordinates
Quote:
Originally Posted by
Spoo
BF
Welcome to the Forums .. :wave:
Question ... are you doing this
- using a VB6 app to interact with an Excel workbook
- all within Excel
If it is the latter, then technically speaking, you are using
VBA.
While VBA is similar to VB6, theoretically, you should be in the Office Development Forum.
Spoo
Hello, yes I think is VBA. I'll adress this question there and ask the admins to close this thread.
Thank you!
Re: Variables used as excel cell coordinates
The error may not be related to this but: declare all your variables specifying type for each of them, the way your're doing it now only the last variable on each line has the correct type. Example (I added the code in red):
Code:
Dim finalValues(1 To 3) As String, possibleValues(1 To 3) As String, remainedValues(1 To 3) As String
Do the same with all your variables. VB assumes that variables without type are Variants.
Side Note: Please! indent your code, not just here, in your project. Do it using TAB key, it's the best way, your code will be much easier to read for you and also for us :)
Re: Variables used as excel cell coordinates
You have a typo in this line:
Quote:
For y = coulumns_start To columns_limit
Because of that your "y" has a value of zero!
Re: Variables used as excel cell coordinates
BF
OK, glad we got the VBA bit confirmed.
Using Jcis's suggestion, here is your OP, with indents.
I took the liberty to edit the Next statements by adding the
identifying "counter".
Code:
For x = lines_start To lines_limit
For y = coulumns_start To columns_limit
k = 0
For i = 1 To cond_nr
'verifying first possible value
vertically_available = yes 'vertically
For j = lines_start To lines_limit
If Sheet1.Cells(y, j) = possibleValues(i) Then
vertically_available = no
Exit For
End If
Next j
If vertically_available = yes And horizontally_available = yes Then
k = k + 1
finalValues(k) = possibleValues(i)
End If
End If
Next i
'randomvalue = CInt(Int((upperbound - lowerbound + 1) * Rnd() + lowerbound))
luck = CInt(Int((k - 1 + 1) * Rnd() + 1))
Sheet1.Cells(x, y) = finalValues(luck)
Next y
Next x
For starters, you seem to have an unmatched End If.
Such a situation will cause a crash, but the "reported error" is generally
unreliable .. usually something else is the issue.
See what happens if you fix that.
EDIT:
I just saw Opus's spot .. nice one .. :thumb:
I have also highlighted that in red.
Here, too, is the balance of your code from your post #3.
I also took the liberty to add some extra Dim lines (to eliminate
the horizontal scroll bar for ease of reading), but did not address
the issue that Jcis brought up .. you still need to fix that.
Code:
Private Sub CommandButton1_Click()
Dim finalValues(1 To 3), possibleValues(1 To 3), remainedValues(1 To 3) As String
Dim l, j, k, i, luck, x, y, lines_limit, columns_limit As Integer
Dim lines_start, columns_start As Integer
Dim vecin_stanga, vecin_dreapta, vecin_sus, vecin_jos As Integer
Dim cond_nr As Integer
Dim stanga_value, dreapta_value, sus_value, jos_value As String
Dim vertically_available, horizontally_available As String
' set vars
lines_start = 2
columns_start = 2
lines_limit = 4
columns_limit = 4
cond_nr = 3
possibleValues(1) = "Deftones"
possibleValues(2) = "Tool"
possibleValues(3) = "Disturbed"
< ... see code snippet above ... >
MsgBox "done "
End Sub
Finally, a suggestion to minimize typo's of the kind that Opus caught ...
- Always use a few CAPITAL letters when you Dim your variable names.
- Thereafter, when you type the variable name in lower case, the VBA Editor
will automatically capitalize it for you. - This is a visual clue .. if you type some var name and no capitals appear
>> oops !
So, something like this (incomplete list, but does use Jcis's suggestion)
Code:
Dim Lines_Limit As Integer
Dim Columns_Limit As Integer
Dim Lines_Start As Integer
Dim Columns_Start As Integer
Dim Cond_NR As Integer
Spoo
Re: Variables used as excel cell coordinates
Hello to all,
Yes, my problem was solved after correcting my typo (thanks a lot Opus & Spoo) . Also all your advices are noted (regarding variable declaration .. etc)
Very warm community you have here :cool: .
Cheers !
