Please Help With Small VBA Problem...

[Doc]

I know I am overlooking something small, but I just cant seem to get a
variable (or for that matter the result of a function) from one sub to
another. Thats probably not very clear so I am including the relevant
portions of code....

Sub WhatToDoWithIt(qCell As Range, qColor As Integer)
'On Error Resume Next
Dim AnsWhat As String, ColHeader As String, NumShts As Integer, HeaderRow As
Integer, HdrNumOfColumns As Integer
HeaderRow = GetHdrRow(ActiveSheet)
<<<<<<<<<<<<<<<<<<<<Problem is here, can not get results from function on
this line into this sub
Range(HeaderRow, 1).Select
HdrNumOfColumns = Selection.SpecialCells(xlCellTypeLastCell).Column
ColHeader = qCell.Cells.End(xlUp).Value
NumShts = (ActiveWorkbook.Sheets.Count).
<<<<Irrelevant code deleted>>>>>>>

Function GetHdrRow(Sht As Worksheet) As Integer
Dim WSO As Worksheet, HdrClue As Range, HdrClueList As Range, HdrTrigger As
Object
Dim firstAddress As String, x As Integer, z As Integer
Static HeaderRow As Integer
z = 0
With ActiveSheet.Range("A1:T3")
Set WSO = ThisWorkbook.Sheets("HeaderSearchStrings")
Set HdrClueList =
WSO.Range("RngName,RngAddress,RngCity,RngEmail,RngPhone,RngZip")
For Each HdrClue In HdrClueList
Set HdrTrigger = .Find(what:=HdrClue, LookIn:=xlValues,
lookat:=xlPart, _
searchorder:=xlByRows)
If Not HdrTrigger Is Nothing Then
firstAddress = HdrTrigger.Address
x = HdrTrigger.Row
Do
Set HdrTrigger = .FindNext(HdrTrigger)
If HdrTrigger.Row = x Then
z = z + 1
End If
Loop While Not HdrTrigger Is Nothing And HdrTrigger.Address
<> firstAddress
End If
Next
End With
If z > 5 Then
HeaderRow = x
Else
HeaderRow = InputBox("Please enter the number of the header row",
"HEADER ROW INFORMATION")
End If
End Function


Please help, TIA

 

[Earl Kiosterud]

Doc,

It's late, and I didn't get into the logic of your GetHdrRow function, but I
don't see GetHdrRow on the left side of a statement. GetHdrRow doesn't seem
to have been assigned a value.

I notice you use q as a prefix, and use three-letter abbreviations, like
GetHdrRow. Are you an AS/400 type, by any chance?

 

[mudraker]

If I understand your problem correctly your code does not set the
HeaderRow value Correctly - You need to either Dim HeaderRow at the
module level and change

HeaderRow = GetHdrRow(ActiveSheet)
to
call GetHdrRow(ActiveSheet)


or
in your GetHdrRow function change

If z > 5 Then
HeaderRow = x
Else
HeaderRow = InputBox("Please enter the number of the header row",
"HEADER ROW INFORMATION")
End If

to

If z > 5 Then
GetHdrRow = x
Else
GetHdrRow = InputBox("Please enter the number of the header row",
"HEADER ROW INFORMATION")
End If



I hope this helps

 

[Doc]

Thank you both very much. Sleep deprivation will be my excuse. I fixed it
sometime between when each of you posted, but I learned something new from
mudraker --the call statement, I have never used before. So thank you again,
its pretty cool to be able to call on anonymous people that you dont know to
help you in the middle of the night (or whatever time it is wherever they
are).
Earl - do you mean that antique IBM system??? hmmm Maybe I learned something
from someone from that camp...

 

[Earl Kiosterud]

Mudraker, Doc,

It's true you can make HeaderRow a module-level variable. Then any sub or
function can change it, and any other can examine it. This can be a
problem in large modules where you could forget the use of variables, and
re-use them, or change them thinking it's only within the procedure.

The return value of a function is set by assigning a value to it, using the
function name:

Function AddStuff(Param1, Param2)
AddStuff = Param1 + Param2
End Function

 

[mudraker]

Earl

Thanks for your reply..But if you look at me origianl reply you wil
see I gave Doc 2 options

option 1 to set a variable at the module level and option 2 to set th
return value of the function

I also listed what code Doc would need to change in each exampl