Having a problem referencing a user selected worksheet in my macro. I would like the user to select two files, the macro then inputs a formula that includes vlookup to the other sheet. However i am not sure how to reference each workbook.
Here is the code.
Code
Sub InsertLocationContents()
Dim rng As Range
Dim LastRow As Long
'OPEN CSV FILE WITH LOCATION CONTENTS
csvFN = Application.GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=csvFN
Workbooks.OpenText Filename:= _
csvFN, Origin:=437 _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
6, 1), Array(35, 1), Array(44, 1), Array(53, 1), Array(62, 1)), TrailingMinusNumbers:= _
True
End If
csvSheetName = ActiveSheet.Name
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("R1C1:R18C3")
dbfFN = Application.GetOpenFilename(Title:="Select shapes dbf file")
If dbfFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=dbfFN
Workbooks.OpenText Filename:= _
dbfFN, Origin:=437 _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
6, 1), Array(35, 1), Array(44, 1), Array(53, 1), Array(62, 1)), TrailingMinusNumbers:= _
True
End If
With dbfFN
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row ' finds the very last cell row used in column e
Range("R" & LastRow).Select
ActiveCell.FormulaR1C1 = _
"=IF(TRUE=ISERROR(VLOOKUP(RC[-15],rng,3,FALSE)),0,(VLOOKUP(RC[-15],rng,3,FALSE)))"
Selection.AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault
End With
End With
End Sub
Display More
[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I forgot to mention that the error occurs on the 'set rng' line.
Thanks for any possible help.