[RESOLVED] Variables used as excel cell coordinates

[black_fender]

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 :

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 ?

[sneha2409]

Have you created the excel workbook object........
can you post that part of the code also.......

[black_fender]

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) :

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

[Spoo]

BF

Welcome to the Forums ..

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

[black_fender]

BF

Welcome to the Forums ..

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!

[jcis]

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):

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

[opus]

You have a typo in this line:

For y = coulumns_start To columns_limit

Because of that your "y" has a value of zero!

[Spoo]

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".

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 ..
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.

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)

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

[black_fender]

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 .

Cheers !