Help With this variable

Private Sub btnRun_Click()

On Error GoTo Err_btnRun

Dim objXLApp As Object
Dim Rng As Range
Dim LastRow As Integer
Dim LastColumn As Integer
Dim MP3Collection As String
Dim WishList As String

Screen.MousePointer = vbHourglass

' Launch Microsoft Excel
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
Application.ScreenUpdating = False

'Open MP3 Collection List
MP3Collection = txtWishList(1).Text
Workbooks.Open filename:=MP3Collection

' Temporarily Delete Column 3
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

'Finds the first empty cell in the last row & column
Range("A1").Select
LastRow = Selection.End(xlDown).Offset(0, 0).Select
LastColumn = Selection.End(xlToRight).Offset(0, 1).Select
ActiveCell.Name = "Finish"

' Create concatenate formula
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&""-"" &"" ""&RC[-1]"

'Fill in column "C" with the DiscNum variable
Worksheets("Mp3_Export").Range("C2:Finish").FillUp '*********

'Open Wish List
WishList = txtWishList(0).Text
Workbooks.Open filename:=WishList

' Sort the data
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

'Trim the white space before the song titles
For Each Rng In Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("A:B")).SpecialCells(xlCellTypeConstants, xlTextValues)
Rng.Value = Trim(Rng.Value)
Next Rng

'Finds the first empty cell in the last row & column
Range("A1").Select
LastRow = Selection.End(xlDown).Offset(0, 0).Select
LastColumn = Selection.End(xlToRight).Offset(0, 1).Select
ActiveCell.Name = "Finish"

' Create concatenate formula
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&""-"" &"" ""&RC[-1]"

'Fill in column "C" with the DiscNum variable
Worksheets("Sheet1").Range("D2:Finish").FillUp '********

'Finds the first empty cell in the last row & column
Range("A1").Select
LastRow = Selection.End(xlDown).Offset(0, 0).Select
LastColumn = Selection.End(xlToRight).Offset(0, 1).Select
ActiveCell.Name = "Last"

ActiveCell.FormulaR1C1 = "=COUNTIF([Collection.xls]Mp3_Export!C[-1],RC[-1])" '********

'Fill in column "C" with the DiscNum variable
Worksheets("Sheet1").Range("D2:Last").FillUp '********

'Delete Duplicates
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd
Range("A1").Select
LastRow = Selection.End(xlDown).Offset(0, 0).Select
LastColumn = Selection.End(xlToRight).Offset(0, 0).Select
ActiveCell.Name = "Finish"
Range("A2:Finish").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.AutoFilter

' Delete Temporary Data
Columns("C").Select
Selection.Delete Shift:=xlToLeft

' Sort the data
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

'Save & Close Wish List
ActiveWorkbook.Save
ActiveWorkbook.Close

ActiveWorkbook.Saved = True

'Close Excel
Application.Quit
Set objXLApp = Nothing

Screen.MousePointer = vbDefault

Unload Me

Exit_btnRun:
Exit Sub

Err_btnRun:
MsgBox Err.Number & " " & Err.Description
Resume Exit_btnRun
End Sub


TIA

[mattbrown]

Posting that much code will not get you any help.

Perhaps if you post a snippet where the problem occurs and a description of the error message we might be able to help. But nobody can be bothered searching through that much code to find some unknown problem

I do apologize, as it was obviously unintentional not to post the problem description as well. Please forgive here is my problem.

In the code below I call the two different Excel spreadsheets from a form. For testing purposes I use the exact same spreadsheets everytime. Now that I got all of the bugs out of my code I need help with the lines indicated with '******. These lines all call one of the workbooks either by just it's file name (i.e. not full path as given by the user in the forms text box) or they call one of the workbooks sheets. How can I get the code to place variables in these places, & how would I get the correct variables?

[mattbrown]

I dont' really have the facilities to test it here but I don't see why you couldn't just use a string.

For example I have used your first marked instance

Dim worksheetName as string
worksheetName = "Mp3_Export"

'Fill in column "C" with the DiscNum variable 
Worksheets(worksheetName).Range"C2:Finish").FillUp '*********

Is this what you are meaning?

You see, I won't know the actual worksheets name at Run Time. The user browses to a file name on a form for two different worksheets.

Dim MP3Collection As String
Dim WishList As String

These strings hold an entire pathname to a file(i.e. C:\Windows\Desktop\MP3_Export.xls)

I think I'd be ok if I could pull out just the file name from these strings (i.e. MP3_Export.xls). But I still may have a problem with the following line of code:

ActiveCell.FormulaR1C1 = "=COUNTIF([Collection.xls]Mp3_Export!C[-1],RC[-1])"

See this line calls not only the File name (Collection.xls) But also the sheet name within.

So what I belive I need is a way to determine the actual file name & sheet names the users choose at run time.

Make any sense?

[Jop]

Yeah, as far as I understand what you're doing you could just use a string as suggested by MattBrown.
You don't have to calculate things with it and it won't be too long so why not use a string?

[mattbrown]

You can assign the content to the strings at run time if you want the user to be able to change it

eg.

Dim worksheetName as string
worksheetName = txtWorksheetName.Text

'Fill in column "C" with the DiscNum variable 
Worksheets(worksheetName).Range"C2:Finish").FillUp '*********

Is this what you are meaning? I am still a little unsure as to exactly what you mean

This is what I needed:
After the workbooks have been opened, I needed the File Name, not the path name which was given by the user, I also then needed the worksheet name of the current sheets in case ethey changed it from Sheet1. I really didn't want the user to have to enter more information, so once they gave the path to open their files my program would take it form there. Thanks for your help



Dim cFileName As String
Dim CSheet As String
Dim WishList As String
Dim WSheet As String
Dim strFilter As String

cFileName = ActiveWorkbook.Name
CSheet = ActiveSheet.Name

ActiveCell.FormulaR1C1 = "=COUNTIF" & "(" & "[" & cFileName & "]" & CSheet & "!C[-1],RC[-1])"