Pass variable to macro
Hello and TIA. I'm trying to pass along a variable from a workbook_open macro
to another macro, but the variable is not getting passed along. The macro "GenerateWord" in a standard module isn't recognizing "sCol". Public sCol As String Sub Workbook_Open() sCol = InputBox("Enter column.") GenerateWord End Sub Sub GenerateWord() Dim iLastRow As Long 'Set range With Workbooks("words.xls").Sheets("test") iLastRow = .Cells(Rows.Count, sCol).End(xlUp).Row 'error occurs here ...... End Sub Regards, Jason |
Pass variable to macro
Public sCol As String
must be declared in the general/standard module at the very top outside any procedures. -- Regards, Tom Ogilvy "Jason Morin" wrote in message ... Hello and TIA. I'm trying to pass along a variable from a workbook_open macro to another macro, but the variable is not getting passed along. The macro "GenerateWord" in a standard module isn't recognizing "sCol". Public sCol As String Sub Workbook_Open() sCol = InputBox("Enter column.") GenerateWord End Sub Sub GenerateWord() Dim iLastRow As Long 'Set range With Workbooks("words.xls").Sheets("test") iLastRow = .Cells(Rows.Count, sCol).End(xlUp).Row 'error occurs here ...... End Sub Regards, Jason |
Pass variable to macro
Hii Jason
You could try iLastRow = .Cells(Rows.Count, ThisWorkbook.sCol).End(xlUp).Row but that's not really passing a variable, it's making the variable available. To really pass it, do Sub Workbook_Open() Dim sCol As String sCol = InputBox("Enter column.") Call GenerateWord(sCol) End Sub Sub GenerateWord(sCol As String) MsgBox "You passed " & sCol End Sub Note that iLastRow will still err as is, because Cells need a numeric column reference, not a column letter / string variable. HTH. Best wishes Harald "Jason Morin" skrev i melding ... Hello and TIA. I'm trying to pass along a variable from a workbook_open macro to another macro, but the variable is not getting passed along. The macro "GenerateWord" in a standard module isn't recognizing "sCol". Public sCol As String Sub Workbook_Open() sCol = InputBox("Enter column.") GenerateWord End Sub Sub GenerateWord() Dim iLastRow As Long 'Set range With Workbooks("words.xls").Sheets("test") iLastRow = .Cells(Rows.Count, sCol).End(xlUp).Row 'error occurs here ...... End Sub Regards, Jason |
All times are GMT +1. The time now is 06:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com