alanive
08-06-2008, 07:33 AM
Hi,
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.
Option Explicit
Sub InsertLocationContents()
Dim aRng As Range
Dim LastRow As Long
Dim dbfFN As String
Dim csvFN As String
'OPEN CSV FILE WITH LOCATION CONTENTS
csvFN = Application.GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = vbNullString 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
Set aRng = ActiveSheet.Range("A1:C18")
dbfFN = Application.GetOpenFilename(Title:="Select shapes dbf file")
If dbfFN = "" 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
'Workbooks(dbfFN).Activate
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],aRng,3,FALSE)),0,(VLOOKUP(RC[-15],aRng,3,FALSE)))"
Selection.AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault
End With
End Sub
The code runs through ok but just places aRng in the formula box rather than the actual physical range.
Any help appreciated
Alan.
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.
Option Explicit
Sub InsertLocationContents()
Dim aRng As Range
Dim LastRow As Long
Dim dbfFN As String
Dim csvFN As String
'OPEN CSV FILE WITH LOCATION CONTENTS
csvFN = Application.GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = vbNullString 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
Set aRng = ActiveSheet.Range("A1:C18")
dbfFN = Application.GetOpenFilename(Title:="Select shapes dbf file")
If dbfFN = "" 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
'Workbooks(dbfFN).Activate
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],aRng,3,FALSE)),0,(VLOOKUP(RC[-15],aRng,3,FALSE)))"
Selection.AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault
End With
End Sub
The code runs through ok but just places aRng in the formula box rather than the actual physical range.
Any help appreciated
Alan.