Excel Macro Variable Scope problem

[djtaverner]

Hi

Ive code some code:

Option Explicit
Sub Macro1()

DIm Count As Integer
Dim verticalcounter As Integer

'ActiveSheet.Select
ThisWorkbook.Worksheets(downloadsheetname).Select
'do lots of stuff

Call findaccount
End Sub

Sub findaccount()


ThisWorkbook.Worksheets(accountmappingsheetname).Select

'do some stuff

End Sub

Problem:

VB editor highlights accountmappingsheetname and says:

"variable not defined"

I know this is due to scope deceleration of the variable. I have tried declaring it as Public and Global but when i do decleration gets highlighted and error given:

"Invalid attribute in sub or function"

Please help, my boss is about to poo all over me

[New2vba]

I may be way off here (I don't even know what scope deceleration is!), but if "downloadsheetname" and "accountmappingsheetname" are the names of your worksheets, then shouldn't your code be

ThisWorkbook.Worksheets("downloadsheetname").Select

And

ThisWorkbook.Worksheets("accountmappingsheetname").Select

?

[DKenny]

You need to declare all your variables, preferrably at the start of your procedure.
Add the following declartion immediately after the line where you declare verticalcounter.

Dim accountmappingsheetname as String

[New2vba]

I assume I was way off then Declan?

[DKenny]

If they are sheet names you are absolutly correct. If they are variables then my answer is the right one.

I didn't even think of that possibliity.

[djtaverner]

1 - they are variable names hence I havn't used ""

2 - Sorry when I copied and pasted the code in I accidentaly deleted the 2 lines

Dim downloadsheetname As String
Dim accountmappingsheetname As String

These were in there already.

Any suggestions??

[DKenny]

Can you post the complete code?

[djtaverner]

Option Explicit
Sub Macro1()
'MsgBox "" & endofblock

' Macro1 Macro
' Macro written 26/04/2006 by david.taverner

Global Count As Integer
Dim verticalcounter As Integer
Dim endofblock As Integer
Dim startofblock As Integer
Dim accountnumber
Dim count_gl_acct_mapping_vertical As Integer
Dim accountnumberfound As Integer

Dim downloadsheetname As String
Dim accountmappingsheetname As String

downloadsheetname = "ZGROUPA downloads 25.4.2006" 'SHOULD BE COMMENTED OUT
accountmappingsheetname = "gl acct mapping"



Count = 1
verticalcounter = 1
count_gl_acct_mapping_vertical = 1

'ActiveSheet.Select
ThisWorkbook.Worksheets(downloadsheetname).Select

Do While Count < 256 ' check all columns from maximum number for column output from SAP Account Number

If Cells(1, Count).Value = "Account number" Then 'find column with account number
Do While verticalcounter < Range("A65536").End(xlUp).Row 'find last row
If Cells(verticalcounter, Count).Value = (Cells((verticalcounter + 1), Count).Value) Then ' check that still same block
verticalcounter = verticalcounter + 1 ' if same block do nothing
Else
endofblock = verticalcounter ' if different block identify last block cell
startofblock = verticalcounter + 1 ' and first new block cell
verticalcounter = verticalcounter + 1
accountnumber = Cells(startofblock, Count).Value ' gets value off account number for block
'MsgBox "endofblock" & endofblock
'MsgBox "startofblock" & startofblock
MsgBox "account number" & accountnumber
Call findaccount
End If
Loop
Count = 256 'stop counter moving across columns
Else
Count = Count + 1
End If

Loop
End Sub

Sub findaccount()


ThisWorkbook.Worksheets(accountmappingsheetname).Select



Do While count_gl_acct_mapping_vertical < Range("A65536").End(xlUp).Row 'find last row

If Cells(count_gl_acct_mapping_vertical, 1).Value = accountnumber Then
accountnumberfound = 1
MsgBox "account found"
Else
'count_gl_acct_mapping_vertical = count_gl_acct_mapping_vertical + 1
End If
MsgBox "counting"
count_gl_acct_mapping_vertical = count_gl_acct_mapping_vertical + 1
Loop

End Sub

[billhuard]

Option Explicit
Sub Macro1()
'MsgBox "" & endofblock
 
' Macro1 Macro
' Macro written 26/04/2006 by david.taverner
 
Global Count As Integer
Dim verticalcounter As Integer
Dim endofblock As Integer
Dim startofblock As Integer
Dim accountnumber
Dim count_gl_acct_mapping_vertical As Integer
Dim accountnumberfound As Integer
 
Dim downloadsheetname As String
Dim accountmappingsheetname As String
 
downloadsheetname = "ZGROUPA downloads 25.4.2006" 'SHOULD BE COMMENTED OUT
accountmappingsheetname = "gl acct mapping"
 
 
 
Count = 1
verticalcounter = 1
count_gl_acct_mapping_vertical = 1
 
'ActiveSheet.Select
ThisWorkbook.Worksheets(downloadsheetname).Select
 
Do While Count < 256 ' check all columns from maximum number for column output from SAP Account Number
 
If Cells(1, Count).Value = "Account number" Then 'find column with account number
Do While verticalcounter < Range("A65536").End(xlUp).Row 'find last row
If Cells(verticalcounter, Count).Value = (Cells((verticalcounter + 1), Count).Value) Then ' check that still same block
verticalcounter = verticalcounter + 1 ' if same block do nothing
Else
endofblock = verticalcounter ' if different block identify last block cell
startofblock = verticalcounter + 1 ' and first new block cell
verticalcounter = verticalcounter + 1
accountnumber = Cells(startofblock, Count).Value ' gets value off account number for block
'MsgBox "endofblock" & endofblock
'MsgBox "startofblock" & startofblock
MsgBox "account number" & accountnumber
Call findaccount
End If
Loop
Count = 256 'stop counter moving across columns
Else
Count = Count + 1
End If
 
Loop
End Sub
 
Sub findaccount()
 
 
ThisWorkbook.Worksheets(accountmappingsheetname).Select
 
 
 
Do While count_gl_acct_mapping_vertical < Range("A65536").End(xlUp).Row 'find last row
 
If Cells(count_gl_acct_mapping_vertical, 1).Value = accountnumber Then
accountnumberfound = 1
MsgBox "account found"
Else
'count_gl_acct_mapping_vertical = count_gl_acct_mapping_vertical + 1
End If
MsgBox "counting"
count_gl_acct_mapping_vertical = count_gl_acct_mapping_vertical + 1
Loop
 
End Sub

Way easier to read with VBCODE tags

[djtaverner]

how do I add visual basic code tags

[billhuard]

[VBCADE]insert your code between theses tags[/VBCADE]

and replace A by O VBCADE = VBCODE

[djtaverner]

Anyone any ideas on whats wrong with the code?

[DKenny]

The problem, at least one of the problems, is that you are using variables in the findaccount procedure that have not been scoped within that procedure.

If you need to use the sames variables and have them mean the same thing across multiple procedures within a module then you need to declare them outside of the procedures.
Move the declaration of all the vairables that are used by both procedures outside of module 1.

Option Explicit
 
Dim accountmappingsheetname As String
Dim count_gl_acct_mapping_vertical As Integer
Dim accountnumber
Dim accountnumberfound As Integer
 
Sub Macro1()
'MsgBox "" & endofblock
 
' Macro1 Macro
' Macro written 26/04/2006 by david.taverner
 
Dim downloadsheetname As String
Dim Count As Integer
Dim verticalcounter As Integer
Dim endofblock As Integer
Dim startofblock As Integer
'rest of code continues here....

[djtaverner]

nice one kenny,

how stupid of me, always the silly mistakes that I cant spot