Need to pass only one value from procedure

[needhelpplease]

I need to pass the value of a variable from one procedure to another without running the entire called procedure

Private Sub openReport(ByVal strProductFile as string)
MsgBox "Open previous week's report.", vbOKOnly + vbExclamation, "Open"
Application.Dialogs(xlDialogOpen).Show

strProductFile = Application.GetOpenFilename
End Sub

Public Sub vlookup()
Dim strProductFile as string
openReport strProductFile

...then I'll concatentate the name of the file with a vlookup formula

BUT

when I do it this way, it executes all of the openReport procedure...I only want the file name to be returned.

 

[daverunt]

Hi,

To pass a variable from one procedure to another I think you have to use by reference and not ByVal
(Reference is default so can be omitted)

So the first line would be:

Private Sub openReport(strProductFile as string)

To return just the filename use Dir:

strProductFile = Dir(Application.GetOpenFilename)

However I don't know how to get around the file opening part of the procedure so I removed:

Application.Dialogs(xlDialogOpen).Show

...and used workbooks.open in the main body. I don't know the full picture so I'm just throwing in ideas.

Private Sub GetReport(strProductFile As String)
MsgBox "Open previous week's report.", vbOKOnly + vbExclamation, "Open"
strProductFile = Application.GetOpenFilename
 
End Sub
 
Public Sub vlookup()
Dim strProductFile As String
GetReport strProductFile
 
MsgBox Dir(strProductFile)
Workbooks.Open strProductFile
 
End Sub

Hope some of this helps.

 

[Norie]

That doesn't quite make sense.

What do you want the sub to do if it's not to run the code in it?

 

[needhelpplease]

I wanted it to return the name of the file to use in a vlookup in another procedure, but not bring up the Open file dialog box again because the file was already open. I think I got it figured out...I did some restructuring with the procedures.

I’m actually having another issue now with a For Next look and If Then Statement.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I want to delete every row that does not have 28168-59 or 19062-59 in column B, except of course the header.<o></o>
<o> </o>
This worked (another than the header part) before I added an OR with another part#, but now that I have both, it’s deleting everything in the spreadsheet.<o></o>
<o> </o>
Any ideas?<o></o>
<o> </o>
Sub main()<o></o>
Dim ws As Worksheet<o></o>
<o> </o>
Set ws = ActiveSheet<o></o>
<o> </o>
For i = ws.Range("B65536").End(xlUp).Row To 1 Step -1<o></o>
If ws.Cells(i, 2) <> "28168-59" Or ws.Cells(i, 2) <> "19062-59" Then<o></o>
<o></o>
ws.Rows(i).Delete<o></o>
<o> </o>
End If<o></o>
Next<o></o>
<o> </o>
End Sub<o></o>
<o> </o>
<o> </o>
I’m sorry, I just signed up on here and I’m not familiar with the tags.<o></o>

 

[Norie]

So all you wanted to find was the name of a file?

Why not just use it's Name property?

As for your new problem the only things I can see that might be causing it is the use of ActiveSheet and lack of a workbook reference.

To make sure you the code is runnning on the correct worksheet in the right workbook you should add a workbook reference and specify the worksheet
name explicitily.

Something like this perhaps.

' change the workbook name and extension as required
Set wbTheWorkbook = Workbooks("TheWBwiththeWSToDeleteFrom.xls")
 
Set ws = wbThisWorkbook("TheNameOfWSToDeleteFrom")
 
' loops back to row 2, assuming headers in row 1
For I = ws.Range("B65536").End(xlUp).Row To 2 Step -1
 
       Select Case ws.Range("B" & I).Value
           Case "28168-59", "19062-59"
               ' do nothing
           Case Else
               ws.Range("B" & I).EntireRow.Delete
       End Select
Next I